Sorry, I should have mentioned at the outset that the really odd thing about
this is that the error is:
"ODBC--insert on a linked table 'Logins' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]INSERT permission denied on
object 'Logins', database 'data', owner 'dbo'.
(#229)"
Needless to say, it occurs at the statement
DoCmd.RunSQL sqlstr
That's why it's odd that it works OK when run directly from the code window,
but not when it is called from the autoexec macro. It is attempting to insert
just a single record.
Thanks
Alistair
"Van T. Dinh" wrote:
> Have you identified which line of code produced the error?
> Error 3155 is "Application-defined or object-defined error" so I suspect the
> error is in the statement:
>
I am using SQL authentication. The login/ password is not currently stored in
the dsn: the tables are either linked manually before opening the database,
or using a button which runs a macro from within the database. The odd thing
is that you could open the database with autoexec, and get the Insert error
message. If you then opened it again without autoexec, kept the tables linked
as they were, then ran the code manually it worked.
"Van T. Dinh" wrote:
> Are you using Windows Authentication or SQL Server Authentication?
> If the later, do you store the LoginID / Password in the ODBC DSN?
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
> message news:6E8412D8-15BE-47C0-83D0-07878DEC7921@.microsoft.com...
>
>
|||Thanks for your suggestions.
I added this code just before the code which attempts to Insert to the
database. The passthrough query works perfectly, but the Insert still fails.
A manual Insert attempted subsequently does work, however. I have also
discovered that if I re-link the table into the database, I am then unable to
Insert to it even manually, until I have closed and re-opened the database.
"Van T. Dinh" wrote:
> I does sound that when you execute the insert in the AutoExec, the database
> has not establish a connection to the SQL Server BE.
> Perhaps, you should run code to create a dummy recordset just to establish
> the connection to the BE before your insert action.
> Stefan Hoffman posted this procedure in another newsgroup
>
> ****
> Create a passthrough query, call it "qryLogin". Use a simple SELECT
> statement like
> SELECT * FROM dbo.Table WHERE 0=1
> ..
> This code should do the login:
> Dim db As DAO.Database
> Set db = CurrentDb
> db.QueryDefs.Item("qryLogin").Connect = _
> db.TableDefs.Item("linkedTable").Connect & _
> ";UID=username;PWD=password"
> db.OpenRecordset("qryLogin")
> ****
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
> message news:08E0025C-A6C9-4780-84F3-DDB878288B4F@.microsoft.com...
>
>
|||Refreshing the link immediately before the insert statements does seem to
work consistently, thanks. While the root cause of the insert problem is
still unknown, I am very grateful to have a working solution now.
Thanks
Alistair
"Van T. Dinh" wrote:
> I suspect that the Table Logins is still not linked correctly when the
> insert code is executed.
> Try using the RefreshLink method (check Access VB Help) on this Table and
> then 1 or 2 DoEvents statements beore your DoCmd.RunSQL.
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
> message news:6F567ABE-4307-4F49-A3D4-56CE7A0A6729@.microsoft.com...
>
>
Showing posts with label linked. Show all posts
Showing posts with label linked. Show all posts
Friday, March 30, 2012
Runtime error 3155 inserting to linked SQL tables from Access
I have suddenly started getting a runtime error 3155 when attempting to
insert new rows into a linked SQL (2000 on Windows 2003 server) database
table from Access (2002 under XP).
A new bit of code in the Access database attempts to insert a database login
record into table Logins, as follows
Set oNet = CreateObject("Wscript.Network")
If Err.Number <> 0 Then
MsgBox "Unable to create Wscript.WshNetwork object"
Wscript.Quit Err.Number
End If
sqlstr = "Insert into Logins (username, workstation, intime) values ( '" &
oNet.UserName & "','" & oNet.computername & "', now())"
DoCmd.RunSQL sqlstr
Oddly the code works perfectly if it is run using the Run sub button from a
vb window in Access, but it produces the error above if it is run from
another function called from the autoexec macro.
ThanksHave you identified which line of code produced the error?
Error 3155 is "Application-defined or object-defined error" so I suspect the
error is in the statement:
Set oNet ...
BTW, if you want to continue the code after the error (which you are trying
to check with the If statement), you need to use the statement:
On Error Resume Next
before the Set statement.
In addition, in the Access IDE, use the Menu Tools / Options / General tab /
Error trapping pane to see the error-trapping options (you need "Break on
Unhandled Errors" option)
If the code works in Access IDE but doesn't work when you call it via
AutoExec / function, I suspect that it is a timing issue when Access start
... I would try a few DoEvents statement to allow the OS completes some
pending jobs like:
DoEvents
DoEvents
On Error Resume Next
'{Your code here}
HTH
Van T. Dinh
MVP (Access)
"Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
message news:C90D9F78-3421-407D-9906-60E7CC101383@.microsoft.com...
>I have suddenly started getting a runtime error 3155 when attempting to
> insert new rows into a linked SQL (2000 on Windows 2003 server) database
> table from Access (2002 under XP).
> A new bit of code in the Access database attempts to insert a database
> login
> record into table Logins, as follows
> Set oNet = CreateObject("Wscript.Network")
> If Err.Number <> 0 Then
> MsgBox "Unable to create Wscript.WshNetwork object"
> Wscript.Quit Err.Number
> End If
> sqlstr = "Insert into Logins (username, workstation, intime) values ( '" &
> oNet.UserName & "','" & oNet.computername & "', now())"
> DoCmd.RunSQL sqlstr
> Oddly the code works perfectly if it is run using the Run sub button from
> a
> vb window in Access, but it produces the error above if it is run from
> another function called from the autoexec macro.
> Thanks|||Sorry, I should have mentioned at the outset that the really odd thing about
this is that the error is:
"ODBC--insert on a linked table 'Logins' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]INSERT permission
denied on
object 'Logins', database 'data', owner 'dbo'.
(#229)"
Needless to say, it occurs at the statement
DoCmd.RunSQL sqlstr
That's why it's odd that it works OK when run directly from the code window,
but not when it is called from the autoexec macro. It is attempting to inser
t
just a single record.
Thanks
Alistair
"Van T. Dinh" wrote:
> Have you identified which line of code produced the error?
> Error 3155 is "Application-defined or object-defined error" so I suspect t
he
> error is in the statement:
>|||Are you using Windows Authentication or SQL Server Authentication?
If the later, do you store the LoginID / Password in the ODBC DSN?
HTH
Van T. Dinh
MVP (Access)
"Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
message news:6E8412D8-15BE-47C0-83D0-07878DEC7921@.microsoft.com...
> Sorry, I should have mentioned at the outset that the really odd thing
> about
> this is that the error is:
> "ODBC--insert on a linked table 'Logins' failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]INSERT permissi
on denied on
> object 'Logins', database 'data', owner 'dbo'.
> (#229)"
> Needless to say, it occurs at the statement
> DoCmd.RunSQL sqlstr
> That's why it's odd that it works OK when run directly from the code
> window,
> but not when it is called from the autoexec macro. It is attempting to
> insert
> just a single record.
> Thanks
> Alistair
>|||I am using SQL authentication. The login/ password is not currently stored i
n
the dsn: the tables are either linked manually before opening the database,
or using a button which runs a macro from within the database. The odd thing
is that you could open the database with autoexec, and get the Insert error
message. If you then opened it again without autoexec, kept the tables linke
d
as they were, then ran the code manually it worked.
"Van T. Dinh" wrote:
> Are you using Windows Authentication or SQL Server Authentication?
> If the later, do you store the LoginID / Password in the ODBC DSN?
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
> message news:6E8412D8-15BE-47C0-83D0-07878DEC7921@.microsoft.com...
>
>|||I does sound that when you execute the insert in the AutoExec, the database
has not establish a connection to the SQL Server BE.
Perhaps, you should run code to create a dummy recordset just to establish
the connection to the BE before your insert action.
Stefan Hoffman posted this procedure in another newsgroup
****
Create a passthrough query, call it "qryLogin". Use a simple SELECT
statement like
SELECT * FROM dbo.Table WHERE 0=1
.
This code should do the login:
Dim db As DAO.Database
Set db = CurrentDb
db.QueryDefs.Item("qryLogin").Connect = _
db.TableDefs.Item("linkedTable").Connect & _
";UID=username;PWD=password"
db.OpenRecordset("qryLogin")
****
HTH
Van T. Dinh
MVP (Access)
"Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
message news:08E0025C-A6C9-4780-84F3-DDB878288B4F@.microsoft.com...
>I am using SQL authentication. The login/ password is not currently stored
>in
> the dsn: the tables are either linked manually before opening the
> database,
> or using a button which runs a macro from within the database. The odd
> thing
> is that you could open the database with autoexec, and get the Insert
> error
> message. If you then opened it again without autoexec, kept the tables
> linked
> as they were, then ran the code manually it worked.
>|||Thanks for your suggestions.
I added this code just before the code which attempts to Insert to the
database. The passthrough query works perfectly, but the Insert still fails.
A manual Insert attempted subsequently does work, however. I have also
discovered that if I re-link the table into the database, I am then unable t
o
Insert to it even manually, until I have closed and re-opened the database.
"Van T. Dinh" wrote:
> I does sound that when you execute the insert in the AutoExec, the databas
e
> has not establish a connection to the SQL Server BE.
> Perhaps, you should run code to create a dummy recordset just to establish
> the connection to the BE before your insert action.
> Stefan Hoffman posted this procedure in another newsgroup
>
> ****
> Create a passthrough query, call it "qryLogin". Use a simple SELECT
> statement like
> SELECT * FROM dbo.Table WHERE 0=1
> ..
> This code should do the login:
> Dim db As DAO.Database
> Set db = CurrentDb
> db.QueryDefs.Item("qryLogin").Connect = _
> db.TableDefs.Item("linkedTable").Connect & _
> ";UID=username;PWD=password"
> db.OpenRecordset("qryLogin")
> ****
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
> message news:08E0025C-A6C9-4780-84F3-DDB878288B4F@.microsoft.com...
>
>|||I suspect that the Table Logins is still not linked correctly when the
insert code is executed.
Try using the RefreshLink method (check Access VB Help) on this Table and
then 1 or 2 DoEvents statements beore your DoCmd.RunSQL.
HTH
Van T. Dinh
MVP (Access)
"Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
message news:6F567ABE-4307-4F49-A3D4-56CE7A0A6729@.microsoft.com...
> Thanks for your suggestions.
> I added this code just before the code which attempts to Insert to the
> database. The passthrough query works perfectly, but the Insert still
> fails.
> A manual Insert attempted subsequently does work, however. I have also
> discovered that if I re-link the table into the database, I am then unable
> to
> Insert to it even manually, until I have closed and re-opened the
> database.
>|||Refreshing the link immediately before the insert statements does seem to
work consistently, thanks. While the root cause of the insert problem is
still unknown, I am very grateful to have a working solution now.
Thanks
Alistair
"Van T. Dinh" wrote:
> I suspect that the Table Logins is still not linked correctly when the
> insert code is executed.
> Try using the RefreshLink method (check Access VB Help) on this Table and
> then 1 or 2 DoEvents statements beore your DoCmd.RunSQL.
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
> message news:6F567ABE-4307-4F49-A3D4-56CE7A0A6729@.microsoft.com...
>
>|||You're welcome ... Gald it finally worked for you ...
Van T. Dinh
MVP (Access)
"Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
message news:46AE9D61-A29C-46CC-A44D-7C60E700C076@.microsoft.com...
> Refreshing the link immediately before the insert statements does seem to
> work consistently, thanks. While the root cause of the insert problem is
> still unknown, I am very grateful to have a working solution now.
> Thanks
> Alistair
>
insert new rows into a linked SQL (2000 on Windows 2003 server) database
table from Access (2002 under XP).
A new bit of code in the Access database attempts to insert a database login
record into table Logins, as follows
Set oNet = CreateObject("Wscript.Network")
If Err.Number <> 0 Then
MsgBox "Unable to create Wscript.WshNetwork object"
Wscript.Quit Err.Number
End If
sqlstr = "Insert into Logins (username, workstation, intime) values ( '" &
oNet.UserName & "','" & oNet.computername & "', now())"
DoCmd.RunSQL sqlstr
Oddly the code works perfectly if it is run using the Run sub button from a
vb window in Access, but it produces the error above if it is run from
another function called from the autoexec macro.
ThanksHave you identified which line of code produced the error?
Error 3155 is "Application-defined or object-defined error" so I suspect the
error is in the statement:
Set oNet ...
BTW, if you want to continue the code after the error (which you are trying
to check with the If statement), you need to use the statement:
On Error Resume Next
before the Set statement.
In addition, in the Access IDE, use the Menu Tools / Options / General tab /
Error trapping pane to see the error-trapping options (you need "Break on
Unhandled Errors" option)
If the code works in Access IDE but doesn't work when you call it via
AutoExec / function, I suspect that it is a timing issue when Access start
... I would try a few DoEvents statement to allow the OS completes some
pending jobs like:
DoEvents
DoEvents
On Error Resume Next
'{Your code here}
HTH
Van T. Dinh
MVP (Access)
"Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
message news:C90D9F78-3421-407D-9906-60E7CC101383@.microsoft.com...
>I have suddenly started getting a runtime error 3155 when attempting to
> insert new rows into a linked SQL (2000 on Windows 2003 server) database
> table from Access (2002 under XP).
> A new bit of code in the Access database attempts to insert a database
> login
> record into table Logins, as follows
> Set oNet = CreateObject("Wscript.Network")
> If Err.Number <> 0 Then
> MsgBox "Unable to create Wscript.WshNetwork object"
> Wscript.Quit Err.Number
> End If
> sqlstr = "Insert into Logins (username, workstation, intime) values ( '" &
> oNet.UserName & "','" & oNet.computername & "', now())"
> DoCmd.RunSQL sqlstr
> Oddly the code works perfectly if it is run using the Run sub button from
> a
> vb window in Access, but it produces the error above if it is run from
> another function called from the autoexec macro.
> Thanks|||Sorry, I should have mentioned at the outset that the really odd thing about
this is that the error is:
"ODBC--insert on a linked table 'Logins' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]INSERT permission
denied on
object 'Logins', database 'data', owner 'dbo'.
(#229)"
Needless to say, it occurs at the statement
DoCmd.RunSQL sqlstr
That's why it's odd that it works OK when run directly from the code window,
but not when it is called from the autoexec macro. It is attempting to inser
t
just a single record.
Thanks
Alistair
"Van T. Dinh" wrote:
> Have you identified which line of code produced the error?
> Error 3155 is "Application-defined or object-defined error" so I suspect t
he
> error is in the statement:
>|||Are you using Windows Authentication or SQL Server Authentication?
If the later, do you store the LoginID / Password in the ODBC DSN?
HTH
Van T. Dinh
MVP (Access)
"Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
message news:6E8412D8-15BE-47C0-83D0-07878DEC7921@.microsoft.com...
> Sorry, I should have mentioned at the outset that the really odd thing
> about
> this is that the error is:
> "ODBC--insert on a linked table 'Logins' failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]INSERT permissi
on denied on
> object 'Logins', database 'data', owner 'dbo'.
> (#229)"
> Needless to say, it occurs at the statement
> DoCmd.RunSQL sqlstr
> That's why it's odd that it works OK when run directly from the code
> window,
> but not when it is called from the autoexec macro. It is attempting to
> insert
> just a single record.
> Thanks
> Alistair
>|||I am using SQL authentication. The login/ password is not currently stored i
n
the dsn: the tables are either linked manually before opening the database,
or using a button which runs a macro from within the database. The odd thing
is that you could open the database with autoexec, and get the Insert error
message. If you then opened it again without autoexec, kept the tables linke
d
as they were, then ran the code manually it worked.
"Van T. Dinh" wrote:
> Are you using Windows Authentication or SQL Server Authentication?
> If the later, do you store the LoginID / Password in the ODBC DSN?
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
> message news:6E8412D8-15BE-47C0-83D0-07878DEC7921@.microsoft.com...
>
>|||I does sound that when you execute the insert in the AutoExec, the database
has not establish a connection to the SQL Server BE.
Perhaps, you should run code to create a dummy recordset just to establish
the connection to the BE before your insert action.
Stefan Hoffman posted this procedure in another newsgroup
****
Create a passthrough query, call it "qryLogin". Use a simple SELECT
statement like
SELECT * FROM dbo.Table WHERE 0=1
.
This code should do the login:
Dim db As DAO.Database
Set db = CurrentDb
db.QueryDefs.Item("qryLogin").Connect = _
db.TableDefs.Item("linkedTable").Connect & _
";UID=username;PWD=password"
db.OpenRecordset("qryLogin")
****
HTH
Van T. Dinh
MVP (Access)
"Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
message news:08E0025C-A6C9-4780-84F3-DDB878288B4F@.microsoft.com...
>I am using SQL authentication. The login/ password is not currently stored
>in
> the dsn: the tables are either linked manually before opening the
> database,
> or using a button which runs a macro from within the database. The odd
> thing
> is that you could open the database with autoexec, and get the Insert
> error
> message. If you then opened it again without autoexec, kept the tables
> linked
> as they were, then ran the code manually it worked.
>|||Thanks for your suggestions.
I added this code just before the code which attempts to Insert to the
database. The passthrough query works perfectly, but the Insert still fails.
A manual Insert attempted subsequently does work, however. I have also
discovered that if I re-link the table into the database, I am then unable t
o
Insert to it even manually, until I have closed and re-opened the database.
"Van T. Dinh" wrote:
> I does sound that when you execute the insert in the AutoExec, the databas
e
> has not establish a connection to the SQL Server BE.
> Perhaps, you should run code to create a dummy recordset just to establish
> the connection to the BE before your insert action.
> Stefan Hoffman posted this procedure in another newsgroup
>
> ****
> Create a passthrough query, call it "qryLogin". Use a simple SELECT
> statement like
> SELECT * FROM dbo.Table WHERE 0=1
> ..
> This code should do the login:
> Dim db As DAO.Database
> Set db = CurrentDb
> db.QueryDefs.Item("qryLogin").Connect = _
> db.TableDefs.Item("linkedTable").Connect & _
> ";UID=username;PWD=password"
> db.OpenRecordset("qryLogin")
> ****
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
> message news:08E0025C-A6C9-4780-84F3-DDB878288B4F@.microsoft.com...
>
>|||I suspect that the Table Logins is still not linked correctly when the
insert code is executed.
Try using the RefreshLink method (check Access VB Help) on this Table and
then 1 or 2 DoEvents statements beore your DoCmd.RunSQL.
HTH
Van T. Dinh
MVP (Access)
"Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
message news:6F567ABE-4307-4F49-A3D4-56CE7A0A6729@.microsoft.com...
> Thanks for your suggestions.
> I added this code just before the code which attempts to Insert to the
> database. The passthrough query works perfectly, but the Insert still
> fails.
> A manual Insert attempted subsequently does work, however. I have also
> discovered that if I re-link the table into the database, I am then unable
> to
> Insert to it even manually, until I have closed and re-opened the
> database.
>|||Refreshing the link immediately before the insert statements does seem to
work consistently, thanks. While the root cause of the insert problem is
still unknown, I am very grateful to have a working solution now.
Thanks
Alistair
"Van T. Dinh" wrote:
> I suspect that the Table Logins is still not linked correctly when the
> insert code is executed.
> Try using the RefreshLink method (check Access VB Help) on this Table and
> then 1 or 2 DoEvents statements beore your DoCmd.RunSQL.
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
> message news:6F567ABE-4307-4F49-A3D4-56CE7A0A6729@.microsoft.com...
>
>|||You're welcome ... Gald it finally worked for you ...
Van T. Dinh
MVP (Access)
"Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
message news:46AE9D61-A29C-46CC-A44D-7C60E700C076@.microsoft.com...
> Refreshing the link immediately before the insert statements does seem to
> work consistently, thanks. While the root cause of the insert problem is
> still unknown, I am very grateful to have a working solution now.
> Thanks
> Alistair
>
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.
================================================== ====
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.
========================================
==============
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.
========================================
==============
Monday, March 26, 2012
Running Transact-SQL script from SQL server agent with a different account
Hi,
I whised to run a T-SQL script as a SQL server Job that uses a linked
server to connect to a remote DB.
When I run the stored procedure in Query Analyser (with a specified
user) everything works fone but on SQL Agent I have the following
error:
Access to the remote server is denied because the current security
context is not trusted
How can I specify with wich user should the step run? The "run as" list
on Job Step window is disabled. How to use the new credential?
(The user exists on local and remote server).
ThanksHi
You don't say if your SQL Agent Service is running under a domain account or
not? At a guess you are using LOCALSYSTEM? See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
John
"pedro.f.silva@.gmail.com" wrote:
> Hi,
> I whised to run a T-SQL script as a SQL server Job that uses a linked
> server to connect to a remote DB.
> When I run the stored procedure in Query Analyser (with a specified
> user) everything works fone but on SQL Agent I have the following
> error:
> Access to the remote server is denied because the current security
> context is not trusted
> How can I specify with wich user should the step run? The "run as" list
> on Job Step window is disabled. How to use the new credential?
> (The user exists on local and remote server).
> Thanks
>|||It is using LOCALSYSTEM, I think.
But I would like to use a domain account just for this job. Is it
possible?
Why cannot i use "run as" on job step?
I thought that could be done creating a credential and a proxy but SQL
server agent doesn't allow proxys on T-SQL steps.
What can I do?
John Bell escreveu:
> Hi
> You don't say if your SQL Agent Service is running under a domain account or
> not? At a guess you are using LOCALSYSTEM? See
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> John
> "pedro.f.silva@.gmail.com" wrote:
> > Hi,
> >
> > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > server to connect to a remote DB.
> > When I run the stored procedure in Query Analyser (with a specified
> > user) everything works fone but on SQL Agent I have the following
> > error:
> >
> > Access to the remote server is denied because the current security
> > context is not trusted
> >
> > How can I specify with wich user should the step run? The "run as" list
> > on Job Step window is disabled. How to use the new credential?
> >
> > (The user exists on local and remote server).
> >
> > Thanks
> >
> >|||Hi
With a T-SQL step there is a run as user on the avanced options if you are
running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
John
"pedro.f.silva@.gmail.com" wrote:
> It is using LOCALSYSTEM, I think.
> But I would like to use a domain account just for this job. Is it
> possible?
> Why cannot i use "run as" on job step?
> I thought that could be done creating a credential and a proxy but SQL
> server agent doesn't allow proxys on T-SQL steps.
> What can I do?
> John Bell escreveu:
> > Hi
> >
> > You don't say if your SQL Agent Service is running under a domain account or
> > not? At a guess you are using LOCALSYSTEM? See
> >
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > Hi,
> > >
> > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > server to connect to a remote DB.
> > > When I run the stored procedure in Query Analyser (with a specified
> > > user) everything works fone but on SQL Agent I have the following
> > > error:
> > >
> > > Access to the remote server is denied because the current security
> > > context is not trusted
> > >
> > > How can I specify with wich user should the step run? The "run as" list
> > > on Job Step window is disabled. How to use the new credential?
> > >
> > > (The user exists on local and remote server).
> > >
> > > Thanks
> > >
> > >
>|||I had noticed that there was a "run as" user on advanced options but it
didn't work.
I was expecting it (no working) since there's no place to specify the
user password. How could it run with that identify if I hadn't
specified the password? But there's no place to do it!
John Bell escreveu:
> Hi
> With a T-SQL step there is a run as user on the avanced options if you are
> running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> John
> "pedro.f.silva@.gmail.com" wrote:
> > It is using LOCALSYSTEM, I think.
> >
> > But I would like to use a domain account just for this job. Is it
> > possible?
> >
> > Why cannot i use "run as" on job step?
> >
> > I thought that could be done creating a credential and a proxy but SQL
> > server agent doesn't allow proxys on T-SQL steps.
> >
> > What can I do?
> >
> > John Bell escreveu:
> > > Hi
> > >
> > > You don't say if your SQL Agent Service is running under a domain account or
> > > not? At a guess you are using LOCALSYSTEM? See
> > >
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > >
> > > John
> > >
> > > "pedro.f.silva@.gmail.com" wrote:
> > >
> > > > Hi,
> > > >
> > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > server to connect to a remote DB.
> > > > When I run the stored procedure in Query Analyser (with a specified
> > > > user) everything works fone but on SQL Agent I have the following
> > > > error:
> > > >
> > > > Access to the remote server is denied because the current security
> > > > context is not trusted
> > > >
> > > > How can I specify with wich user should the step run? The "run as" list
> > > > on Job Step window is disabled. How to use the new credential?
> > > >
> > > > (The user exists on local and remote server).
> > > >
> > > > Thanks
> > > >
> > > >
> >
> >|||Hi
I would suggest that you create an account to use for a service account and
change the service account to be that.
John
"pedro.f.silva@.gmail.com" wrote:
> I had noticed that there was a "run as" user on advanced options but it
> didn't work.
> I was expecting it (no working) since there's no place to specify the
> user password. How could it run with that identify if I hadn't
> specified the password? But there's no place to do it!
>
> John Bell escreveu:
> > Hi
> >
> > With a T-SQL step there is a run as user on the avanced options if you are
> > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > It is using LOCALSYSTEM, I think.
> > >
> > > But I would like to use a domain account just for this job. Is it
> > > possible?
> > >
> > > Why cannot i use "run as" on job step?
> > >
> > > I thought that could be done creating a credential and a proxy but SQL
> > > server agent doesn't allow proxys on T-SQL steps.
> > >
> > > What can I do?
> > >
> > > John Bell escreveu:
> > > > Hi
> > > >
> > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > not? At a guess you are using LOCALSYSTEM? See
> > > >
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > >
> > > > John
> > > >
> > > > "pedro.f.silva@.gmail.com" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > server to connect to a remote DB.
> > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > user) everything works fone but on SQL Agent I have the following
> > > > > error:
> > > > >
> > > > > Access to the remote server is denied because the current security
> > > > > context is not trusted
> > > > >
> > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > on Job Step window is disabled. How to use the new credential?
> > > > >
> > > > > (The user exists on local and remote server).
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > >
> > >
>|||Thanks John.
That works if I want all my Jobs to run with the same user.
Since I only have one it's ok :) - SQL server agent runs with the
account I need to invoke the Stored Procedure.
It would be nice however to be able to run different T-SQL Jobs with
different users.
John Bell escreveu:
> Hi
> I would suggest that you create an account to use for a service account and
> change the service account to be that.
> John
> "pedro.f.silva@.gmail.com" wrote:
> > I had noticed that there was a "run as" user on advanced options but it
> > didn't work.
> >
> > I was expecting it (no working) since there's no place to specify the
> > user password. How could it run with that identify if I hadn't
> > specified the password? But there's no place to do it!
> >
> >
> > John Bell escreveu:
> > > Hi
> > >
> > > With a T-SQL step there is a run as user on the avanced options if you are
> > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > >
> > > John
> > >
> > > "pedro.f.silva@.gmail.com" wrote:
> > >
> > > > It is using LOCALSYSTEM, I think.
> > > >
> > > > But I would like to use a domain account just for this job. Is it
> > > > possible?
> > > >
> > > > Why cannot i use "run as" on job step?
> > > >
> > > > I thought that could be done creating a credential and a proxy but SQL
> > > > server agent doesn't allow proxys on T-SQL steps.
> > > >
> > > > What can I do?
> > > >
> > > > John Bell escreveu:
> > > > > Hi
> > > > >
> > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > >
> > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > >
> > > > > John
> > > > >
> > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > server to connect to a remote DB.
> > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > error:
> > > > > >
> > > > > > Access to the remote server is denied because the current security
> > > > > > context is not trusted
> > > > > >
> > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > >
> > > > > > (The user exists on local and remote server).
> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >|||Hi
I don't know what your job is doing or why it needs to communicate with the
remote server, but it seems to be your only option with your current
implementation. If you used SQL 2005 there are more alternatives.
John
"pedro.f.silva@.gmail.com" wrote:
> Thanks John.
> That works if I want all my Jobs to run with the same user.
> Since I only have one it's ok :) - SQL server agent runs with the
> account I need to invoke the Stored Procedure.
> It would be nice however to be able to run different T-SQL Jobs with
> different users.
>
> John Bell escreveu:
> > Hi
> >
> > I would suggest that you create an account to use for a service account and
> > change the service account to be that.
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > I had noticed that there was a "run as" user on advanced options but it
> > > didn't work.
> > >
> > > I was expecting it (no working) since there's no place to specify the
> > > user password. How could it run with that identify if I hadn't
> > > specified the password? But there's no place to do it!
> > >
> > >
> > > John Bell escreveu:
> > > > Hi
> > > >
> > > > With a T-SQL step there is a run as user on the avanced options if you are
> > > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > > >
> > > > John
> > > >
> > > > "pedro.f.silva@.gmail.com" wrote:
> > > >
> > > > > It is using LOCALSYSTEM, I think.
> > > > >
> > > > > But I would like to use a domain account just for this job. Is it
> > > > > possible?
> > > > >
> > > > > Why cannot i use "run as" on job step?
> > > > >
> > > > > I thought that could be done creating a credential and a proxy but SQL
> > > > > server agent doesn't allow proxys on T-SQL steps.
> > > > >
> > > > > What can I do?
> > > > >
> > > > > John Bell escreveu:
> > > > > > Hi
> > > > > >
> > > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > > >
> > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > > server to connect to a remote DB.
> > > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > > error:
> > > > > > >
> > > > > > > Access to the remote server is denied because the current security
> > > > > > > context is not trusted
> > > > > > >
> > > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > > >
> > > > > > > (The user exists on local and remote server).
> > > > > > >
> > > > > > > Thanks
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>|||I am using SQL Server 2005 :)
Which alternatives do I have?
John Bell escreveu:
> Hi
> I don't know what your job is doing or why it needs to communicate with the
> remote server, but it seems to be your only option with your current
> implementation. If you used SQL 2005 there are more alternatives.
> John
> "pedro.f.silva@.gmail.com" wrote:
> > Thanks John.
> >
> > That works if I want all my Jobs to run with the same user.
> >
> > Since I only have one it's ok :) - SQL server agent runs with the
> > account I need to invoke the Stored Procedure.
> >
> > It would be nice however to be able to run different T-SQL Jobs with
> > different users.
> >
> >
> > John Bell escreveu:
> > > Hi
> > >
> > > I would suggest that you create an account to use for a service account and
> > > change the service account to be that.
> > >
> > > John
> > >
> > > "pedro.f.silva@.gmail.com" wrote:
> > >
> > > > I had noticed that there was a "run as" user on advanced options but it
> > > > didn't work.
> > > >
> > > > I was expecting it (no working) since there's no place to specify the
> > > > user password. How could it run with that identify if I hadn't
> > > > specified the password? But there's no place to do it!
> > > >
> > > >
> > > > John Bell escreveu:
> > > > > Hi
> > > > >
> > > > > With a T-SQL step there is a run as user on the avanced options if you are
> > > > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > > > >
> > > > > John
> > > > >
> > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > >
> > > > > > It is using LOCALSYSTEM, I think.
> > > > > >
> > > > > > But I would like to use a domain account just for this job. Is it
> > > > > > possible?
> > > > > >
> > > > > > Why cannot i use "run as" on job step?
> > > > > >
> > > > > > I thought that could be done creating a credential and a proxy but SQL
> > > > > > server agent doesn't allow proxys on T-SQL steps.
> > > > > >
> > > > > > What can I do?
> > > > > >
> > > > > > John Bell escreveu:
> > > > > > > Hi
> > > > > > >
> > > > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > > > >
> > > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > > > >
> > > > > > > John
> > > > > > >
> > > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > > >
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > > > server to connect to a remote DB.
> > > > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > > > error:
> > > > > > > >
> > > > > > > > Access to the remote server is denied because the current security
> > > > > > > > context is not trusted
> > > > > > > >
> > > > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > > > >
> > > > > > > > (The user exists on local and remote server).
> > > > > > > >
> > > > > > > > Thanks
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >|||Hi
Look at the using extensions to the EXECUTE command. But first read
http://www.sommarskog.se/grantperm.html
John
"pedro.f.silva@.gmail.com" wrote:
> I am using SQL Server 2005 :)
> Which alternatives do I have?
> John Bell escreveu:
> > Hi
> >
> > I don't know what your job is doing or why it needs to communicate with the
> > remote server, but it seems to be your only option with your current
> > implementation. If you used SQL 2005 there are more alternatives.
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > Thanks John.
> > >
> > > That works if I want all my Jobs to run with the same user.
> > >
> > > Since I only have one it's ok :) - SQL server agent runs with the
> > > account I need to invoke the Stored Procedure.
> > >
> > > It would be nice however to be able to run different T-SQL Jobs with
> > > different users.
> > >
> > >
> > > John Bell escreveu:
> > > > Hi
> > > >
> > > > I would suggest that you create an account to use for a service account and
> > > > change the service account to be that.
> > > >
> > > > John
> > > >
> > > > "pedro.f.silva@.gmail.com" wrote:
> > > >
> > > > > I had noticed that there was a "run as" user on advanced options but it
> > > > > didn't work.
> > > > >
> > > > > I was expecting it (no working) since there's no place to specify the
> > > > > user password. How could it run with that identify if I hadn't
> > > > > specified the password? But there's no place to do it!
> > > > >
> > > > >
> > > > > John Bell escreveu:
> > > > > > Hi
> > > > > >
> > > > > > With a T-SQL step there is a run as user on the avanced options if you are
> > > > > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > >
> > > > > > > It is using LOCALSYSTEM, I think.
> > > > > > >
> > > > > > > But I would like to use a domain account just for this job. Is it
> > > > > > > possible?
> > > > > > >
> > > > > > > Why cannot i use "run as" on job step?
> > > > > > >
> > > > > > > I thought that could be done creating a credential and a proxy but SQL
> > > > > > > server agent doesn't allow proxys on T-SQL steps.
> > > > > > >
> > > > > > > What can I do?
> > > > > > >
> > > > > > > John Bell escreveu:
> > > > > > > > Hi
> > > > > > > >
> > > > > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > > > > >
> > > > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > > > > >
> > > > > > > > John
> > > > > > > >
> > > > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > > > >
> > > > > > > > > Hi,
> > > > > > > > >
> > > > > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > > > > server to connect to a remote DB.
> > > > > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > > > > error:
> > > > > > > > >
> > > > > > > > > Access to the remote server is denied because the current security
> > > > > > > > > context is not trusted
> > > > > > > > >
> > > > > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > > > > >
> > > > > > > > > (The user exists on local and remote server).
> > > > > > > > >
> > > > > > > > > Thanks
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>sql
I whised to run a T-SQL script as a SQL server Job that uses a linked
server to connect to a remote DB.
When I run the stored procedure in Query Analyser (with a specified
user) everything works fone but on SQL Agent I have the following
error:
Access to the remote server is denied because the current security
context is not trusted
How can I specify with wich user should the step run? The "run as" list
on Job Step window is disabled. How to use the new credential?
(The user exists on local and remote server).
ThanksHi
You don't say if your SQL Agent Service is running under a domain account or
not? At a guess you are using LOCALSYSTEM? See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
John
"pedro.f.silva@.gmail.com" wrote:
> Hi,
> I whised to run a T-SQL script as a SQL server Job that uses a linked
> server to connect to a remote DB.
> When I run the stored procedure in Query Analyser (with a specified
> user) everything works fone but on SQL Agent I have the following
> error:
> Access to the remote server is denied because the current security
> context is not trusted
> How can I specify with wich user should the step run? The "run as" list
> on Job Step window is disabled. How to use the new credential?
> (The user exists on local and remote server).
> Thanks
>|||It is using LOCALSYSTEM, I think.
But I would like to use a domain account just for this job. Is it
possible?
Why cannot i use "run as" on job step?
I thought that could be done creating a credential and a proxy but SQL
server agent doesn't allow proxys on T-SQL steps.
What can I do?
John Bell escreveu:
> Hi
> You don't say if your SQL Agent Service is running under a domain account or
> not? At a guess you are using LOCALSYSTEM? See
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> John
> "pedro.f.silva@.gmail.com" wrote:
> > Hi,
> >
> > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > server to connect to a remote DB.
> > When I run the stored procedure in Query Analyser (with a specified
> > user) everything works fone but on SQL Agent I have the following
> > error:
> >
> > Access to the remote server is denied because the current security
> > context is not trusted
> >
> > How can I specify with wich user should the step run? The "run as" list
> > on Job Step window is disabled. How to use the new credential?
> >
> > (The user exists on local and remote server).
> >
> > Thanks
> >
> >|||Hi
With a T-SQL step there is a run as user on the avanced options if you are
running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
John
"pedro.f.silva@.gmail.com" wrote:
> It is using LOCALSYSTEM, I think.
> But I would like to use a domain account just for this job. Is it
> possible?
> Why cannot i use "run as" on job step?
> I thought that could be done creating a credential and a proxy but SQL
> server agent doesn't allow proxys on T-SQL steps.
> What can I do?
> John Bell escreveu:
> > Hi
> >
> > You don't say if your SQL Agent Service is running under a domain account or
> > not? At a guess you are using LOCALSYSTEM? See
> >
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > Hi,
> > >
> > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > server to connect to a remote DB.
> > > When I run the stored procedure in Query Analyser (with a specified
> > > user) everything works fone but on SQL Agent I have the following
> > > error:
> > >
> > > Access to the remote server is denied because the current security
> > > context is not trusted
> > >
> > > How can I specify with wich user should the step run? The "run as" list
> > > on Job Step window is disabled. How to use the new credential?
> > >
> > > (The user exists on local and remote server).
> > >
> > > Thanks
> > >
> > >
>|||I had noticed that there was a "run as" user on advanced options but it
didn't work.
I was expecting it (no working) since there's no place to specify the
user password. How could it run with that identify if I hadn't
specified the password? But there's no place to do it!
John Bell escreveu:
> Hi
> With a T-SQL step there is a run as user on the avanced options if you are
> running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> John
> "pedro.f.silva@.gmail.com" wrote:
> > It is using LOCALSYSTEM, I think.
> >
> > But I would like to use a domain account just for this job. Is it
> > possible?
> >
> > Why cannot i use "run as" on job step?
> >
> > I thought that could be done creating a credential and a proxy but SQL
> > server agent doesn't allow proxys on T-SQL steps.
> >
> > What can I do?
> >
> > John Bell escreveu:
> > > Hi
> > >
> > > You don't say if your SQL Agent Service is running under a domain account or
> > > not? At a guess you are using LOCALSYSTEM? See
> > >
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > >
> > > John
> > >
> > > "pedro.f.silva@.gmail.com" wrote:
> > >
> > > > Hi,
> > > >
> > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > server to connect to a remote DB.
> > > > When I run the stored procedure in Query Analyser (with a specified
> > > > user) everything works fone but on SQL Agent I have the following
> > > > error:
> > > >
> > > > Access to the remote server is denied because the current security
> > > > context is not trusted
> > > >
> > > > How can I specify with wich user should the step run? The "run as" list
> > > > on Job Step window is disabled. How to use the new credential?
> > > >
> > > > (The user exists on local and remote server).
> > > >
> > > > Thanks
> > > >
> > > >
> >
> >|||Hi
I would suggest that you create an account to use for a service account and
change the service account to be that.
John
"pedro.f.silva@.gmail.com" wrote:
> I had noticed that there was a "run as" user on advanced options but it
> didn't work.
> I was expecting it (no working) since there's no place to specify the
> user password. How could it run with that identify if I hadn't
> specified the password? But there's no place to do it!
>
> John Bell escreveu:
> > Hi
> >
> > With a T-SQL step there is a run as user on the avanced options if you are
> > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > It is using LOCALSYSTEM, I think.
> > >
> > > But I would like to use a domain account just for this job. Is it
> > > possible?
> > >
> > > Why cannot i use "run as" on job step?
> > >
> > > I thought that could be done creating a credential and a proxy but SQL
> > > server agent doesn't allow proxys on T-SQL steps.
> > >
> > > What can I do?
> > >
> > > John Bell escreveu:
> > > > Hi
> > > >
> > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > not? At a guess you are using LOCALSYSTEM? See
> > > >
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > >
> > > > John
> > > >
> > > > "pedro.f.silva@.gmail.com" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > server to connect to a remote DB.
> > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > user) everything works fone but on SQL Agent I have the following
> > > > > error:
> > > > >
> > > > > Access to the remote server is denied because the current security
> > > > > context is not trusted
> > > > >
> > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > on Job Step window is disabled. How to use the new credential?
> > > > >
> > > > > (The user exists on local and remote server).
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > >
> > >
>|||Thanks John.
That works if I want all my Jobs to run with the same user.
Since I only have one it's ok :) - SQL server agent runs with the
account I need to invoke the Stored Procedure.
It would be nice however to be able to run different T-SQL Jobs with
different users.
John Bell escreveu:
> Hi
> I would suggest that you create an account to use for a service account and
> change the service account to be that.
> John
> "pedro.f.silva@.gmail.com" wrote:
> > I had noticed that there was a "run as" user on advanced options but it
> > didn't work.
> >
> > I was expecting it (no working) since there's no place to specify the
> > user password. How could it run with that identify if I hadn't
> > specified the password? But there's no place to do it!
> >
> >
> > John Bell escreveu:
> > > Hi
> > >
> > > With a T-SQL step there is a run as user on the avanced options if you are
> > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > >
> > > John
> > >
> > > "pedro.f.silva@.gmail.com" wrote:
> > >
> > > > It is using LOCALSYSTEM, I think.
> > > >
> > > > But I would like to use a domain account just for this job. Is it
> > > > possible?
> > > >
> > > > Why cannot i use "run as" on job step?
> > > >
> > > > I thought that could be done creating a credential and a proxy but SQL
> > > > server agent doesn't allow proxys on T-SQL steps.
> > > >
> > > > What can I do?
> > > >
> > > > John Bell escreveu:
> > > > > Hi
> > > > >
> > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > >
> > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > >
> > > > > John
> > > > >
> > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > server to connect to a remote DB.
> > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > error:
> > > > > >
> > > > > > Access to the remote server is denied because the current security
> > > > > > context is not trusted
> > > > > >
> > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > >
> > > > > > (The user exists on local and remote server).
> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >|||Hi
I don't know what your job is doing or why it needs to communicate with the
remote server, but it seems to be your only option with your current
implementation. If you used SQL 2005 there are more alternatives.
John
"pedro.f.silva@.gmail.com" wrote:
> Thanks John.
> That works if I want all my Jobs to run with the same user.
> Since I only have one it's ok :) - SQL server agent runs with the
> account I need to invoke the Stored Procedure.
> It would be nice however to be able to run different T-SQL Jobs with
> different users.
>
> John Bell escreveu:
> > Hi
> >
> > I would suggest that you create an account to use for a service account and
> > change the service account to be that.
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > I had noticed that there was a "run as" user on advanced options but it
> > > didn't work.
> > >
> > > I was expecting it (no working) since there's no place to specify the
> > > user password. How could it run with that identify if I hadn't
> > > specified the password? But there's no place to do it!
> > >
> > >
> > > John Bell escreveu:
> > > > Hi
> > > >
> > > > With a T-SQL step there is a run as user on the avanced options if you are
> > > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > > >
> > > > John
> > > >
> > > > "pedro.f.silva@.gmail.com" wrote:
> > > >
> > > > > It is using LOCALSYSTEM, I think.
> > > > >
> > > > > But I would like to use a domain account just for this job. Is it
> > > > > possible?
> > > > >
> > > > > Why cannot i use "run as" on job step?
> > > > >
> > > > > I thought that could be done creating a credential and a proxy but SQL
> > > > > server agent doesn't allow proxys on T-SQL steps.
> > > > >
> > > > > What can I do?
> > > > >
> > > > > John Bell escreveu:
> > > > > > Hi
> > > > > >
> > > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > > >
> > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > > server to connect to a remote DB.
> > > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > > error:
> > > > > > >
> > > > > > > Access to the remote server is denied because the current security
> > > > > > > context is not trusted
> > > > > > >
> > > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > > >
> > > > > > > (The user exists on local and remote server).
> > > > > > >
> > > > > > > Thanks
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>|||I am using SQL Server 2005 :)
Which alternatives do I have?
John Bell escreveu:
> Hi
> I don't know what your job is doing or why it needs to communicate with the
> remote server, but it seems to be your only option with your current
> implementation. If you used SQL 2005 there are more alternatives.
> John
> "pedro.f.silva@.gmail.com" wrote:
> > Thanks John.
> >
> > That works if I want all my Jobs to run with the same user.
> >
> > Since I only have one it's ok :) - SQL server agent runs with the
> > account I need to invoke the Stored Procedure.
> >
> > It would be nice however to be able to run different T-SQL Jobs with
> > different users.
> >
> >
> > John Bell escreveu:
> > > Hi
> > >
> > > I would suggest that you create an account to use for a service account and
> > > change the service account to be that.
> > >
> > > John
> > >
> > > "pedro.f.silva@.gmail.com" wrote:
> > >
> > > > I had noticed that there was a "run as" user on advanced options but it
> > > > didn't work.
> > > >
> > > > I was expecting it (no working) since there's no place to specify the
> > > > user password. How could it run with that identify if I hadn't
> > > > specified the password? But there's no place to do it!
> > > >
> > > >
> > > > John Bell escreveu:
> > > > > Hi
> > > > >
> > > > > With a T-SQL step there is a run as user on the avanced options if you are
> > > > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > > > >
> > > > > John
> > > > >
> > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > >
> > > > > > It is using LOCALSYSTEM, I think.
> > > > > >
> > > > > > But I would like to use a domain account just for this job. Is it
> > > > > > possible?
> > > > > >
> > > > > > Why cannot i use "run as" on job step?
> > > > > >
> > > > > > I thought that could be done creating a credential and a proxy but SQL
> > > > > > server agent doesn't allow proxys on T-SQL steps.
> > > > > >
> > > > > > What can I do?
> > > > > >
> > > > > > John Bell escreveu:
> > > > > > > Hi
> > > > > > >
> > > > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > > > >
> > > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > > > >
> > > > > > > John
> > > > > > >
> > > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > > >
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > > > server to connect to a remote DB.
> > > > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > > > error:
> > > > > > > >
> > > > > > > > Access to the remote server is denied because the current security
> > > > > > > > context is not trusted
> > > > > > > >
> > > > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > > > >
> > > > > > > > (The user exists on local and remote server).
> > > > > > > >
> > > > > > > > Thanks
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >|||Hi
Look at the using extensions to the EXECUTE command. But first read
http://www.sommarskog.se/grantperm.html
John
"pedro.f.silva@.gmail.com" wrote:
> I am using SQL Server 2005 :)
> Which alternatives do I have?
> John Bell escreveu:
> > Hi
> >
> > I don't know what your job is doing or why it needs to communicate with the
> > remote server, but it seems to be your only option with your current
> > implementation. If you used SQL 2005 there are more alternatives.
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > Thanks John.
> > >
> > > That works if I want all my Jobs to run with the same user.
> > >
> > > Since I only have one it's ok :) - SQL server agent runs with the
> > > account I need to invoke the Stored Procedure.
> > >
> > > It would be nice however to be able to run different T-SQL Jobs with
> > > different users.
> > >
> > >
> > > John Bell escreveu:
> > > > Hi
> > > >
> > > > I would suggest that you create an account to use for a service account and
> > > > change the service account to be that.
> > > >
> > > > John
> > > >
> > > > "pedro.f.silva@.gmail.com" wrote:
> > > >
> > > > > I had noticed that there was a "run as" user on advanced options but it
> > > > > didn't work.
> > > > >
> > > > > I was expecting it (no working) since there's no place to specify the
> > > > > user password. How could it run with that identify if I hadn't
> > > > > specified the password? But there's no place to do it!
> > > > >
> > > > >
> > > > > John Bell escreveu:
> > > > > > Hi
> > > > > >
> > > > > > With a T-SQL step there is a run as user on the avanced options if you are
> > > > > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > >
> > > > > > > It is using LOCALSYSTEM, I think.
> > > > > > >
> > > > > > > But I would like to use a domain account just for this job. Is it
> > > > > > > possible?
> > > > > > >
> > > > > > > Why cannot i use "run as" on job step?
> > > > > > >
> > > > > > > I thought that could be done creating a credential and a proxy but SQL
> > > > > > > server agent doesn't allow proxys on T-SQL steps.
> > > > > > >
> > > > > > > What can I do?
> > > > > > >
> > > > > > > John Bell escreveu:
> > > > > > > > Hi
> > > > > > > >
> > > > > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > > > > >
> > > > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > > > > >
> > > > > > > > John
> > > > > > > >
> > > > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > > > >
> > > > > > > > > Hi,
> > > > > > > > >
> > > > > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > > > > server to connect to a remote DB.
> > > > > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > > > > error:
> > > > > > > > >
> > > > > > > > > Access to the remote server is denied because the current security
> > > > > > > > > context is not trusted
> > > > > > > > >
> > > > > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > > > > >
> > > > > > > > > (The user exists on local and remote server).
> > > > > > > > >
> > > > > > > > > Thanks
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>sql
Friday, March 23, 2012
Running Stored Procedures on Linked servers from Microsoft SQL server
Hi,
I am trying to invoke storedprocedures on a linked server from MS SQL SERVER and it fails to recognise the object as a SP rather it gives me a message saying that the object does not have any columns. The Linked server is a SYBASE server. I can access all the tables and views but not the procedures. By the way, I have set the following server options on for the linked sybase server --
RPC OUT
RPC
But the Use Remote Collations is not turned on.
Please help!!!!!!!!!!!!!!!
Regards
JCI ran into something similar before when trying to execute SPs on a linked server (although they both were MS SQL Server)...
I remember having to use a fully qualified procedure name when I executed it:
EXEC LinkedServerName.DbName.DbOwner.SPName xxx,xxx,...
I dunno if this is applicable with your situation as I have never linked to another db platform other than from MS SQL Server to MS Access. I've use Sybase ASE before...but never in a linked scenario...
Kael|||I got your email stating that using a fully qualified name didn't work.
An idea I have is maybe examining the parameters that were used for the sp_addlinkedserver stored proc when the link was made from Sybase to MS SQL Server. Try dropping the linked server and recreating it.
Also, you can try using the OpenQuery function in the FROM clause on SQL Server rather than using a fully qualified name.
Are you using the OLE DB Provider for Sybase SQL Server (or named something like that) as the data provider when the linked server SP was used? Maybe try updating the DLL for that provider. I'd use OLE DB rather than a generic provider since it will expose more functionality.
Good luck!
Kael
I am trying to invoke storedprocedures on a linked server from MS SQL SERVER and it fails to recognise the object as a SP rather it gives me a message saying that the object does not have any columns. The Linked server is a SYBASE server. I can access all the tables and views but not the procedures. By the way, I have set the following server options on for the linked sybase server --
RPC OUT
RPC
But the Use Remote Collations is not turned on.
Please help!!!!!!!!!!!!!!!
Regards
JCI ran into something similar before when trying to execute SPs on a linked server (although they both were MS SQL Server)...
I remember having to use a fully qualified procedure name when I executed it:
EXEC LinkedServerName.DbName.DbOwner.SPName xxx,xxx,...
I dunno if this is applicable with your situation as I have never linked to another db platform other than from MS SQL Server to MS Access. I've use Sybase ASE before...but never in a linked scenario...
Kael|||I got your email stating that using a fully qualified name didn't work.
An idea I have is maybe examining the parameters that were used for the sp_addlinkedserver stored proc when the link was made from Sybase to MS SQL Server. Try dropping the linked server and recreating it.
Also, you can try using the OpenQuery function in the FROM clause on SQL Server rather than using a fully qualified name.
Are you using the OLE DB Provider for Sybase SQL Server (or named something like that) as the data provider when the linked server SP was used? Maybe try updating the DLL for that provider. I'd use OLE DB rather than a generic provider since it will expose more functionality.
Good luck!
Kael
Tuesday, February 21, 2012
Running sp_refreshview causes error for view on linked server
When I try to run sp_refreshview on a view that accesses a table on a linked
server (after that table's structure has changed), I get the following error
:
Server: Msg 7391, Level 16, State 1, Procedure vwTest, Line 3
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Help!!
How do I ensure that the view on the linked server remains correct, after
the table structure of the table it references changes? I understand that
sp_refreshview will do the same as dropping and re-creating the view, but wh
y
doesn't it work on a linked server?
Also, is there a way to automate the refresh of the view after a schema
change on the table it references? Some kind of trigger on the table maybe?
But the trigger events do not include a schema change do they?
FredFred,
What happens if you right-click the view in QA and choose Edit to generate
an ALTER statement and then execute that statement?
This would retain your permissions as opposed to DROP / CREATE.
HTH
Jerry
"fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
news:DB681BC7-36C7-4FB6-A9C9-C52166A96C17@.microsoft.com...
> When I try to run sp_refreshview on a view that accesses a table on a
> linked
> server (after that table's structure has changed), I get the following
> error:
> Server: Msg 7391, Level 16, State 1, Procedure vwTest, Line 3
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Help!!
> How do I ensure that the view on the linked server remains correct, after
> the table structure of the table it references changes? I understand that
> sp_refreshview will do the same as dropping and re-creating the view, but
> why
> doesn't it work on a linked server?
> Also, is there a way to automate the refresh of the view after a schema
> change on the table it references? Some kind of trigger on the table
> maybe?
> But the trigger events do not include a schema change do they?
> Fred
>|||Thanks Jerry,
The Alter View statement works. It looks something like this:
ALTER VIEW ViewName
AS
select * from [LinkedServer\PRODUCTION].Database.dbo.TblName
However, my problem remains to automate the "refresh" of this view, every
time there is a schema change on the table (on the linked server) that it
references. Some kind of trigger based on the schema change maybe?
We have quite a few views that are based on tables on linked servers and the
person that makes the schema change to the referenced tables do not always
remember to refresh the views.
Fred
"Jerry Spivey" wrote:
> Fred,
> What happens if you right-click the view in QA and choose Edit to generat
e
> an ALTER statement and then execute that statement?
> This would retain your permissions as opposed to DROP / CREATE.
> HTH
> Jerry
> "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
> news:DB681BC7-36C7-4FB6-A9C9-C52166A96C17@.microsoft.com...
>
>|||I was able to get the sp_refreshview to work on the linked server setup, by
configuring MS DTC (Distributed Transaction Coordinator) for network access
-
see Microsoft Article ID 329332.
I still need to find a way to automate the "refresh" of this view, every
time there is a schema change on the table (on the linked server) that it
references.
Any ideas?
Fred
"fredscuba" wrote:
> Thanks Jerry,
> The Alter View statement works. It looks something like this:
> ALTER VIEW ViewName
> AS
> select * from [LinkedServer\PRODUCTION].Database.dbo.TblName
> However, my problem remains to automate the "refresh" of this view, every
> time there is a schema change on the table (on the linked server) that it
> references. Some kind of trigger based on the schema change maybe?
> We have quite a few views that are based on tables on linked servers and t
he
> person that makes the schema change to the referenced tables do not always
> remember to refresh the views.
>
> Fred
>
> "Jerry Spivey" wrote:
>
server (after that table's structure has changed), I get the following error
:
Server: Msg 7391, Level 16, State 1, Procedure vwTest, Line 3
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Help!!
How do I ensure that the view on the linked server remains correct, after
the table structure of the table it references changes? I understand that
sp_refreshview will do the same as dropping and re-creating the view, but wh
y
doesn't it work on a linked server?
Also, is there a way to automate the refresh of the view after a schema
change on the table it references? Some kind of trigger on the table maybe?
But the trigger events do not include a schema change do they?
FredFred,
What happens if you right-click the view in QA and choose Edit to generate
an ALTER statement and then execute that statement?
This would retain your permissions as opposed to DROP / CREATE.
HTH
Jerry
"fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
news:DB681BC7-36C7-4FB6-A9C9-C52166A96C17@.microsoft.com...
> When I try to run sp_refreshview on a view that accesses a table on a
> linked
> server (after that table's structure has changed), I get the following
> error:
> Server: Msg 7391, Level 16, State 1, Procedure vwTest, Line 3
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Help!!
> How do I ensure that the view on the linked server remains correct, after
> the table structure of the table it references changes? I understand that
> sp_refreshview will do the same as dropping and re-creating the view, but
> why
> doesn't it work on a linked server?
> Also, is there a way to automate the refresh of the view after a schema
> change on the table it references? Some kind of trigger on the table
> maybe?
> But the trigger events do not include a schema change do they?
> Fred
>|||Thanks Jerry,
The Alter View statement works. It looks something like this:
ALTER VIEW ViewName
AS
select * from [LinkedServer\PRODUCTION].Database.dbo.TblName
However, my problem remains to automate the "refresh" of this view, every
time there is a schema change on the table (on the linked server) that it
references. Some kind of trigger based on the schema change maybe?
We have quite a few views that are based on tables on linked servers and the
person that makes the schema change to the referenced tables do not always
remember to refresh the views.
Fred
"Jerry Spivey" wrote:
> Fred,
> What happens if you right-click the view in QA and choose Edit to generat
e
> an ALTER statement and then execute that statement?
> This would retain your permissions as opposed to DROP / CREATE.
> HTH
> Jerry
> "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
> news:DB681BC7-36C7-4FB6-A9C9-C52166A96C17@.microsoft.com...
>
>|||I was able to get the sp_refreshview to work on the linked server setup, by
configuring MS DTC (Distributed Transaction Coordinator) for network access
-
see Microsoft Article ID 329332.
I still need to find a way to automate the "refresh" of this view, every
time there is a schema change on the table (on the linked server) that it
references.
Any ideas?
Fred
"fredscuba" wrote:
> Thanks Jerry,
> The Alter View statement works. It looks something like this:
> ALTER VIEW ViewName
> AS
> select * from [LinkedServer\PRODUCTION].Database.dbo.TblName
> However, my problem remains to automate the "refresh" of this view, every
> time there is a schema change on the table (on the linked server) that it
> references. Some kind of trigger based on the schema change maybe?
> We have quite a few views that are based on tables on linked servers and t
he
> person that makes the schema change to the referenced tables do not always
> remember to refresh the views.
>
> Fred
>
> "Jerry Spivey" wrote:
>
Subscribe to:
Posts (Atom)