ODBC call fail/ Record Locked

I am in the process of moving native Access tables over to
SQL Server 7. These tables are updated through code.
Some are updated with SQL statements executed through a db
object and others are updated using DAO.
When I update using SQL, I get "This record is being
modified by another user. . . Save, Copy to Clipboard,
Drop Changes."
When I update using DAO, the code crashes on the .Update
command and says, "ODBC call fail."
I can update the tables manually without error.
Is there a way to fix this? What am I doing wrong in the
code?
Crystal
You should seriously consider getting rid of all DAO code that
performs DML against SQL Server tables. It's the slowest, buggiest,
and least-efficient way of performing any task. The reason is that you
are invoking an instance of the Jet engine on every call. The result
is that the call goes through Jet-ODBC-SQL Server. However, if you use
SQL statements in a pass-through query, the statement is passed
directly to SQL Server, where it is executed on the server. This
results in faster, more efficient transactions. Pass-through queries
also give you the capability of calling stored procedures, and can be
used as the basis of reports. If you must use recordsets for some
reason, use ADO, not DAO when going against SQL Server data.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Mon, 3 May 2004 06:32:13 -0700, "Crystal"
<anonymous@.discussions.microsoft.com> wrote:

>I am in the process of moving native Access tables over to
>SQL Server 7. These tables are updated through code.
>Some are updated with SQL statements executed through a db
>object and others are updated using DAO.
>When I update using SQL, I get "This record is being
>modified by another user. . . Save, Copy to Clipboard,
>Drop Changes."
>When I update using DAO, the code crashes on the .Update
>command and says, "ODBC call fail."
>I can update the tables manually without error.
>Is there a way to fix this? What am I doing wrong in the
>code?
>Crystal

ODBC call fail/ Record Locked

I am in the process of moving native Access tables over to
SQL Server 7. These tables are updated through code.
Some are updated with SQL statements executed through a db
object and others are updated using DAO.
When I update using SQL, I get "This record is being
modified by another user. . . Save, Copy to Clipboard,
Drop Changes."
When I update using DAO, the code crashes on the .Update
command and says, "ODBC call fail."
I can update the tables manually without error.
Is there a way to fix this? What am I doing wrong in the
code?
CrystalYou should seriously consider getting rid of all DAO code that
performs DML against SQL Server tables. It's the slowest, buggiest,
and least-efficient way of performing any task. The reason is that you
are invoking an instance of the Jet engine on every call. The result
is that the call goes through Jet-ODBC-SQL Server. However, if you use
SQL statements in a pass-through query, the statement is passed
directly to SQL Server, where it is executed on the server. This
results in faster, more efficient transactions. Pass-through queries
also give you the capability of calling stored procedures, and can be
used as the basis of reports. If you must use recordsets for some
reason, use ADO, not DAO when going against SQL Server data.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Mon, 3 May 2004 06:32:13 -0700, "Crystal"
<anonymous@.discussions.microsoft.com> wrote:

>I am in the process of moving native Access tables over to
>SQL Server 7. These tables are updated through code.
>Some are updated with SQL statements executed through a db
>object and others are updated using DAO.
>When I update using SQL, I get "This record is being
>modified by another user. . . Save, Copy to Clipboard,
>Drop Changes."
>When I update using DAO, the code crashes on the .Update
>command and says, "ODBC call fail."
>I can update the tables manually without error.
>Is there a way to fix this? What am I doing wrong in the
>code?
>Crystal

ODBC and internet access

We are trying to restict internet access from certin computers but, it's still in place because we need to process credit cards. Is it possible to remove internet access and allow our SQL based program to still run credit cards because of the ODBC connection to the SQL server which has internet access?

ODBC connects via SQL Native Client which takes in the server name/ip address of the machine to connect. As long as your app can connect to sql server via an intranet connection, you do not need to have internet connection. However, if the only link between your app and the server is the internet, then you can't disable internet on the ODBC connection.

HTH

|||I don′t know if I got the situation described, is it like the following: ?

