deAttach database: sqlserver

Showing posts with label sqlserver. Show all posts

Tuesday, March 27, 2012

Deadlocks after upgrade to SQLServer 2000

0x4FF839C8) Value:0x291ff8c0 Cost

0/0)
spid4
spid4 Node:2
spid4 PAG: 8:1:45964 CleanCnt:1 Mode: SIU Flags: 0x2
spid4 Grant List 2::
spid4 Owner:0x29400060 Mode: S Flg:0x0 Ref:0 Life:00000001
SPID:56 ECID:0
spid4 SPID: 56 ECID: 0 Statement Type: UPDATE Line #: 1
spid4 Input Buf: Language Event: UPDATE Import SET
import_record_status = 0 ,import_record_text = NULL WHERE import_record_inde
x
BETWEEN 0 AND 50 AND import_record_status > 0
spid4 Requested By:
spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:59 ECID:0
Ec

0x5A11F9C8) Value:0x291adac0 Cost

0/0)
spid4 Victim Resource Owner:
spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:59 ECID:0
Ec

0x5A11F9C8) Value:0x291adac0 Cost

0/0)
spid4
This looks like a resource order conflict, where SPID 59 holds a Shared lock
on page A and request a IX lock on page B, while SPID 56 holds a Shared lock
on Page B and requests IX on Page A. As much as I could see using Profiler,
the statements are not within a transaction, transaction isolation level is
READ COMMITTED. How can this happen? And how can I avoid this?
Page information (ObjId 1892917815 = Import):
m_pageId = (1:2412) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 1892917815 m_indexId = 0 m_prevPage = (1:89846)
m_nextPage = (1:60805) pminlen = 52 m_slotCnt = 9
m_pageId = (1:45964) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 1892917815 m_indexId = 0 m_prevPage = (1:2075)
m_nextPage = (1:62881) pminlen = 52 m_slotCnt = 12
Table definition:
CREATE TABLE [Import] (
[import_record_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ID
] DEFAULT
(newid()),
[import_record_index] [int] NOT NULL CONSTRAINT [DF_import_idx]
DEFAULT (0),
[import_id] [uniqueidentifier] NULL ,
[import_record_date] [datetime] NULL CONSTRAINT [DF_import_date]
DEFAULT
(getdate()),
[import_record_status] [int] NULL CONSTRAINT [DF_import_status]
DEFAULT (1),
[import_record_text] [varchar] (50) NULL ,
[field1] [varchar] (10) NULL ,
[field2] [varchar] (1) NULL ,
[field3] [varchar] (2) NULL ,
..
CONSTRAINT [PK_Import] PRIMARY KEY CLUSTERED
(
[import_record_id]
)
One problem maybe could be the clustered index on uniqueidentifier? There
are no other indexes on this table. I tried to change the PK to be a non
clustered index and defined a clustered one over date/index, but cant tell
yet if this helps or not. I have more examples if this could be useful.
Thanks,
PatrickPatrick wrote:
> Hi,
> Since we upgraded from SQLServer 7 to 2000 we get deadlocks that we
> didnt get before. After upgrading we did a transfer of the database,
> rebuild indexes, update statistics. As much as I know, nothing has
> changed in our application code. Maybe someone can help?
> <SNIP>
Well, you're correct that a clustered index on a UNIQUEIDENTIFIER can
cause problems on a busy table. it will likely cause page splitting as
each row is inserted into the table, which will slow down the
transaction, cause excessive page reads and writes, increase blocking
times, and make it more likely that a deadlock can occur. If you've made
the change in the clustered index already, then you may just want to
wait and see if it helps. If not, examine the performance of your
queries to make sure they are running in an optimized fashion.
David Gugick
Quest Software
www.imceda.com
www.quest.comsql

Deadlocks after upgrade to SQLServer 2000

0x4FF839C8) Value:0x291ff8c0 Cost

0/0)
spid4
spid4 Node:2
spid4 PAG: 8:1:45964 CleanCnt:1 Mode: SIU Flags: 0x2
spid4 Grant List 2::
spid4 Owner:0x29400060 Mode: S Flg:0x0 Ref:0 Life:00000001
SPID:56 ECID:0
spid4 SPID: 56 ECID: 0 Statement Type: UPDATE Line #: 1
spid4 Input Buf: Language Event: UPDATE Import SET
import_record_status = 0 ,import_record_text = NULL WHERE import_record_index
BETWEEN 0 AND 50 AND import_record_status > 0
spid4 Requested By:
spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:59 ECID:0
Ec

0x5A11F9C8) Value:0x291adac0 Cost

0/0)
spid4 Victim Resource Owner:
spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:59 ECID:0
Ec

0x5A11F9C8) Value:0x291adac0 Cost

