DB2 linked server issues

I have a DB2 linked server using DB2OLEDB from MS. the DB2 table does not have journaling enabled, so all of my data creation and update statements are failing when using OpenQuery. Is there a way to get my data mod statements to execute on DB2 without requiring journaling? I have tried using ODBC and setting the Commit Level to none, but it appears that SQL server overrides that setting when connecting through the Linked Server. Any ideas?

Have you tried using

a Control Panel ODBC System DSN entry as your data source? This old school

connection technique fixed my numerous db2 linked server problems. I suspect

there is a bug in the IBM drivers that goes away when you configure the

connection like this.

It should also fix

your settings override problem and give you access to even more settings to tinker

with!

Thanks,
Greg Van Mullem

|||

we have tried using an ODBC DSN as the source for the Linked Server, but it still has the same problems. Somehow SQL Server seems to force the "transaction log/journaling" upon the ODBC source as well.

|||If you are running the Developer or Enterprise Edition

of SQL Server you could try this feature pack download from Microsoft.

Unfortunately, it does not work with SQL Standard Edition so I have not

been able to try it.

Microsoft

OLEDB Provider for DB2

Thanks,
Greg Van Mullem|||Have you tried the new EXEC AT syntax with linked server in SQL Server 2005?

DB_E_ROWSNOTRELEASED ADO2.81 Recodset->Seek() after Recordset>Update()

I am getting error 0x80040E25 when I try to call seek after update on a Recordset opened as (adOpenStatic, adLockOptimistic, adCmdTableDirect)

9 - (13.250) - <2> - *** error in .\DbRecordset.cpp, line 908
10 - (13.250) - <2> - ADO_ERRORS FOR pRs = 200a420, seek, err=-2147217883(80040e25)
11 - (13.250) - <2> - ADO_ERROR: E R R O R 1 of 1.
12 - (13.250) - <2> - ADO_ERROR: DESCRIPTION: All HROWs must be released before new ones can be obtained. [,,,,,].
13 - (13.250) - <2> - ADO_ERROR: NUMBER: 80040E25
14 - (13.250) - <2> - ADO_ERROR: NATIVE_ERROR: 0
15 - (13.250) - <2> - ADO_ERROR: SOURCE: Microsoft SQL Server 2005 Mobile Edition OLE DB Provider

I registered SQL Mobile 3.0 dlls using regsvr32.exe so now I can connect to SQLCE3.0 databases on desktop using plain ADO with such connection string _T("Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0; Data Source=") + name of the file

I have not asked this question before as it did not make sense -> there was no official SQLCE3.0 support on desktop. Now, since SQL CE is promiced to be supported on desktop as SQL/E I decided to ask.

Hi Xentrax,

This forum is meant for SQL Server Everywhere questions too.

SQL Server Everywhere supports only OLEDB and ADO.NET.

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere, Microsoft Corporation

DB_E_ROWSNOTRELEASED ADO2.81 Recodset->Seek() after Recordset>Update()

I am getting error 0x80040E25 when I try to call seek after update on a Recordset opened as (adOpenStatic, adLockOptimistic, adCmdTableDirect)

9 - (13.250) - <2> - *** error in .\DbRecordset.cpp, line 908
10 - (13.250) - <2> - ADO_ERRORS FOR pRs = 200a420, seek, err=-2147217883(80040e25)
11 - (13.250) - <2> - ADO_ERROR: E R R O R 1 of 1.
12 - (13.250) - <2> - ADO_ERROR: DESCRIPTION: All HROWs must be released before new ones can be obtained. [,,,,,].
13 - (13.250) - <2> - ADO_ERROR: NUMBER: 80040E25
14 - (13.250) - <2> - ADO_ERROR: NATIVE_ERROR: 0
15 - (13.250) - <2> - ADO_ERROR: SOURCE: Microsoft SQL Server 2005 Mobile Edition OLE DB Provider

I registered SQL Mobile 3.0 dlls using regsvr32.exe so now I can connect to SQLCE3.0 databases on desktop using plain ADO with such connection string _T("Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0; Data Source=") + name of the file

I have not asked this question before as it did not make sense -> there was no official SQLCE3.0 support on desktop. Now, since SQL CE is promiced to be supported on desktop as SQL/E I decided to ask.

Hi Xentrax,

This forum is meant for SQL Server Everywhere questions too.

SQL Server Everywhere supports only OLEDB and ADO.NET.

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere, Microsoft Corporation

sql

DB Update fails in program, works in Management Studio?

I have an SQL statement that, when run through SQL Server management studio works fine. However, when I run it on my ASP page, it doesn't update the data! I have tried both as a stored procedure and as a simple commandText update statement.