App -- Internet SQLServer

What is your actual concern here ? That the SQL Server is exposed to the internet ? Or that the app has internet access ?

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

In the case where you're trying to restrict access to the server to clients from specific IP address, you can do so using TDS endpoints. Please take a look at http://msdn2.microsoft.com/en-us/library/ms191220.aspx.

Thanks,

Il-Sung.

odbc - start transaction - open query - new process

I try to do:
- open transaction
- open cursor nr 1
- open cursor nr 2
- insert
- commit

for cursor nr 2 and for insert new connection establishes

When next time I try to do the same I gen dead lock

What for is this new connection (what should I do - it makes probles)
Any suggestions?I'm not sure, just guessing (not too great with cursors)
are you closing your cursors?|||I close them but after insert

When I close cursor befour open next curosr or insert there is no problem. It happens only during transaction

Thx for replay

Obtaining Scripts

I need to script a couple of things that I do through enterprise manager...is there a way to do a process (such as clear connections/detach database) through enterprise manager and then copy the script that sql ran in the back ground?
Best way is to run profiler and capture the commands while performing your tasks on Ent. Manager.
"Jamie Elliott" wrote:

> I need to script a couple of things that I do through enterprise manager...is there a way to do a process (such as clear connections/detach database) through enterprise manager and then copy the script that sql ran in the back ground?
|||Standart Trace template will be fine ..
"Jamie Elliott" wrote:
[vbcol=seagreen]
> when setting up profiler...is there any certain setting I should give it?
> "Umut Nazlica" wrote:

Obtaining Scripts

I need to script a couple of things that I do through enterprise manager...i
s there a way to do a process (such as clear connections/detach database) th
rough enterprise manager and then copy the script that sql ran in the back g
round?Best way is to run profiler and capture the commands while performing your t
asks on Ent. Manager.
"Jamie Elliott" wrote:

> I need to script a couple of things that I do through enterprise manager...is ther
e a way to do a process (such as clear connections/detach database) through enterpri
se manager and then copy the script that sql ran in the back ground?|||Standart Trace template will be fine ..
"Jamie Elliott" wrote:
[vbcol=seagreen]
> when setting up profiler...is there any certain setting I should give it?
> "Umut Nazlica" wrote:
>

obtaining disk space

Hi,
I'm new to these forums (and to SQL Server), so please be gentle with me.

I am developing a process to obtain information on all our remote servers/databases, and store it in a single local database.
I'm after things like db size, last backup date, free drive space etc...the usual weekly statistics.

I've linked the remote servers to my local one, and have written a few simple procedures (which exist on the local server) to grab backup and file size information from the remote tables. The output is stored locally in tables which we can then query as necessary.

I am having difficulty obtaining the free drive space details.
I'm using :-
'exec <remote_server>.master.dbo.xp_fixeddrives' to get the info, but I cannot store the output in a table on the local server. (remote_server_name, date, drive_letter, space_mb)

I wish to avoid creating any objects on the remote servers if at all possible.
I really want to pass the remote server name into the procedure, and the output to be inserted into the table.

Many thanksset nocount on

create table #FreeSpace(Drive char(1), MB_Free int)

insert into #FreeSpace exec master..xp_fixeddrives

drop table #FreeSpace
go|||Thanks for the reply Blindman, I think the issue is that the insert is being turned into a distributed transaction when the "xp_fixeddisks" function is being called eg...

"insert into my_local_database.dbo.disk_space (drive,mb_free)
exec the_remote_server.master.dbo.xp_fixeddrives"

returns the error "MSDTC on server 'my_local_server' is unavailable".
Whereas the sql to get the file size stats, uses pure sql rather than the exec and works :-

"insert into my_local_database.dbo.db_growth select 'the_remote_server','northwind', getdate() as timestamps, *
from the_remote_server.northwind.dbo.sysfiles"

If it is run locally, the insert works :-
"insert into my_local_database.dbo.disk_space (drive,mb_free) exec master.dbo.xp_fixeddrives"