0/0)
spid4
This looks like a resource order conflict, where SPID 59 holds a Shared lock
on page A and request a IX lock on page B, while SPID 56 holds a Shared lock
on Page B and requests IX on Page A. As much as I could see using Profiler,
the statements are not within a transaction, transaction isolation level is
READ COMMITTED. How can this happen? And how can I avoid this?
Page information (ObjId 1892917815 = Import):
m_pageId = (1:2412) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 1892917815 m_indexId = 0 m_prevPage = (1:89846)
m_nextPage = (1:60805) pminlen = 52 m_slotCnt = 9
m_pageId = (1:45964) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 1892917815 m_indexId = 0 m_prevPage = (1:2075)
m_nextPage = (1:62881) pminlen = 52 m_slotCnt = 12
Table definition:
CREATE TABLE [Import] (
[import_record_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ID] DEFAULT
(newid()),
[import_record_index] [int] NOT NULL CONSTRAINT [DF_import_idx] DEFAULT (0),
[import_id] [uniqueidentifier] NULL ,
[import_record_date] [datetime] NULL CONSTRAINT [DF_import_date] DEFAULT
(getdate()),
[import_record_status] [int] NULL CONSTRAINT [DF_import_status] DEFAULT (1),
[import_record_text] [varchar] (50) NULL ,
[field1] [varchar] (10) NULL ,
[field2] [varchar] (1) NULL ,
[field3] [varchar] (2) NULL ,
...
CONSTRAINT [PK_Import] PRIMARY KEY CLUSTERED
(
[import_record_id]
)
One problem maybe could be the clustered index on uniqueidentifier? There
are no other indexes on this table. I tried to change the PK to be a non
clustered index and defined a clustered one over date/index, but cant tell
yet if this helps or not. I have more examples if this could be useful.
Thanks,
Patrick
Patrick wrote:
> Hi,
> Since we upgraded from SQLServer 7 to 2000 we get deadlocks that we
> didnt get before. After upgrading we did a transfer of the database,
> rebuild indexes, update statistics. As much as I know, nothing has
> changed in our application code. Maybe someone can help?
> <SNIP>
Well, you're correct that a clustered index on a UNIQUEIDENTIFIER can
cause problems on a busy table. it will likely cause page splitting as
each row is inserted into the table, which will slow down the
transaction, cause excessive page reads and writes, increase blocking
times, and make it more likely that a deadlock can occur. If you've made
the change in the clustered index already, then you may just want to
wait and see if it helps. If not, examine the performance of your
queries to make sure they are running in an optimized fashion.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Deadlocks after upgrade to SQLServer 2000

Hi,
Since we upgraded from SQLServer 7 to 2000 we get deadlocks that we didnt
get before. After upgrading we did a transfer of the database, rebuild
indexes, update statistics. As much as I know, nothing has changed in our
application code. Maybe someone can help?
Here is an example:
spid4 Node:1
spid4 PAG: 8:1:2412 CleanCnt:1 Mode: SIU Flags: 0x2
spid4 Grant List 2::
spid4 Owner:0x292d8520 Mode: S Flg:0x0 Ref:0 Life:00000001
SPID:59 ECID:0
spid4 SPID: 59 ECID: 0 Statement Type: UPDATE Line #: 1
spid4 Input Buf: Language Event: UPDATE Import SET
import_record_status = 0 ,import_record_text = NULL WHERE import_record_index
BETWEEN 0 AND 50 AND import_record_status > 0
spid4 Requested By:
spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:56 ECID:0
Ec:(0x4FF839C8) Value:0x291ff8c0 Cost:(0/0)
spid4
spid4 Node:2
spid4 PAG: 8:1:45964 CleanCnt:1 Mode: SIU Flags: 0x2
spid4 Grant List 2::
spid4 Owner:0x29400060 Mode: S Flg:0x0 Ref:0 Life:00000001
SPID:56 ECID:0
spid4 SPID: 56 ECID: 0 Statement Type: UPDATE Line #: 1
spid4 Input Buf: Language Event: UPDATE Import SET
import_record_status = 0 ,import_record_text = NULL WHERE import_record_index
BETWEEN 0 AND 50 AND import_record_status > 0
spid4 Requested By:
spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:59 ECID:0
Ec:(0x5A11F9C8) Value:0x291adac0 Cost:(0/0)
spid4 Victim Resource Owner:
spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:59 ECID:0
Ec:(0x5A11F9C8) Value:0x291adac0 Cost:(0/0)
spid4
This looks like a resource order conflict, where SPID 59 holds a Shared lock
on page A and request a IX lock on page B, while SPID 56 holds a Shared lock
on Page B and requests IX on Page A. As much as I could see using Profiler,
the statements are not within a transaction, transaction isolation level is
READ COMMITTED. How can this happen? And how can I avoid this?
Page information (ObjId 1892917815 = Import):
m_pageId = (1:2412) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 1892917815 m_indexId = 0 m_prevPage = (1:89846)
m_nextPage = (1:60805) pminlen = 52 m_slotCnt = 9
m_pageId = (1:45964) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 1892917815 m_indexId = 0 m_prevPage = (1:2075)
m_nextPage = (1:62881) pminlen = 52 m_slotCnt = 12
Table definition:
CREATE TABLE [Import] (
[import_record_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ID] DEFAULT
(newid()),
[import_record_index] [int] NOT NULL CONSTRAINT [DF_import_idx] DEFAULT (0),
[import_id] [uniqueidentifier] NULL ,
[import_record_date] [datetime] NULL CONSTRAINT [DF_import_date] DEFAULT
(getdate()),
[import_record_status] [int] NULL CONSTRAINT [DF_import_status] DEFAULT (1),
[import_record_text] [varchar] (50) NULL ,
[field1] [varchar] (10) NULL ,
[field2] [varchar] (1) NULL ,
[field3] [varchar] (2) NULL ,
...
CONSTRAINT [PK_Import] PRIMARY KEY CLUSTERED
(
[import_record_id]
)
One problem maybe could be the clustered index on uniqueidentifier? There
are no other indexes on this table. I tried to change the PK to be a non
clustered index and defined a clustered one over date/index, but cant tell
yet if this helps or not. I have more examples if this could be useful.
Thanks,
PatrickPatrick wrote:
> Hi,
> Since we upgraded from SQLServer 7 to 2000 we get deadlocks that we
> didnt get before. After upgrading we did a transfer of the database,
> rebuild indexes, update statistics. As much as I know, nothing has
> changed in our application code. Maybe someone can help?
> <SNIP>
Well, you're correct that a clustered index on a UNIQUEIDENTIFIER can
cause problems on a busy table. it will likely cause page splitting as
each row is inserted into the table, which will slow down the
transaction, cause excessive page reads and writes, increase blocking
times, and make it more likely that a deadlock can occur. If you've made
the change in the clustered index already, then you may just want to
wait and see if it helps. If not, examine the performance of your
queries to make sure they are running in an optimized fashion.
David Gugick
Quest Software
www.imceda.com
www.quest.com

deadlocks

Hi Everybody

I am new to sqlserver 2000.I know basics of locks.but i dont know how to
resolve deadlock issues.I am cofusing by reading articles with 90%
information and remaining 10% missing.Can any one help me which is the good
site to learn and resolve deadlocks.

Note: I create deadlock. when i try to trace deadlock using dbcc traceon
(1205,3604,-1).In error log showing nothing about the deadlock.

showing created traceon.......

Any help would be appreciated.

--
Message posted via http://www.sqlmonster.comSpecify trace flag 3605 instead of 3604 to write the deadlock info to the
SQL Server error log,

DBCC TRACEON (1205,3605,-1)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"pardhi via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in message
news:5cc8e2b282894cce84e1ab206f439136@.SQLMonster.c om...
> Hi Everybody
> I am new to sqlserver 2000.I know basics of locks.but i dont know how to
> resolve deadlock issues.I am cofusing by reading articles with 90%
> information and remaining 10% missing.Can any one help me which is the
> good
> site to learn and resolve deadlocks.
> Note: I create deadlock. when i try to trace deadlock using dbcc traceon
> (1205,3604,-1).In error log showing nothing about the deadlock.
> showing created traceon.......
> Any help would be appreciated.
> --
> Message posted via http://www.sqlmonster.com|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
> Specify trace flag 3605 instead of 3604 to write the deadlock info to the
> SQL Server error log,
> DBCC TRACEON (1205,3605,-1)

And to make it even better, use 1204, not 1205. 1205 writes deadlock
information as well I believe, but on an extremely detailed level.

Unfortunately, the output from 1204 is cryptic, and far from trivial
to understand.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

Have you looked at:
http://support.microsoft.com/kb/271509/EN-US/

Also "Inside SQL Server 2000" by Kalen Delany ISBN
0-7356-0998-5 is a good source for understanding and resolving blocking.

John

I Created deadlock and opened new page and typed the command
DBCC TRACEON (1205,3605,-1).
I didn't see any deadlock message except

(End deadlock search 9232 a deadlock was not found)

but in enterprise manager showing spid 54 blocking and spid 55 blocked.

even tried (1204).

Can you please tell step by step how to see locking.

i am using standard vresion

Thanks

--
Message posted via http://www.sqlmonster.com|||Hi Eland

I Created deadlock and opened new page and typed the command
DBCC TRACEON (1205,3605,-1).
I didn't see any deadlock message except

(End deadlock search 9232 a deadlock was not found)

but in enterprise manager showing spid 54 blocking and spid 55 blocked.

even tried (1204).

Can you please tell step by step how to see locking.

i am using standard version

Thanks

--
Message posted via http://www.sqlmonster.com|||Hi John

Little bit confusing that article if any sent to me please.

--
Message posted via http://www.sqlmonster.com|||reddy via SQLMonster.com (forum@.SQLMonster.com) writes:
> I Created deadlock and opened new page and typed the command
> DBCC TRACEON (1205,3605,-1).
> I didn't see any deadlock message except
> (End deadlock search 9232 a deadlock was not found)
> but in enterprise manager showing spid 54 blocking and spid 55 blocked.
> even tried (1204).

Then you are probably not having a deadlock, just blocking. A deadlock
is when two processes block each other in a way so that none of them
can proceed without one of them being rolled back.

To produce a deadlock do this:

CREATE TABLE x(a int NOT NULL PRIMARY KEY,
b int NOT NULL)
go
INSERT x (a, b) VALUES (1, 1)

And then run this from two windows in Query Analyzer:

BEGIN TRANSACTION
SELECT * FROM x WITH (HOLDLOCK)
WAITFOR DELAY '00:00:05'
UPDATE x SET b = 12
ROLLBACK TRANSACTION

One of these process will become a deadlock victim.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

Confusing in what way?

John|||As Erland mentioned, it seems you are experiencing blocking rather than
deadlocks. The deadlock trace flags are won't help in resolving a blocking
problem.

Long-term blocking is a symptom of long-running queries or transactions.
Blocking may be caused by poor application design or an indication that
tuning is needed. The key is to keep transactions and queries as short as
possible so that blocking locks are held only for short periods. It's a
good practice that one never waits on user response in an open transaction.

You can identify the resource blocking spid 55 with EXEC sp_lock 55. This
will show a status of WAIT for the resource in question. You can find the
name of an object by specifying the reported ObjId in the query SELECT
OBJECT_NAME(<ObjId>). Use DBCC INPUTBUFFER or fn_get_sql to determine the
SQL statements involved in the blocking.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"reddy via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:8e97e015504a44bf96d11d443d671bea@.SQLMonster.c om...
> Hi Dan
> I Created deadlock and opened new page and typed the command
> DBCC TRACEON (1205,3605,-1).
> I didn't see any deadlock message except
> (End deadlock search 9232 a deadlock was not found)
> but in enterprise manager showing spid 54 blocking and spid 55 blocked.
> even tried (1204).
> Can you please tell step by step how to see locking.
> i am using standard vresion
> Thanks
> --
> Message posted via http://www.sqlmonster.com|||Thanks a lot Dan.That helps me.

--
Message posted via http://www.sqlmonster.com|||Thanks a lot Erland.That helps me.

--
Message posted via http://www.sqlmonster.com|||Hi John

Nevermind i got my problem fixed
Thanks a lot.

--
Message posted via http://www.sqlmonster.com|||Hi

I am glad to hear that.

You may want to try out the deadlock examples in "Inside SQL Server
2000" in conjunction with the blocker script.

John

Sunday, March 11, 2012

Deadlock Condition

We are working on VB 6.0 as front-end tool and sqlserver 7.0 as back-end.
Currently we have started experiencing deadlock condition mostly when we are firing the update statements.
We haev tried closing all recordsets after using them and setting them to 'Nothing', but hasn't helped.
The number of users has nothing to do with this problem experienced.
As sometimes even with around 65-70 users we don't have this and sometimes even 1-2 people working on the network experience this.Hi Geeta,
It doesnt matter whether 50 users use the system simultaneously or no ... A deadlock can even occur when there are 2 users in the system. It depends on what tables of the database are being used and for what purpose.It is very likely that there are long running queries which are holding locks on the table while another user is either trying to query or update the same table.
There can be many reasons as to why a query/update suddenly starts running slowly all of a sudden... the simples reasons can be that the table size has grown a lot larger than what it used to be or there can be external factors like CPU being used by another process which keeps the SQL server process to starve...
You will have to be very specific as to when u observe the deadlocks ...esp because u are saying that they dont occur all the time.
It will be really nice if you can provide more information
Cheers
Sachin|||Oohhh, your problem is such general, that only general statements can be made.

Are you using DAO of ADO of ADO.NET, or are using Java or Borland technology?

First of all, I'm not sure whether you have a deadlock situation at all. In a deadlock situation, two transactions started, and one will be forced to roll-back. I guess, you have simply a locking problem, which occurs when you are updating a record, and a second process wants to read it.

Second thought: such a locking problem can een happen within 1 program, running by one user! I had that problem with two concurrent threads. So, the number of concurrent users isn't really an issue, if you have designed your application properly.

I don't have my old sources right here, but i remember that I had to set a kind of WaitForTransaction timeout, which was by default 0.|||Hi,

I am using ADO technology.
Please tell me more about what needs to be added into
the code so that I can get rid of this problem.

Thanks.

Geeta|||BOL:

Minimizing Deadlocks

Although deadlocks cannot be avoided completely, the number of deadlocks can be minimized. Minimizing deadlocks can increase transaction throughput and reduce system overhead because fewer transactions are:

Rolled back, undoing all the work performed by the transaction.
Resubmitted by applications because they were rolled back when deadlocked.

To help minimize deadlocks:

Access objects in the same order.
Avoid user interaction in transactions.
Keep transactions short and in one batch.
Use a low isolation level.
Use bound connections.

Thursday, March 8, 2012

Dead Lock

0x49C09530) Value:0x60540ac0 Cost

0/BC)
2006-04-12 10:57:05.31 spid4
2006-04-12 10:57:05.31 spid4 Node:2
2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:1 (b3016336158a)
CleanCnt:1 Mode: X Flags: 0x0
2006-04-12 10:57:05.31 spid4 Grant List 2::
2006-04-12 10:57:05.31 spid4 Owner:0x60541880 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:137 ECID:0
2006-04-12 10:57:05.31 spid4 SPID: 137 ECID: 0 Statement Type: UPDATE
Line #: 434
2006-04-12 10:57:05.31 spid4 Input Buf: RPC Event: sp_prepexec;1
2006-04-12 10:57:05.31 spid4 Requested By:
2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:528 ECID:0 Ec