All I do is simly update the value of a column based on another column – nothing particularly complex:

update customerset dateChanged =System.DateTime.Now.ToString("dd-MMM-yyyy"),CURRSTAT = 'Active',custType = case WHEN cust_Changing_To IS NOT NULL THEN cust_Changing_To ELSE custType END,cust_Changing_To = NULLFROM customers_vWHERE CURRSTAT = 'Changing'

As you can see, nothing that complex. The line that is causing the problem is the case:

custType = case WHEN cust_Changing_To IS NOT NULL THEN cust_Changing_To ELSE custType END

all it does is if another nullable integer column is not null, sets it to the value of that column, else it retains its existing value.

Like I say, this works in Management studio, but I cannot get it to execute programatticaly from an asp page.

No exceptions are being thrown, it just doesn't update the data.

Any ideas?

Thanks

Try running SQL Profiler and see exactly what is being executed against the database.

DB UpDate Error

The clients are able to connect and pull data on existing accounts. However when users try to create a new version of an existing account or an actual new account, they get:

Db UpDate Error

ERROR 40002 01000: [Microsoft][ODBC SQL Server Driver][SQL Server]Command has been aborted.

Unable To Set Version Info. MODE: NEW

Class[APPOBJ.SerVersion]

Is this some type of version conflict? I've tried finding more information about the Error 40002 but I don't find the one specific to mine. I'm not an SQL expert but I would like to try and find out what the error message is trying to tell me. Any help would be greatly appreicated.

I thought I would add we are using SQL 6.5 and Access 97. Server is running NT 4.00.1381 and clients are running WinXP.sql

Db Trigger Question

Hello,

I have a db trigger (example below) that is supposed to update a column with the current date. However, I need to know which row is being updated by a user so that I would update ONLY this row. Is there a straightforward way to figure out which row is updated by a user? My table includes a column called rowid which is a primary key.

Thanks for any help!

CREATE TRIGGER dbo.[DatetimeUpdate]

ON dbo.DepartmentMapping] AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

UPDATE dbo.DepartmentMapping

SET DateUpdated = GetDate()

Donnie:

You need to take advangage of the INSERTED pseudo-table in your trigger. Look up CREATE TRIGGER in books online and examine their examples. Your update ought to change to something like:

UPDATE dbo.DepartmentMapping
SET DateUpdated = GetDate()
FROM inserted i
INNER JOIN dbo.DepartmentMapping a
ON a.{departmentMappingKey} = i.{departmentMappingKey}

|||

When code inside a trigger is being executed, the code has access to two 'Virtual Tables' named Inserted and Deleted.

In the case of an UPDATE statement, the Deleted table contains the rows as they would have been before any changes and the Inserted table contains the rows as they are after the changes. The virtual tables have the same columns as the table on which the trigger is defined.

You could amend your code to that shown below.

Chris

UPDATE dm

SET DateUpdated = GetDate()

FROM dbo.DepartmentMapping dm

INNER JOIN deleted d ON d.DepartmentMappingID = dm.DepartmentMappingID

--or equally

--INNER JOIN inserted i ON i.DepartmentMappingID = dm.DepartmentMappingID

|||Great. Thank You!|||Of course you wouldn't want to use deleted in this case, since you would be trying to update a deleted row :)sql

DB Mirroring VS. Clustered Failover Questions

We are planning to update our two node, active/passive clustered system to SQL 2005 from SQL2K. Would we be better off to use database mirroring rather than clustering with failover? I believe mirroring takes 3 systems rather than two (one for each mirror and one for monitoring), but can the monitor be a rather low powered PC?

Our current cluster uses two 4-processor Dells and an external PowerVault RAID array with fiber channel connections. Each server has two NICs, one for general network and one for heartbeat and a fiber channel card to connect to the external PowerVault.

Here are my questions:

1 - If I understand it correctly, mirroring uses just separate servers without the need for the rather expensive and complicated shared fiber channel array that must go between them. Do I still need a shared array or can I just use internal RAID arrays in each box?

2 - Are there any advantages to keeping our current system? Our current system has worked well, but the failover takes a couple of minutes. Is mirroring faster?

3 - Is there still a virtual IP address and network name that everyone connects to?

4 - What are the drawbacks?

5 - Can I run non-mirrored DBs on the same servers or are they really locked together tightly?

6 - How much different in configuration can the two boxes be? With the cluster, I really need duplicates in every way.

7 - Do I need a separate license for the SQL Server on each box in the mirror or is one enough, since they work in lock step?

Thanks for any of your experiences on this.