it is the "exec the_remote_server......" bit that is giving me problems.

I could get it to work by using two processes - (1) the remote server runs the function and inserts into a table of it's own, then (2) my_local_server grabs that data and inserts into the central table, but I'd like to avoid creating tables or making too many config changes on the remote servers if at all possible.

Thanks.|||I'd like to avoid creating tables or making too many config changes on the remote servers if at all possible.

Thanks.
No changes on remote server.

create table FreeSpace
(
Drive nvarchar(5),
MB_Free nvarchar(20)
)
go
create Procedure remoteServerDiskSpace_sp (
@.servername nvarchar(4000),
@.FileName varchar(50)
)
AS
declare @.bulksql varchar(200)
Begin


SET @.servername = 'osql -Slocalhost -E -q"SET NOCOUNT ON; exec ' + @.servername +' .master.dbo.xp_fixeddrives" -s"," -n -w8000 -o"' + @.FileName +'"'

Exec master..xp_cmdshell @.servername, NO_OUTPUT
set @.bulksql='BULK INSERT FreeSpace FROM '''+@.FileName+''' WITH (FIELDTERMINATOR = '','',FirstRow=3)'
exec(@.bulksql)
END
GO
-- execute query
exec remoteServerDiskSpace_sp 'remoteserverName','c:\DiskSpace.csv'
--select query
select * from FreeSpace|||Thanks Mallier,
I've now done something in a similar vein to your routine, and it works a treat.

Obtaining different timings for the same process?

Dear fellows,
The following loop bring me differents results, execute one after one (among
them a truncate table, of course):
DECLARE @.loop as integer
set @.loop = 1
while @.loop < 10000
begin
insert into A_test(id,nombre,ape) values(@.loop,'a','aadfasdf')
set @.loop = @.loop + 1
end
1st: 26 sec.
2nd: 30 sec.
3rd: 32 sec.
What's happening?
Any input would be much appreciated.
Enric"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:F66A1DC3-E5CD-4B06-AE4E-53818843E3C5@.microsoft.com...
> Dear fellows,
> The following loop bring me differents results, execute one after one
> (among
> them a truncate table, of course):
> DECLARE @.loop as integer
> set @.loop = 1
> while @.loop < 10000
> begin
> insert into A_test(id,nombre,ape) values(@.loop,'a','aadfasdf')
> set @.loop = @.loop + 1
> end
>
> 1st: 26 sec.
> 2nd: 30 sec.
> 3rd: 32 sec.
> What's happening?
> Any input would be much appreciated.
> Enric
What other processes are running on your server while this is running.
Page splitting or allocation of new extents to store your data may be
happening.
Indexes may be getting updated
Statistics may be getting updated
It's hard to say what the real culprit is. These are some things that I
would check however.
Rick Sawtell
MCT, MCSD, MCDBA|||Well, what does your Profiler say?
ML
http://milambda.blogspot.com/|||Thanks to both for the quick responses but I was wondering about the
difference among them. It seems very high. We are talking about 6 or 7
seconds for a total of 35 more or less..
"Rick Sawtell" wrote:

> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:F66A1DC3-E5CD-4B06-AE4E-53818843E3C5@.microsoft.com...
> What other processes are running on your server while this is running.
> Page splitting or allocation of new extents to store your data may be
> happening.
> Indexes may be getting updated
> Statistics may be getting updated
>
> It's hard to say what the real culprit is. These are some things that I
> would check however.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Obtaining CD in the UK

Anybody had any luck with this... It's hardly an obvious process is it
?Yea i have got the CD. If you got your SQL licenses through OSL agreement
(like us) then get in contact with the guys who supplied your agreement and
they should be able to send it to you. It should be free apart from the
media cost, about £20 for us.
Mark
"mt@.calluk.com" wrote:
> Anybody had any luck with this... It's hardly an obvious process is it
> ?
>

Obtain values from different tables

Table processes_user
id_user | id_proc
--
35 | 17001
100 | 1089
35 | 17002
Table processes_flow
(example:the process 17001 is with the user 35 and the deadline of the
flow is 2006-07-30! As you can see there are 2 entries on the table
below, the first with a lower id references an old flow, but I want to
get the deadline of the last flow of the process)
--
id | id_proc | deadline
--
10| 17001 | null
12| 1089 | 2006-05-12
15| 17001 | 2006-07-30
20| 17002 | null
--
I would like to get for the user 35 the following info:
17001 | 2006-07-30
17002 | null
How can I do this with a sql command? I would like you to share some
ideas because I'm stuck with this.
Regards,
Hugo SantosSELECT a.id_user, a.id_proc, b.deadline
FROM processes_user a
LEFT JOIN (SELECT id_proc, MAX(deadline) as deadline
FROM processes_flow
GROUP BY id_proc) b ON a.id_proc = b.id_proc
WHERE a.id_user= 35
Untested.
Stu
hugonsan...@.gmail.com wrote:
> Table processes_user
> id_user | id_proc
> --
> 35 | 17001
> 100 | 1089
> 35 | 17002
>
> Table processes_flow
> (example:the process 17001 is with the user 35 and the deadline of the
> flow is 2006-07-30! As you can see there are 2 entries on the table
> below, the first with a lower id references an old flow, but I want to
> get the deadline of the last flow of the process)
> --
> id | id_proc | deadline
> --
> 10| 17001 | null
> 12| 1089 | 2006-05-12
> 15| 17001 | 2006-07-30
> 20| 17002 | null
> --
> I would like to get for the user 35 the following info:
> 17001 | 2006-07-30
> 17002 | null
> How can I do this with a sql command? I would like you to share some
> ideas because I'm stuck with this.
> Regards,
> Hugo Santos|||Stu wrote:
> SELECT a.id_user, a.id_proc, b.deadline
> FROM processes_user a
> LEFT JOIN (SELECT id_proc, MAX(deadline) as deadline
> FROM processes_flow
> GROUP BY id_proc) b ON a.id_proc = b.id_proc
> WHERE a.id_user= 35
>
> Untested.
> Stu
>
Thanks for your reply Stu, but with that aren't you going to get the
max deadline only? I want to show the deadline from the last flow.
For example.. on the first flow you may have a deadline and on the
second the deadline is null. And I want to get the deadline from the
last flow... which is null.
Can you clear this out?|||I'm sorry, I made an assumption that may or may not be true; I'm
assuming that NULL comes before a deadline. In that case, the subquery
will only return rows that have a deadline associated with them; doing
a LEFT JOIN will return the MAX(deadline) if there is one, and NULL if
there is not.
hugos wrote:
> Stu wrote:
> Thanks for your reply Stu, but with that aren't you going to get the
> max deadline only? I want to show the deadline from the last flow.
> For example.. on the first flow you may have a deadline and on the
> second the deadline is null. And I want to get the deadline from the
> last flow... which is null.
> Can you clear this out?|||Thanks.
I already found a different way! I think it's not optimized but it will
work for now ;)
Stu escreveu:
> I'm sorry, I made an assumption that may or may not be true; I'm
> assuming that NULL comes before a deadline. In that case, the subquery
> will only return rows that have a deadline associated with them; doing
> a LEFT JOIN will return the MAX(deadline) if there is one, and NULL if
> there is not.

Obtain the query plan of a running process

Hi,

Is there a way to findout the query plan of the executing process using
the SPID/KPID information.

Thanks in advance,
Thyagu.DOne way to do this task is: Set up a Profile Trace and add Show Plan
event , save the info , and filter with the SPID

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
Make SQL Server faster - www.quicksqlserver.com
___________________________________

"Thyagu" <tdelli@.gmail.comwrote in message
news:1158834419.604983.220140@.m7g2000cwm.googlegro ups.com...

Quote:

Originally Posted by

Hi,
>
Is there a way to findout the query plan of the executing process using
the SPID/KPID information.
>
Thanks in advance,
Thyagu.D
>