0x7E0EB508) Value:0x675dfc20 Cost

0/0)
2006-04-12 10:57:05.31 spid4 Victim Resource Owner:
2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:528 ECID:0 Ec

0x7E0EB508) Value:0x675dfc20 Cost

0/0)
SPID 137 is updating the row which SPID 528 truying to access (This SP is
only selecting the rows no update) I specified the UPDLOCK lock hint in
update statment with no improvement.
Any help is appreciated.
--
Farhan"Farhan Soomro" <FarhanSoomro@.discussions.microsoft.com> wrote in message
news:E43DE237-5437-4BE0-AA3D-80B14FD67B9C@.microsoft.com...
> We are using SQLserver 2000 SP3 Standard Version. We are having dead lock
> and
> following is log about deadlock
> 2006-04-12 10:57:05.31 spid4 Wait-for graph
> 2006-04-12 10:57:05.31 spid4
> 2006-04-12 10:57:05.31 spid4 Node:1
> 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:53 (480260485b26)
> CleanCnt:1 Mode: S Flags: 0x0
> 2006-04-12 10:57:05.31 spid4 Grant List 1::
> 2006-04-12 10:57:05.31 spid4 Owner:0x66a25520 Mode: S
> Flg:0x0
> Ref:0 Life:00000001 SPID:528 ECID:0
> 2006-04-12 10:57:05.31 spid4 SPID: 528 ECID: 0 Statement Type:
> SELECT
> Line #: 38
> 2006-04-12 10:57:05.31 spid4 Input Buf: Language Event: Exec
> dbo.VB_ConflictsAppointmentsByDate
> @.StartDate='04/12/2006',@.EndDate='04/12/2006',@.Discipline='',
> @.Location=,@.ResourceID='000150',@.S_Start
=156,@.S_End=168
> 2006-04-12 10:57:05.31 spid4 Requested By:
> 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: X
> SPID:137 ECID:0 Ec