Biggest difference is probably the level at which each provides its redundancy...mirroring provides protection/avail at the database level, but clustering provides protection at the server instance level. Another big difference is that in mirroring, the principal and mirror servers are separate SQL Server instances with distinct names, but a SQL instance on a cluster gets a single virtual server name and IP that remains the same no matter what node of the cluster is hosting the instance.

If you need protection at the server level (i.e. multiple databases are required for application functionality, etc.), clustering is probably a more appropriate choice...but if you only need to provide availability for one database at a time, mirroring has a number of advantages.

Unlike clustering, mirroring does't require proprietary hardware and does't have a potential failure point with shared storage...mirroring also can probably bring the standby database into service much faster than any other high availability technology, and works well with new capabilities in ADO.NET and SQL Native Access Client for client-side failover.

Note that you can't use database mirroring within a single cluster, but you can definately consider using database mirroring as a method for creating a hot standby for a cluster instance database. If you do, be aware that because a cluster failover is probably longer than the timeout value on mirroring, a High Availability mode mirroring session will react to a cluster failover as a failure of the principal server. It would then put the cluster node into a mirroring state.

HTH

|||

Thank you, that is helpful. I am wondering, if the mirroring is at the DB level, how does the application (in this case a three tier app with a thick-client VB app on the desktop communicating to a mid-tier portion on another server) know about the failure and manage to get connected to the mirror? Is it transparent to the application, as is the case with a cluster, or must some code be written to test some indication of primary vs. mirror state?

It sounds like there is no provision for a virtual-IP as we would get with clustering. Is that true?

Also, do we need to purchase Enterprise Edition of SQL 2005 for a two node, active/passive cluster arrangement, or will Standard Edition handle that type of setup?

|||

Take a look at this article, it should answer all those questions and more:

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

DB Maintenance Plans failing - How to fix?

Created 'Database Maintenance Plans' to check integrity
of database, to update statistics for query analyzer
queries, to update data pages and index pages.
The Database Maintenance Plans jobs are failing with
error: 4062, cannot open user default database '<ID>'.
Using master instead. The weird thing about it is that
the error refers to an old job/master plan name.
It looks like the database for Database Maintenance Plans
is corrupted - using data from old plans for new plans.
What needs to be done to fix this problem?Using SQL Server 7 and Windows 2000 server.
>--Original Message--
>Created 'Database Maintenance Plans' to check integrity
>of database, to update statistics for query analyzer
>queries, to update data pages and index pages.
>The Database Maintenance Plans jobs are failing with
>error: 4062, cannot open user default database '<ID>'.
>Using master instead. The weird thing about it is that
>the error refers to an old job/master plan name.
>It looks like the database for Database Maintenance
Plans
>is corrupted - using data from old plans for new plans.
>What needs to be done to fix this problem?
>.
>|||The output text file was refering to a previous job. No
other references to previous jobs was seen.
>--Original Message--
>Created 'Database Maintenance Plans' to check integrity
>of database, to update statistics for query analyzer
>queries, to update data pages and index pages.
>The Database Maintenance Plans jobs are failing with
>error: 4062, cannot open user default database '<ID>'.
>Using master instead. The weird thing about it is that
>the error refers to an old job/master plan name.
>It looks like the database for Database Maintenance
Plans
>is corrupted - using data from old plans for new plans.
>What needs to be done to fix this problem?
>.
>|||Solved the issue. The MSSQLSERVER service login default
database had been changed to point to a database other
then 'master'. Apparently this is a prerequisite for
jobs to run. Once the MSSQLSERVER service login was
pointed at the 'master' database jobs ran.

DB LOCK

Comming across the [BULK-OP-DB],[BULK-OP-LOG] , locks in the database and there is no database backup job running. Auto stats update is set to off. Auto shrink is also set to off.
Can someone shed more light on this ? and its implications on performance ?
Thanks !!
[BULK-OP-LOG]
Lock acquired on a transaction log to synchronize certain bulk (non-logged)
operations.
* If the BULK-OP-LOG lock is LCK_M_X (exclusive), cause may be
database/file/log backup or incremental db backup
* If the BULK-OP-LOG lock is LCK_M_S (shared), cause may be tran log
truncation, nonlogged bulk insert/bcp/select into, or text modification
operations
* If the BULK-OP-LOG lock is LCK_M_U (update), cause may be add file, drop
file, shrink file/db, or create filegroup
* If the BULK-OP-LOG lock is LCK_M_NL (NULL), cause may be dbcc
checkalloc/dbcc checkdb, create index, grow file, or modify file
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

DB Lock