0x49C09530) Value:0x60540ac0 Cost

0/BC)
> 2006-04-12 10:57:05.31 spid4
> 2006-04-12 10:57:05.31 spid4 Node:2
> 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:1 (b3016336158a)
> CleanCnt:1 Mode: X Flags: 0x0
> 2006-04-12 10:57:05.31 spid4 Grant List 2::
> 2006-04-12 10:57:05.31 spid4 Owner:0x60541880 Mode: X
> Flg:0x0
> Ref:0 Life:02000000 SPID:137 ECID:0
> 2006-04-12 10:57:05.31 spid4 SPID: 137 ECID: 0 Statement Type:
> UPDATE
> Line #: 434
> 2006-04-12 10:57:05.31 spid4 Input Buf: RPC Event: sp_prepexec;1
> 2006-04-12 10:57:05.31 spid4 Requested By:
> 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:528 ECID:0 Ec

0x7E0EB508) Value:0x675dfc20 Cost

0/0)
> 2006-04-12 10:57:05.31 spid4 Victim Resource Owner:
> 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:528 ECID:0 Ec

0x7E0EB508) Value:0x675dfc20 Cost

0/0)
> SPID 137 is updating the row which SPID 528 truying to access (This SP is
> only selecting the rows no update) I specified the UPDLOCK lock hint in
> update statment with no improvement.
> Any help is appreciated.
> --
It's hard to suggest a change that resolves teh deadlock with minimal
degradation of concurrency or correctness without a complete picture of the
database structures, transactions and concurreny requirements of the
application.
However as a general matter, if you escalate the locks taken by one of the
transactions to a table lock, the deadlock should will go away. Again this
may cause an unacceptable degradation in the concurrency of the applciation.
Another aproach to simply optimize the SELECT. If it can read less, lock
less, or use a different index, etc you might resolve the deadlock and
improve appliction performance to boot.
David|||Thanks David.
I was thinking on same line, update sp is very complex and hard to adjust
versus select.
Appreciated.
--
Farhan
"David Browne" wrote:

> "Farhan Soomro" <FarhanSoomro@.discussions.microsoft.com> wrote in message
> news:E43DE237-5437-4BE0-AA3D-80B14FD67B9C@.microsoft.com...
> It's hard to suggest a change that resolves teh deadlock with minimal
> degradation of concurrency or correctness without a complete picture of th
e
> database structures, transactions and concurreny requirements of the
> application.
> However as a general matter, if you escalate the locks taken by one of the
> transactions to a table lock, the deadlock should will go away. Again th
is
> may cause an unacceptable degradation in the concurrency of the applciatio
n.
> Another aproach to simply optimize the SELECT. If it can read less, lock
> less, or use a different index, etc you might resolve the deadlock and
> improve appliction performance to boot.
> David
>
>

Dead Lock

We are using SQLserver 2000 SP3 Standard Version. We are having dead lock and
following is log about deadlock
2006-04-12 10:57:05.31 spid4 Wait-for graph
2006-04-12 10:57:05.31 spid4
2006-04-12 10:57:05.31 spid4 Node:1
2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:53 (480260485b26)
CleanCnt:1 Mode: S Flags: 0x0
2006-04-12 10:57:05.31 spid4 Grant List 1::
2006-04-12 10:57:05.31 spid4 Owner:0x66a25520 Mode: S Flg:0x0
Ref:0 Life:00000001 SPID:528 ECID:0
2006-04-12 10:57:05.31 spid4 SPID: 528 ECID: 0 Statement Type: SELECT
Line #: 38
2006-04-12 10:57:05.31 spid4 Input Buf: Language Event: Exec
dbo.VB_ConflictsAppointmentsByDate
@.StartDate='04/12/2006',@.EndDate='04/12/2006',@.Discipline='',
@.Location=,@.ResourceID='000150',@.S_Start=156,@.S_End=168
2006-04-12 10:57:05.31 spid4 Requested By:
2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:137 ECID:0 Ec:(0x49C09530) Value:0x60540ac0 Cost:(0/BC)
2006-04-12 10:57:05.31 spid4
2006-04-12 10:57:05.31 spid4 Node:2
2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:1 (b3016336158a)
CleanCnt:1 Mode: X Flags: 0x0
2006-04-12 10:57:05.31 spid4 Grant List 2::
2006-04-12 10:57:05.31 spid4 Owner:0x60541880 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:137 ECID:0
2006-04-12 10:57:05.31 spid4 SPID: 137 ECID: 0 Statement Type: UPDATE
Line #: 434
2006-04-12 10:57:05.31 spid4 Input Buf: RPC Event: sp_prepexec;1
2006-04-12 10:57:05.31 spid4 Requested By:
2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
2006-04-12 10:57:05.31 spid4 Victim Resource Owner:
2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
SPID 137 is updating the row which SPID 528 truying to access (This SP is
only selecting the rows no update) I specified the UPDLOCK lock hint in
update statment with no improvement.
Any help is appreciated.
--
Farhan"Farhan Soomro" <FarhanSoomro@.discussions.microsoft.com> wrote in message
news:E43DE237-5437-4BE0-AA3D-80B14FD67B9C@.microsoft.com...
> We are using SQLserver 2000 SP3 Standard Version. We are having dead lock
> and
> following is log about deadlock
> 2006-04-12 10:57:05.31 spid4 Wait-for graph
> 2006-04-12 10:57:05.31 spid4
> 2006-04-12 10:57:05.31 spid4 Node:1
> 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:53 (480260485b26)
> CleanCnt:1 Mode: S Flags: 0x0
> 2006-04-12 10:57:05.31 spid4 Grant List 1::
> 2006-04-12 10:57:05.31 spid4 Owner:0x66a25520 Mode: S
> Flg:0x0
> Ref:0 Life:00000001 SPID:528 ECID:0
> 2006-04-12 10:57:05.31 spid4 SPID: 528 ECID: 0 Statement Type:
> SELECT
> Line #: 38
> 2006-04-12 10:57:05.31 spid4 Input Buf: Language Event: Exec
> dbo.VB_ConflictsAppointmentsByDate
> @.StartDate='04/12/2006',@.EndDate='04/12/2006',@.Discipline='',
> @.Location=,@.ResourceID='000150',@.S_Start=156,@.S_End=168
> 2006-04-12 10:57:05.31 spid4 Requested By:
> 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: X
> SPID:137 ECID:0 Ec:(0x49C09530) Value:0x60540ac0 Cost:(0/BC)
> 2006-04-12 10:57:05.31 spid4
> 2006-04-12 10:57:05.31 spid4 Node:2
> 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:1 (b3016336158a)
> CleanCnt:1 Mode: X Flags: 0x0
> 2006-04-12 10:57:05.31 spid4 Grant List 2::
> 2006-04-12 10:57:05.31 spid4 Owner:0x60541880 Mode: X
> Flg:0x0
> Ref:0 Life:02000000 SPID:137 ECID:0
> 2006-04-12 10:57:05.31 spid4 SPID: 137 ECID: 0 Statement Type:
> UPDATE
> Line #: 434
> 2006-04-12 10:57:05.31 spid4 Input Buf: RPC Event: sp_prepexec;1
> 2006-04-12 10:57:05.31 spid4 Requested By:
> 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
> 2006-04-12 10:57:05.31 spid4 Victim Resource Owner:
> 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
> SPID 137 is updating the row which SPID 528 truying to access (This SP is
> only selecting the rows no update) I specified the UPDLOCK lock hint in
> update statment with no improvement.
> Any help is appreciated.
> --
It's hard to suggest a change that resolves teh deadlock with minimal
degradation of concurrency or correctness without a complete picture of the
database structures, transactions and concurreny requirements of the
application.
However as a general matter, if you escalate the locks taken by one of the
transactions to a table lock, the deadlock should will go away. Again this
may cause an unacceptable degradation in the concurrency of the applciation.
Another aproach to simply optimize the SELECT. If it can read less, lock
less, or use a different index, etc you might resolve the deadlock and
improve appliction performance to boot.
David|||Thanks David.
I was thinking on same line, update sp is very complex and hard to adjust
versus select.
Appreciated.
--
Farhan
"David Browne" wrote:
> "Farhan Soomro" <FarhanSoomro@.discussions.microsoft.com> wrote in message
> news:E43DE237-5437-4BE0-AA3D-80B14FD67B9C@.microsoft.com...
> > We are using SQLserver 2000 SP3 Standard Version. We are having dead lock
> > and
> > following is log about deadlock
> >
> > 2006-04-12 10:57:05.31 spid4 Wait-for graph
> > 2006-04-12 10:57:05.31 spid4
> > 2006-04-12 10:57:05.31 spid4 Node:1
> > 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:53 (480260485b26)
> > CleanCnt:1 Mode: S Flags: 0x0
> > 2006-04-12 10:57:05.31 spid4 Grant List 1::
> > 2006-04-12 10:57:05.31 spid4 Owner:0x66a25520 Mode: S
> > Flg:0x0
> > Ref:0 Life:00000001 SPID:528 ECID:0
> > 2006-04-12 10:57:05.31 spid4 SPID: 528 ECID: 0 Statement Type:
> > SELECT
> > Line #: 38
> > 2006-04-12 10:57:05.31 spid4 Input Buf: Language Event: Exec
> > dbo.VB_ConflictsAppointmentsByDate
> > @.StartDate='04/12/2006',@.EndDate='04/12/2006',@.Discipline='',
> > @.Location=,@.ResourceID='000150',@.S_Start=156,@.S_End=168
> > 2006-04-12 10:57:05.31 spid4 Requested By:
> > 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: X
> > SPID:137 ECID:0 Ec:(0x49C09530) Value:0x60540ac0 Cost:(0/BC)
> > 2006-04-12 10:57:05.31 spid4
> > 2006-04-12 10:57:05.31 spid4 Node:2
> > 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:1 (b3016336158a)
> > CleanCnt:1 Mode: X Flags: 0x0
> > 2006-04-12 10:57:05.31 spid4 Grant List 2::
> > 2006-04-12 10:57:05.31 spid4 Owner:0x60541880 Mode: X
> > Flg:0x0
> > Ref:0 Life:02000000 SPID:137 ECID:0
> > 2006-04-12 10:57:05.31 spid4 SPID: 137 ECID: 0 Statement Type:
> > UPDATE
> > Line #: 434
> > 2006-04-12 10:57:05.31 spid4 Input Buf: RPC Event: sp_prepexec;1
> > 2006-04-12 10:57:05.31 spid4 Requested By:
> > 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
> > 2006-04-12 10:57:05.31 spid4 Victim Resource Owner:
> > 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
> >
> > SPID 137 is updating the row which SPID 528 truying to access (This SP is
> > only selecting the rows no update) I specified the UPDLOCK lock hint in
> > update statment with no improvement.
> > Any help is appreciated.
> > --
> It's hard to suggest a change that resolves teh deadlock with minimal
> degradation of concurrency or correctness without a complete picture of the
> database structures, transactions and concurreny requirements of the
> application.
> However as a general matter, if you escalate the locks taken by one of the
> transactions to a table lock, the deadlock should will go away. Again this
> may cause an unacceptable degradation in the concurrency of the applciation.
> Another aproach to simply optimize the SELECT. If it can read less, lock
> less, or use a different index, etc you might resolve the deadlock and
> improve appliction performance to boot.
> David
>
>