Comming across the [BULK-OP-DB],[BULK-OP-LOG] , locks in the database and there is no database backup job running. Auto stats update is set to off. Auto shrink is also set to off.
Can someone shed more light on this ? and its implications on performance ?
DB:db_id[BULK-OP-DB], which identifies the database lock taken by the backup database.
DB:db_id[BULK-OP-LOG], which identifies the lock taken by the backup log for that particular database.
Thanks !!
It would depend on the kind of lock.
If the BULK-OP-DB lock is
1. LCK_M_X (exclusive):- cause may be database or file backup, incremental
db backup, or dbcc checkalloc/dbcc checkdb
2. LCK_M_S (shared):- cause may be index creation, text modification
operations, or nonlogged bulk insert/bcp/select into
3. LCK_M_U (update):- cause may be filegroup creation, shrink file/shrink
database, drop file, or add file
4. LCK_M_NL (NULL):- cause may be modify file, auto grow file, or tran log
backup/truncation
Hope that helps!
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

DB LOCK

Comming across the [BULK-OP-DB],[BULK-OP-LOG] , locks in the database and there is no database backup job running. Auto stats update is set to off. Auto shrink is also set to off.
Can someone shed more light on this ? and its implications on performance '
Thanks !![BULK-OP-LOG]
Lock acquired on a transaction log to synchronize certain bulk (non-logged)
operations.
* If the BULK-OP-LOG lock is LCK_M_X (exclusive), cause may be
database/file/log backup or incremental db backup
* If the BULK-OP-LOG lock is LCK_M_S (shared), cause may be tran log
truncation, nonlogged bulk insert/bcp/select into, or text modification
operations
* If the BULK-OP-LOG lock is LCK_M_U (update), cause may be add file, drop
file, shrink file/db, or create filegroup
* If the BULK-OP-LOG lock is LCK_M_NL (NULL), cause may be dbcc
checkalloc/dbcc checkdb, create index, grow file, or modify file
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

DB Lock

Comming across the [BULK-OP-DB],[BULK-OP-LOG] , locks in the database and there is no database backup job running. Auto stats update is set to off. Auto shrink is also set to off.
Can someone shed more light on this ? and its implications on performance '
DB:db_id[BULK-OP-DB], which identifies the database lock taken by the backup database.
DB:db_id[BULK-OP-LOG], which identifies the lock taken by the backup log for that particular database.
Thanks !!It would depend on the kind of lock.
If the BULK-OP-DB lock is
1. LCK_M_X (exclusive):- cause may be database or file backup, incremental
db backup, or dbcc checkalloc/dbcc checkdb
2. LCK_M_S (shared):- cause may be index creation, text modification
operations, or nonlogged bulk insert/bcp/select into
3. LCK_M_U (update):- cause may be filegroup creation, shrink file/shrink
database, drop file, or add file
4. LCK_M_NL (NULL):- cause may be modify file, auto grow file, or tran log
backup/truncation
Hope that helps!
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

DB Lock

Comming across the [BULK-OP-DB],[BULK-OP-LOG] , locks in the databas
e and there is no database backup job running. Auto stats update is set to o
ff. Auto shrink is also set to off.
Can someone shed more light on this ? and its implications on performance '
DB:db_id[BULK-OP-DB], which identifies the database lock taken by the ba
ckup database.
DB:db_id[BULK-OP-LOG], which identifies the lock taken by the backup log
for that particular database.
Thanks !!It would depend on the kind of lock.
If the BULK-OP-DB lock is
1. LCK_M_X (exclusive):- cause may be database or file backup, incremental
db backup, or dbcc checkalloc/dbcc checkdb
2. LCK_M_S (shared):- cause may be index creation, text modification
operations, or nonlogged bulk insert/bcp/select into
3. LCK_M_U (update):- cause may be filegroup creation, shrink file/shrink
database, drop file, or add file
4. LCK_M_NL (NULL):- cause may be modify file, auto grow file, or tran log
backup/truncation
Hope that helps!
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

DB LOCK

Comming across the [BULK-OP-DB],[BULK-OP-LOG] , locks in the databas
e and there is no database backup job running. Auto stats update is set to o
ff. Auto shrink is also set to off.
Can someone shed more light on this ? and its implications on performance '
Thanks !![BULK-OP-LOG]
Lock acquired on a transaction log to synchronize certain bulk (non-logged)
operations.
* If the BULK-OP-LOG lock is LCK_M_X (exclusive), cause may be
database/file/log backup or incremental db backup
* If the BULK-OP-LOG lock is LCK_M_S (shared), cause may be tran log
truncation, nonlogged bulk insert/bcp/select into, or text modification
operations
* If the BULK-OP-LOG lock is LCK_M_U (update), cause may be add file, drop
file, shrink file/db, or create filegroup
* If the BULK-OP-LOG lock is LCK_M_NL (NULL), cause may be dbcc
checkalloc/dbcc checkdb, create index, grow file, or modify file
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.