DDL via DAO: Possible?

We are converting our database from Jet to SQLServer Express. We have a general-purpose database extract/import/management utility, written in VC++, that uses DAO to access and manipulate the database. This utility has grown over the years to include a wide range of functionality, which it would be time-consuming to rewrite.

I've been been able to tweak it to successfully connect to SQLServer, select and update data.

I have NOT been able to find a way to issue DDL, such as ALTER TABLE, etc.

The steps that I am following are:

m_pCDRDatabase = new CDaoDatabase;

m_pCDRDatabase->Open ("",FALSE, FALSE,
"ODBC;"
"PROVIDER=MSDASQL;"
"DSN=DSN1;"
"Database=Current_DB;"
"Uid=user1;"
"Pwd=pwd1;"

m_pCDRQueryDef = new CDaoQueryDef(m_pCDRDatabase);
m_pCDRQueryDef->Create("","DROP TABLE [temp]");
m_pCDRQueryDef->Execute(dbSQLPassThrough);
(I have also tried the dbSeeChanges and dbExecDirect options)

The ->Execute call gives me "Cannot perform this operation.", with an error code of 0x800a0bd8.

I've tried searching MS support, and the web in general, without success.
Does anyone know if it is possible to what I am attempting, and if so, how.

Thanks a lot.

Joe

I don't believe it is using DAO and ODBC based on the Microsoft response to "Problem to connect Access 2003 to SQL Server 2005 Express" - you will have to resort to Visual Studio or the SQL Server Management Studio Express tool. This is conjecture on my part, but I believe that the security enhancements in 2005 are probably one of the major reasons that this won't work.

Friday, February 17, 2012

dbms_lob conversion

hi,
am interested in porting some of my oracle quries to sqlserver. Here is a sample oracle procedure:
create procedure sp1(ac_clob clob)
as
n number;
position number;
begin
position := 4;
n := dbms_lob.instr(ac_clob,'test', position); --returns the location of the substring
n: = dbms_lob.getlength(ac_clob); --returns the length of the clob object
end;
As the clob object comes as a parameter let me know if it can be converted in sql server. Also I some other functions to dbms_lob packages like: dbms_lob.copy, dbms_lob.write, dbms_lob.trim,Hi,

SQL Server does not have the exact same functionality as the DBMS_LOB package. For example, there is no direct I/O between files and blob variables, and blob variables are not allowed as local variables. You need to use BULK INSERT and/or chunk-mode reads and writes from TSQL. ADO as a Stream class that makes this much easier from a client app.

In SQL2K we do support some functions on text columns (like CLOBs in Oracle) from the TSQL level, including DATALENGTH(), PATINDEX(), and SUBSTRING().

See:
Managing ntext, text, and image Data
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_13_8orl.asp

In SQL 2005, we've introduced a new feature called "large-value types", which allow you to treat CLOBS just like normal string values. You can declare local variables, use them in expressions, pass them as parameters, return them from functions, cast to/from XML, and so on. They have become a first-class data type in the TSQL language. Their declarations look like

varchar(max), nvarchar(max), varbinary(max)

It's really a great SQL 2005 feature, and it makes life *much* easier when dealing with CLOBS and BLOBs from within TSQL.

Regards,
Clifford Dibble
Program Manager, SQL Server|||Thanks for your suggestions|||In addition to what Clifford says, you can take a look at the CHARINDEX, PATINDEX and SUBSTRING functions which give you the functionality you have in your sample code. These should work in both SQL 2000 and SQL 2005.

- Christian

Tuesday, February 14, 2012

DBLib in Yukon?

We use the Microsoft/Solomon ERP product and it uses
DBLib, a SQLServer 6.5 (maybe 16bit) DLL for its access
to SQLServer. DBLib is supported in SQL 7 and SQL2000 as
a legacy tool. Will DBLib will be supported in Yukon?
How can I find a definitive answer to this question?Since Yukon is still in Beta, definitive statements about its functionality
are somewhat limited and may be subject to change. However SQL2000 BOL says:
"Connections from existing applications written using DB-Library will still
be supported in the next version of SQL Server, but this support will also
be dropped in a future release."
http://msdn.microsoft.com/library/en-us/dblibc/dbc_pdc00b_589v.asp
The official info on Yukon is here:
http://www.microsoft.com/sql/yukon/productinfo/default.asp
--
David Portas
--
Please reply only to the newsgroup
--|||I feel your pain. We still use DbLib as well and we are worried.
I've heard that it will be provided as a separate download and a separate
setup.
Anyway it sucks to use such an old technology. We have issues with DbLib and
NAT and other issues we did not have any explanations for since MS does not
support it anymore.
"Price Brattin" <pbrattin@.buenosw.com> wrote in message
news:075e01c3ce1f$f5e06de0$a501280a@.phx.gbl...
> We use the Microsoft/Solomon ERP product and it uses
> DBLib, a SQLServer 6.5 (maybe 16bit) DLL for its access
> to SQLServer. DBLib is supported in SQL 7 and SQL2000 as
> a legacy tool. Will DBLib will be supported in Yukon?
> How can I find a definitive answer to this question?|||Price - I'm not familiar with the Solomon product, but are you telling me that
the latest version (whatever that may be), shipped with the "MS" Logo on it,
still uses db-lib!?!?
> We use the Microsoft/Solomon ERP product and it uses
> DBLib, a SQLServer 6.5 (maybe 16bit) DLL for its access
> to SQLServer. DBLib is supported in SQL 7 and SQL2000 as
> a legacy tool. Will DBLib will be supported in Yukon?
> How can I find a definitive answer to this question?
>
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||Daniel,
MS certainly aren't doing any more development on it, but they still "support"
it. Have you reported bugs to them via PSS and not received a work-around or
fix?
> Anyway it sucks to use such an old technology. We have issues with DbLib and
> NAT and other issues we did not have any explanations for since MS does not
> support it anymore.
> "Price Brattin" <pbrattin@.buenosw.com> wrote in message
> news:075e01c3ce1f$f5e06de0$a501280a@.phx.gbl...
> > We use the Microsoft/Solomon ERP product and it uses
> > DBLib, a SQLServer 6.5 (maybe 16bit) DLL for its access
> > to SQLServer. DBLib is supported in SQL 7 and SQL2000 as
> > a legacy tool. Will DBLib will be supported in Yukon?
> > How can I find a definitive answer to this question?
>
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||Are you sure they still support it?
I remember I saw some info on MS site that it is not supported anymore. We
have a client that uses one of our apps and he cannot use NAT. The client
had to use static IP addresses in order to use the App over internet and a
VPN connection.
I'll look into MS site again for more info.
Thanks!
"Neil Pike" <neilpike@.compuserve.com> wrote in message
news:VA.00006190.022d20cf@.compuserve.com...
> Daniel,
> MS certainly aren't doing any more development on it, but they still
"support"
> it. Have you reported bugs to them via PSS and not received a work-around
or
> fix?
> > Anyway it sucks to use such an old technology. We have issues with DbLib
and
> > NAT and other issues we did not have any explanations for since MS does
not
> > support it anymore.
> >
> > "Price Brattin" <pbrattin@.buenosw.com> wrote in message
> > news:075e01c3ce1f$f5e06de0$a501280a@.phx.gbl...
> > > We use the Microsoft/Solomon ERP product and it uses
> > > DBLib, a SQLServer 6.5 (maybe 16bit) DLL for its access
> > > to SQLServer. DBLib is supported in SQL 7 and SQL2000 as
> > > a legacy tool. Will DBLib will be supported in Yukon?
> > > How can I find a definitive answer to this question?
> >
> Neil Pike MVP/MCSE. Protech Computing Ltd
> Reply here - no email
> SQL FAQ (484 entries) see
> http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
> (faqxxx.zip in lib 7)
> or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> or www.sqlserverfaq.com
> or www.mssqlserver.com/faq
>|||Yep. Microsoft Business Solutions/Solomon is still based
on DBLib.
>--Original Message--
> Price - I'm not familiar with the Solomon product, but
are you telling me that
>the latest version (whatever that may be), shipped with
the "MS" Logo on it,
>still uses db-lib!?!?
>> We use the Microsoft/Solomon ERP product and it uses
>> DBLib, a SQLServer 6.5 (maybe 16bit) DLL for its
access
>> to SQLServer. DBLib is supported in SQL 7 and SQL2000
as
>> a legacy tool. Will DBLib will be supported in
Yukon?
>> How can I find a definitive answer to this question?
> Neil Pike MVP/MCSE. Protech Computing Ltd
> Reply here - no email
> SQL FAQ (484 entries) see
> http://forumsb.compuserve.com/gvforums/UK/default.asp?
SRV=MSDevApps
> (faqxxx.zip in lib 7)
> or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> or www.sqlserverfaq.com
> or www.mssqlserver.com/faq
>
>.
>|||Price Brattin (pbrattin@.buenosw.com) writes:
> We use the Microsoft/Solomon ERP product and it uses
> DBLib, a SQLServer 6.5 (maybe 16bit) DLL for its access
> to SQLServer. DBLib is supported in SQL 7 and SQL2000 as
> a legacy tool. Will DBLib will be supported in Yukon?
> How can I find a definitive answer to this question?
You can connect to SQL Server Yukon with DB-Library. However, no DLL
from DB-Library comes with the current beta, and I would not expect
this change for the RTM version. So you should take well care of your
NTWDBLIB.DLL.
I should also add that you cannot use all new features in SQL Server
Yukon with DB-Library. For instance, when I attempted to access a
column of the new xml datatype, I got an error message saying that
this was not supported. (You get this already in SQL7 for ntext columns,
by the way.)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Daniel,
As it's shipped with SQL 2000, and fixes to it have been produced in at least
one SQL 2000 SP, then I'd say they still do support it.
I can't see why db-lib would break under NAT as I don't recall seeing any IP
addresses inside it's data frames when I've looked at traces of it in the past
for debugging. But I've never knowingly run a db-lib app through a NAT
router/device myself.
If you can do a network trace of a working (non-NAT) connection and a
non-working (NAT) connection, I'll be happy to look at the traces for you.
> Are you sure they still support it?
> I remember I saw some info on MS site that it is not supported anymore. We
> have a client that uses one of our apps and he cannot use NAT. The client
> had to use static IP addresses in order to use the App over internet and a
> VPN connection.
> I'll look into MS site again for more info.
> Thanks!
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||Actually what happens may be due to the application architecture as well.
One instance on the app works fine through NAT but when the second instance
is launched from a machine behind the NAT then the first app looses its
connection.
The app opens the connection when it is launched and it keeps it open till
the end. Yes, I know this is really bad but the app is pretty old and big
and I cannot improve it (read I'm not allowed to!).
"Neil Pike" <neilpike@.compuserve.com> wrote in message
news:VA.00006192.0065f62e@.compuserve.com...
> Daniel,
> As it's shipped with SQL 2000, and fixes to it have been produced in at
least
> one SQL 2000 SP, then I'd say they still do support it.
> I can't see why db-lib would break under NAT as I don't recall seeing any
IP
> addresses inside it's data frames when I've looked at traces of it in the
past
> for debugging. But I've never knowingly run a db-lib app through a NAT
> router/device myself.
> If you can do a network trace of a working (non-NAT) connection and a
> non-working (NAT) connection, I'll be happy to look at the traces for you.
> > Are you sure they still support it?
> >
> > I remember I saw some info on MS site that it is not supported anymore.
We
> > have a client that uses one of our apps and he cannot use NAT. The
client
> > had to use static IP addresses in order to use the App over internet and
a
> > VPN connection.
> >
> > I'll look into MS site again for more info.
> >
> > Thanks!
> Neil Pike MVP/MCSE. Protech Computing Ltd
> Reply here - no email
> SQL FAQ (484 entries) see
> http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
> (faqxxx.zip in lib 7)
> or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> or www.sqlserverfaq.com
> or www.mssqlserver.com/faq
>|||Ahh. So is the NAT a HIDE-NAT, and it's the application "complaining" about
seeing two connections coming at it from the same IP address then?
That would make sense - db-lib won't care about that.
The workaround for that, if you're in control of the NAT, is to set-up a NAT
pool instead of a static hide-NAT. Put enough addresses in the pool to cater
for the number of concurrent connections you're going to get.
> Actually what happens may be due to the application architecture as well.
> One instance on the app works fine through NAT but when the second instance
> is launched from a machine behind the NAT then the first app looses its
> connection.
> The app opens the connection when it is launched and it keeps it open till
> the end. Yes, I know this is really bad but the app is pretty old and big
> and I cannot improve it (read I'm not allowed to!).
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq

DBLIB

Hello!
I have developed a software which uses DBLib to access SQLServer 2000.
I tried if the same software will work with 32 bit version of SQLServer
2005 - there was no problem.
But now I have a customer who has a 64 bit version of SQLServer 2005 and I
urgently need to find solution for using the same software with 64 bit
version of SQLServer 2005.
Do I need a 64 bit ntwdblib.dll?
Has Microsoft implemented a 64 bit version of DBLib?
Thank you!Hi
DBLib is in "Maintenance mode" since SQL Server 7.0. No new functionality is
supplied.
DBLib is not supported on 64 bit, either x64 or IA64.
It is very old technology. Any reason you did not use OLE DB to develop
against?
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:O%23fTxr3yGHA.3360@.TK2MSFTNGP03.phx.gbl...
> Hello!
> I have developed a software which uses DBLib to access SQLServer 2000.
> I tried if the same software will work with 32 bit version of SQLServer
> 2005 - there was no problem.
> But now I have a customer who has a 64 bit version of SQLServer 2005 and I
> urgently need to find solution for using the same software with 64 bit
> version of SQLServer 2005.
> Do I need a 64 bit ntwdblib.dll?
> Has Microsoft implemented a 64 bit version of DBLib?
> Thank you!
>|||Michael is right, DB-Lib is not supported on any 64-bit edition.
Here's what it says in Books Online in the topic "Deprecated Database Engine
Features in SQL Server 2005
"Although the SQL Server 2005 Database Engine still supports connections
from existing applications using the DB-Library and Embedded SQL APIs, it
does not include the files or documentation needed to do programming work on
applications that use these APIs. A future version of the SQL Server
Database Engine will drop support for connections from DB-Library or
Embedded SQL applications. Do not use DB-Library or Embedded SQL to develop
new applications. Remove any dependencies on either DB-Library or Embedded
SQL when modifying existing applications. Instead of these APIs, use the
SQLClient namespace or an API such as OLE DB or ODBC. SQL Server 2005 does
not include the DB-Library DLL required to run these applications. To run
DB-Library or Embedded SQL applications you must have available the
DB-Library DLL from SQL Server version 6.5, SQL Server 7.0, or SQL Server
2000."
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Michael Epprecht [MSFT]" <michael.epprecht@.online.microsoft.com> wrote
in
message news:%23EBDk%233yGHA.4104@.TK2MSFTNGP02.phx.gbl...
> Hi
> DBLib is in "Maintenance mode" since SQL Server 7.0. No new functionality
> is supplied.
> DBLib is not supported on 64 bit, either x64 or IA64.
> It is very old technology. Any reason you did not use OLE DB to develop
> against?
> Regards
> --
> Mike
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "ggeshev" <ggeshev@.tonegan.bg> wrote in message
> news:O%23fTxr3yGHA.3360@.TK2MSFTNGP03.phx.gbl...
>

DBF to be attached in sqlserver2000/2005

Hi, Everybody

Please tell me the detailed procedure for having a *.DBF file attached to sqlserver 2000 or 2005.

I just want this file which is of visualfoxpro - DBF to be in sqlserver...........

thanks and regards,

Vishwanath.

Hey,

I don't believe SQL can import new database structures. You may, through DTS/SSIS, be able to copy the structure/data over, and that would create a SQL-compliant structure, depending on what was in the tables. I thought foxpro was more of an object-oriented database, which is not what SQL is...

|||

Hi Vishu

A lot depends on what you mean by "attached." You can use a linked server to access Fox data in SQL Server. You will need the FoxPro and Visual FoxPro OLE DB data provider, downloadable from a link at http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx . If you have a DBC (Database Container) in the directory where the Fox tables are then point your connection string to it. Otherwise, use the "Free Table" option and just point the connection string to the directory where the DBFs are. When you select data from the linked server use code like Select MyField From MyLinkedServer...MyTableName without using ".dbf" on the end of the table name.

|||

Hi!

Visual FoxPro is both an ODBC/OLE DB compliant database and a fully object-oriented development tool. In addition to using DBFs (Fox tables) as the data store you can use an Access MDB or SQL Server. In either the case of SQL Server it would be used as a front end in the same way as VB or C#.

Tuesday, March 27, 2012

Deadlocks after upgrade to SQLServer 2000

Deadlocks after upgrade to SQLServer 2000

Deadlocks after upgrade to SQLServer 2000

deadlocks

Sunday, March 11, 2012

Deadlock Condition

Thursday, March 8, 2012

Dead Lock

Dead Lock

DDL via DAO: Possible?

Friday, February 17, 2012

dbms_lob conversion

Tuesday, February 14, 2012

DBLib in Yukon?

DBLIB

DBF to be attached in sqlserver2000/2005

deAttach database

Blog Archive

About Me