Insert Trigger on Linked Server during a FULL DB Backup

I have two servers, Server A and B. Server B is linked to Server A as a linked Server and there is one table TableA on ServerA and ServerB.
TableA receives contineous Inserts and there is an Insert trigger written on TableA on ServerA to insert the same data to ServerB using the INSERTED table. This equation works fine for most of the day, but whenever I run a Full Database backup on ServerB, the whole Trigger Insert process slows. I wonder why? Any idea, any suggestion?During backup server is kind of busy ;) and if there is only one processor and not fast I/O device ...

Insert to a Linked Server possible via Service Broker?

I have configured a non-SQL linked server (via an OLE DB provider) and I wish to insert data into it via Service Broker but I am getting the following error in the SQL Server log:

The activated proc [dbo].[sp_ mytableServiceProgram] running on queue TestDB.dbo.mytableQueue output the following:'Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.'

As you see below, my strored proc. is not issuing any 'save trans' statements, so why is it not allowing me to wrap my code in a transaction? How else can I use a transaction (in order to not lose anything from the queue) and yet still be able to insert to the linked server?

CREATE PROC sp_mytableServiceProgram

AS

SET NOCOUNT ON;

DECLARE

@.XML XML,

@.MessageBody VARBINARY(MAX),

@.MessageTypeName nvarchar(256),

@.Dialog UNIQUEIDENTIFIER;

-- This procedure continues to process messages in the queue until the

-- queue is empty.

WHILE (1 = 1)

BEGIN

BEGIN TRANSACTION;

--BEGIN DISTRIBUTED TRANSACTION; --Tried this but didn't help.

-- Receive the next available message

WAITFOR (

RECEIVE TOP(1) -- just handle one message at a time

@.MessageTypeName = message_type_name,

@.MessageBody = message_body,

@.Dialog = conversation_handle

FROM mytableQueue

), TIMEOUT 2000 ;

-- If RECEIVE did not return a message, roll back the transaction

-- and break out of the while loop, exiting the procedure.

IF (@.@.ROWCOUNT = 0)

BEGIN

ROLLBACK TRANSACTION;

BREAK;

END ;

SET @.XML = CAST(@.MessageBody AS XML);

INSERT INTO LINKEDSERVER.dbname.user.mytable

SELECT tbl.rows.value('@.doc_no', 'INT') AS doc_no,

tbl.rows.value('@.queryid', 'NVARCHAR(50)') AS queryid,

tbl.rows.value('@.ar_num', 'NVARCHAR(50)') AS ar_num,

tbl.rows.value('@.status', 'NVARCHAR(20)') AS status,

tbl.rows.value('@.creationtime', 'DATETIME') AS creationtime,

tbl.rows.value('@.note', 'NVARCHAR(250)') AS note,

tbl.rows.value('@.posted', 'NCHAR(1)') AS posted,

tbl.rows.value('@.kms', 'INT') AS kms,

tbl.rows.value('@.schresid', 'NVARCHAR(50)') AS schresid,

tbl.rows.value('@.resolution_code', 'NCHAR(8)') AS resolution_code,

tbl.rows.value('@.page_count', 'INT') AS page_count,

tbl.rows.value('@.new_serial_number', 'NVARCHAR(20)') AS new_serial_number,

tbl.rows.value('@.taskresolution', 'NVARCHAR(250)') AS taskresolution

FROM @.XML.nodes('/inserted') tbl(rows);

-- If the INSERT did not insert any rows, rollback.

IF @.@.ROWCOUNT = 0

BEGIN

ROLLBACK TRANSACTION;

BREAK;

END

COMMIT TRANSACTION;

END

GO

Is the procedure runnning fine if is invoked from a user connection (e.g. using exec sp_mytableServiceProgram from a Management Studio query) but it fails when called from activation context?

Also, what OLDDB provider are you using for the linked server?

BTW, your procedure should handle the [http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog] and [http://schemas.microsoft.com/SQL/ServiceBroker/Error] message types.

HTH,
~ Remus

|||

No, I see the same error when run as myself (but with more detail):

Msg 3933, Level 16, State 1, Procedure sp_mytableServiceProgram, Line 38

Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.

Line #38 is the insert to the linked server and the OLEDB provider is set to 'Ifxoledbc' which is for an IBM Informix database server.

And yes, you are correct, I do handle the other two message types, (just removed for posting simplicity).

Is the WAITFOR...RECEIVE statement perhaps doing an implicit SAVE TRANS for each and every message that it receives?

Regards,

Ron

|||

Is the WAITFOR that creates an internal savepoint. Remove the WAITFOR and it should work, using a plain RECEIVE. It does work for me, using an SQLNCLI linked server.

HTH,
~ Remus

|||

Right, I was able to see the same thing, removing WAITFOR allowed the insert to the linked server to go through.

But I wonder what are the repercussions to not using WAITFOR are?

Thanks,

Ron

|||

WAITFOR in activated procedures has the purpose of lingering the proedure around for a few seconds when he queue is empty, in hope another message comes anew. This avoids the situation when the rate of incoming messages is just slow enough to keep activating the procedure immedeatly after it just finished.

You should be fine, the benefits of WAITFOR show up only at one particular rate of incomming messages and the gains offered by it are not earth shatering in the first place anyway...

HTH,
~ Remus

|||Excellent, thank you.sql

insert temp with linked server

Hi,
I have a problem with insert temp table with linked server. I have a sql
2000 with sp4 and I have a dozen linked servers. All of them work except one
which is running on windows 2003. I didn't get any error message and the
transaction just open and never stop. I have to kill it manually.
I have checked the DTC on windows 2003, which is quite different. I am sure
that the settings are all right, including tricks like network service, etc.
I guess it might be some kind of bug but I just can't find anything about it
after searching Google and MS KB.
Is anyone running same problem or has any clue,
Thanks,
m
I think I've got more or less the same problem.
Since I've load 2000 SP4 the data return from a SP called to a Default
instance is not the same as in the case of a Named Instance. Instead it does
not return a error but return false results.
IOW :
Before SP4 --> No Problems
After SP4 -->
exec[missql01].SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* For any Default Instance : Return Correct Results */
exec [M24BLACKB01\BES_001].SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* For Any Named Instance : Return INCORRECT Results */
Same command on Local server M24BLACKB01\BES_001:
exec SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* Return CORRECT Results */
"dp" wrote:

> Hi,
> I have a problem with insert temp table with linked server. I have a sql
> 2000 with sp4 and I have a dozen linked servers. All of them work except one
> which is running on windows 2003. I didn't get any error message and the
> transaction just open and never stop. I have to kill it manually.
> I have checked the DTC on windows 2003, which is quite different. I am sure
> that the settings are all right, including tricks like network service, etc.
> I guess it might be some kind of bug but I just can't find anything about it
> after searching Google and MS KB.
> Is anyone running same problem or has any clue,
> Thanks,
>
> --
> m

insert temp with linked server

Hi,
I have a problem with insert temp table with linked server. I have a sql
2000 with sp4 and I have a dozen linked servers. All of them work except one
which is running on windows 2003. I didn't get any error message and the
transaction just open and never stop. I have to kill it manually.
I have checked the DTC on windows 2003, which is quite different. I am sure
that the settings are all right, including tricks like network service, etc.
I guess it might be some kind of bug but I just can't find anything about it
after searching Google and MS KB.
Is anyone running same problem or has any clue,
Thanks,
mI think I've got more or less the same problem.
Since I've load 2000 SP4 the data return from a SP called to a Default
instance is not the same as in the case of a Named Instance. Instead it does
not return a error but return false results.
IOW :
Before SP4 --> No Problems
After SP4 -->
exec[missql01].SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* For any Default Instance : Return Correct Results */
exec [M24BLACKB01\BES_001].SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* For Any Named Instance : Return INCORRECT Results */
Same command on Local server M24BLACKB01\BES_001:
exec SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* Return CORRECT Results */
"dp" wrote:

> Hi,
> I have a problem with insert temp table with linked server. I have a sql
> 2000 with sp4 and I have a dozen linked servers. All of them work except o
ne
> which is running on windows 2003. I didn't get any error message and the
> transaction just open and never stop. I have to kill it manually.
> I have checked the DTC on windows 2003, which is quite different. I am sur
e
> that the settings are all right, including tricks like network service, et
c.
> I guess it might be some kind of bug but I just can't find anything about
it
> after searching Google and MS KB.
> Is anyone running same problem or has any clue,
> Thanks,
>
> --
> m

Insert statement hangs (linked server)

Hi. I'm trying to do something like this, but sql server hangs:
insert into #local_temp_table
exec linked_server_name.database..stored_procedure @.parameter
But, when run only exec statement, it works fine:
exec linked_server_name.database..stored_procedure @.parameter
Any idea? Thanks in advance,
Rayerorr message is?
--
"Ray" wrote:

> Hi. I'm trying to do something like this, but sql server hangs:
> insert into #local_temp_table
> exec linked_server_name.database..stored_procedure @.parameter
> But, when run only exec statement, it works fine:
> exec linked_server_name.database..stored_procedure @.parameter
> Any idea? Thanks in advance,
> Ray
>|||no error message appears, sql server just hangs
left in execution about 15 minutes and no error message appeared, then
canceled the operation
any idea?
"Aleksandar Grbic" wrote:
> erorr message is?
> --
>
> "Ray" wrote:
>|||remote server connection - query timeout is 600 sec, check this value
--
"Ray" wrote:
> no error message appears, sql server just hangs
> left in execution about 15 minutes and no error message appeared, then
> canceled the operation
> any idea?
> "Aleksandar Grbic" wrote:
>|||Changed "remote query timeout" from 600 to 20 and still no error appears
(after 15 minutes of execution time).
Regards,
Ray
"Aleksandar Grbic" wrote:
> remote server connection - query timeout is 600 sec, check this value
> --
>
> "Ray" wrote:
>

Insert Statement Fails on Linked servers

Hi,
we have a local server with a database say A on it . we also have a
linked server which has a database B.
now we are trying to insert into a table in a using data from the
database B. both of the tables in both the database are the same in
structure .
now when i use a query like
insert into a.Table1
( No,
Name
)
select
no,
name
frrom
host_sever.B.dbo.table1
where <some condition >
the above query fails and the error says a nested distributed
transaction cannot be started
both the tables have a trigger attached to it .
we found that first inserting the data into a temp table and then
copying that data into the main table in local server in database A
works fine.
also i tested some scenario with no trigger and it works fine .
is this how it is when there are triggers attached and is there any way
we can succesfully run the query ableve without temp tables.
thanks
ravinderavravinder@.gmail.com wrote:
> Hi,
> we have a local server with a database say A on it . we also have a
> linked server which has a database B.
> now we are trying to insert into a table in a using data from the
> database B. both of the tables in both the database are the same in
> structure .
> now when i use a query like
> insert into a.Table1
> ( No,
> Name
> )
> select
> no,
> name
> frrom
> host_sever.B.dbo.table1
> where <some condition >
> the above query fails and the error says a nested distributed
> transaction cannot be started
> both the tables have a trigger attached to it .
> we found that first inserting the data into a temp table and then
> copying that data into the main table in local server in database A
> works fine.
> also i tested some scenario with no trigger and it works fine .
> is this how it is when there are triggers attached and is there any way
> we can succesfully run the query ableve without temp tables.
> thanks
> ravinder
>
Is the MSDTC service running on both machines? Is it enabled for
network access?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> avravinder@.gmail.com wrote:
> Is the MSDTC service running on both machines? Is it enabled for
> network access?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
i looked at the services and DTC is runnign . there is another one is
control panel which says MSDTC which i think you are referreing too.
the network protocol says TCP/Ip and the salection default MS DTC
server is blank and disabled .
should this also not have created a issue when dum[ing into the temp
table and cause the same issue.
thanks
ravinder|||avravinder@.gmail.com wrote:
> Tracy McKibben wrote:
> i looked at the services and DTC is runnign . there is another one is
> control panel which says MSDTC which i think you are referreing too.
> the network protocol says TCP/Ip and the salection default MS DTC
> server is blank and disabled .
> should this also not have created a issue when dum[ing into the temp
> table and cause the same issue.
> thanks
> ravinder
>
No, it wouldn't cause an issue that way. Everything that occurs within
a trigger is done inside a transaction, and for a transaction to cross a
linked server, DTC must be available.
I would suggest starting here:
http://www.sqlservercentral.com/col...realsqlguy.com

Insert Statement Fails on Linked servers

Hi,
we have a local server with a database say A on it . we also have a
linked server which has a database B.
now we are trying to insert into a table in a using data from the
database B. both of the tables in both the database are the same in
structure .
now when i use a query like
insert into a.Table1
( No,
Name
)
select
no,
name
frrom
host_sever.B.dbo.table1
where <some condition >
the above query fails and the error says a nested distributed
transaction cannot be started
both the tables have a trigger attached to it .
we found that first inserting the data into a temp table and then
copying that data into the main table in local server in database A
works fine.
also i tested some scenario with no trigger and it works fine .
is this how it is when there are triggers attached and is there any way
we can succesfully run the query ableve without temp tables.
thanks
ravinderavravinder@.gmail.com wrote:
> Hi,
> we have a local server with a database say A on it . we also have a
> linked server which has a database B.
> now we are trying to insert into a table in a using data from the
> database B. both of the tables in both the database are the same in
> structure .
> now when i use a query like
> insert into a.Table1
> ( No,
> Name
> )
> select
> no,
> name
> frrom
> host_sever.B.dbo.table1
> where <some condition >
> the above query fails and the error says a nested distributed
> transaction cannot be started
> both the tables have a trigger attached to it .
> we found that first inserting the data into a temp table and then
> copying that data into the main table in local server in database A
> works fine.
> also i tested some scenario with no trigger and it works fine .
> is this how it is when there are triggers attached and is there any way
> we can succesfully run the query ableve without temp tables.
> thanks
> ravinder
>
Is the MSDTC service running on both machines? Is it enabled for
network access?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> avravinder@.gmail.com wrote:
> > Hi,
> > we have a local server with a database say A on it . we also have a
> > linked server which has a database B.
> > now we are trying to insert into a table in a using data from the
> > database B. both of the tables in both the database are the same in
> > structure .
> > now when i use a query like
> > insert into a.Table1
> > ( No,
> > Name
> > )
> > select
> > no,
> > name
> > frrom
> > host_sever.B.dbo.table1
> > where <some condition >
> >
> > the above query fails and the error says a nested distributed
> > transaction cannot be started
> > both the tables have a trigger attached to it .
> > we found that first inserting the data into a temp table and then
> > copying that data into the main table in local server in database A
> > works fine.
> > also i tested some scenario with no trigger and it works fine .
> >
> > is this how it is when there are triggers attached and is there any way
> > we can succesfully run the query ableve without temp tables.
> >
> > thanks
> > ravinder
> >
> Is the MSDTC service running on both machines? Is it enabled for
> network access?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
i looked at the services and DTC is runnign . there is another one is
control panel which says MSDTC which i think you are referreing too.
the network protocol says TCP/Ip and the salection default MS DTC
server is blank and disabled .
should this also not have created a issue when dum[ing into the temp
table and cause the same issue.
thanks
ravinder|||avravinder@.gmail.com wrote:
> Tracy McKibben wrote:
>> avravinder@.gmail.com wrote:
>> Hi,
>> we have a local server with a database say A on it . we also have a
>> linked server which has a database B.
>> now we are trying to insert into a table in a using data from the
>> database B. both of the tables in both the database are the same in
>> structure .
>> now when i use a query like
>> insert into a.Table1
>> ( No,
>> Name
>> )
>> select
>> no,
>> name
>> frrom
>> host_sever.B.dbo.table1
>> where <some condition >
>> the above query fails and the error says a nested distributed
>> transaction cannot be started
>> both the tables have a trigger attached to it .
>> we found that first inserting the data into a temp table and then
>> copying that data into the main table in local server in database A
>> works fine.
>> also i tested some scenario with no trigger and it works fine .
>> is this how it is when there are triggers attached and is there any way
>> we can succesfully run the query ableve without temp tables.
>> thanks
>> ravinder
>> Is the MSDTC service running on both machines? Is it enabled for
>> network access?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
> i looked at the services and DTC is runnign . there is another one is
> control panel which says MSDTC which i think you are referreing too.
> the network protocol says TCP/Ip and the salection default MS DTC
> server is blank and disabled .
> should this also not have created a issue when dum[ing into the temp
> table and cause the same issue.
> thanks
> ravinder
>
No, it wouldn't cause an issue that way. Everything that occurs within
a trigger is done inside a transaction, and for a transaction to cross a
linked server, DTC must be available.
I would suggest starting here:
http://www.sqlservercentral.com/columnists/ckempster/debuggingmsdtcissues.asp
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Insert SQL table into AS400 Alias

I have a SQL 2000 table that I want to insert into an AS400 alias. The 400 is a linked server to SQL. I can insert 1 record at a time with Insert OpenQuery.

insert openquery(AS400bck, 'select * from netfil.sp')
VALUES(1,6,2493,1,30,180,1,-8.00,0,-100.00,0,0,30,180,180,'',2,'X','','')

One of the tables is quite large so I don't want to have to use the values clause.

How can I insert the entire table at once?

Does this work?

I only have read only access to my AS400 environment...

insert openquery(AS400bck, 'select * from netfil.sp')
select [columns] from table where xxx=yyy
|||The statement did work correctly. Thanks for your help.

INSERT query using linked servers is slow.

Good day everyone,
I am setting up a simple data mart on a server running SQL 2005. It gets
updated nightly from another server with SQL 2000. Originally both database
s
were on the same server under 2000. Now that they are on different servers,
some of the INSERT queries seem to be running for an abnormally long time (s
o
long that I end up having to kill them).
All of the queries are of the type "INSERT INTO [Remote] SELECT [Fields]
from [Local-Tables]". Most only join together 3 or 4 tables, using key
fields. Only a couple of them are running long, the others complete in abou
t
the same time as before. The problem tables are not at all large compared t
o
the ones that work fine, and in some cases even have less activity (new
records).
I am pretty sure the two servers are linked correctly, as the majority of
these SQL commands still work fine. The remote query timeout parameter in
sp_configure has been set to 0, since the default of 600 was causing
problems. Other than that, no changes have been made on either server.
Any suggestions for other things I might check? Thanks in advance for your
help.Couple of things:
Are you using BEGIN TRAN at all? If so, it should be BEGIN DISTRIBUTED TRAN
on linked servers.
Have you checked all the usual rules which apply to large INSERTs? eg
- presumably there is no-one else logged in to the database when you are
doing these large inserts ie no danger of locking.
- are there a lot of indexes on the table you're inserting to; this will
slow things down
- are there any triggers firing? Think about disabling them
- is there any other audit stuff going on, traces etc?
- make sure all table names are fully qualified eg
server01.northwind.dbo.authors (presumably you have to do this anyway)
- if you've got IDENTITY columns, particularly as primary keys on the target
table, I believe these _can_ cause hotspots, although these are supposed to
be a minor concern on modern hardware
- think about breaking up your inserts, say 10,000 rows at a time so you can
keep track of their progress. I've seen techniques for doing this in loops
on the web using either SET ROWCOUNT or TOP
Hope that helps.
Let me know how you get on.
Damien
"Chris F" wrote:

> Good day everyone,
> I am setting up a simple data mart on a server running SQL 2005. It gets
> updated nightly from another server with SQL 2000. Originally both databa
ses
> were on the same server under 2000. Now that they are on different server
s,
> some of the INSERT queries seem to be running for an abnormally long time
(so
> long that I end up having to kill them).
> All of the queries are of the type "INSERT INTO [Remote] SELECT [Fields]
> from [Local-Tables]". Most only join together 3 or 4 tables, using key
> fields. Only a couple of them are running long, the others complete in ab
out
> the same time as before. The problem tables are not at all large compared
to
> the ones that work fine, and in some cases even have less activity (new
> records).
> I am pretty sure the two servers are linked correctly, as the majority of
> these SQL commands still work fine. The remote query timeout parameter in
> sp_configure has been set to 0, since the default of 600 was causing
> problems. Other than that, no changes have been made on either server.
> Any suggestions for other things I might check? Thanks in advance for you
r
> help.
>|||Chris
How much data do you insert?
Consider script out all indexes (remove them) and run the INSERT ,now that
after inserting re-create all indexes
"Chris F" <ChrisF@.discussions.microsoft.com> wrote in message
news:18089707-C1D6-4296-B937-C17D34DCAE65@.microsoft.com...
> Good day everyone,
> I am setting up a simple data mart on a server running SQL 2005. It gets
> updated nightly from another server with SQL 2000. Originally both
> databases
> were on the same server under 2000. Now that they are on different
> servers,
> some of the INSERT queries seem to be running for an abnormally long time
> (so
> long that I end up having to kill them).
> All of the queries are of the type "INSERT INTO [Remote] SELECT [Fields]
> from [Local-Tables]". Most only join together 3 or 4 tables, using key
> fields. Only a couple of them are running long, the others complete in
> about
> the same time as before. The problem tables are not at all large compared
> to
> the ones that work fine, and in some cases even have less activity (new
> records).
> I am pretty sure the two servers are linked correctly, as the majority of
> these SQL commands still work fine. The remote query timeout parameter in
> sp_configure has been set to 0, since the default of 600 was causing
> problems. Other than that, no changes have been made on either server.
> Any suggestions for other things I might check? Thanks in advance for
> your
> help.
>|||Thanks for your help.
Actually I am not using any form of BEGIN TRAN, since I keep getting a 7391
error in all cases. I am running a stored procedure consisting of several
delete and insert statements. Now that they are through the backlog (from
not having run for several days) all but one of the procedures have
acceptable run times.
Going down your list of checks, there are no other users, the only index is
the PK, no triggers, no audits/traces, no identity columns. I have been
fully qualifying the table on the remote server but not the local one where
the stored proc kicks off, I can try this and see if it helps. Will also
look at breaking up the query (I need to wait until the current run finishes
,
I found out over the wend that it does eventually). The only table we ar
e
still having problems with is the largest in the DB.
"Damien" wrote:
> Couple of things:
> Are you using BEGIN TRAN at all? If so, it should be BEGIN DISTRIBUTED TR
AN
> on linked servers.
> Have you checked all the usual rules which apply to large INSERTs? eg
> - presumably there is no-one else logged in to the database when you are
> doing these large inserts ie no danger of locking.
> - are there a lot of indexes on the table you're inserting to; this will
> slow things down
> - are there any triggers firing? Think about disabling them
> - is there any other audit stuff going on, traces etc?
> - make sure all table names are fully qualified eg
> server01.northwind.dbo.authors (presumably you have to do this anyway)
> - if you've got IDENTITY columns, particularly as primary keys on the targ
et
> table, I believe these _can_ cause hotspots, although these are supposed t
o
> be a minor concern on modern hardware
> - think about breaking up your inserts, say 10,000 rows at a time so you c
an
> keep track of their progress. I've seen techniques for doing this in loop
s
> on the web using either SET ROWCOUNT or TOP
>
> Hope that helps.
> Let me know how you get on.
>
> Damien
> "Chris F" wrote:
>

Insert produces error

Hi,

Using SQL Server 2000 with Windows 2000 Adv Server
&
Microsoft Access linked table (running stored procedure using ADO as
follows:

************************************************** ********
Private Sub cboAddrType_NotInList(NewData As String, Response As Integer)

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim msg As String

On Error GoTo Err_AddrType_NotInList
'Exit the procedure if the combo box was cleared
If Trim(NewData) = "" Then Exit Sub

'Confirm that the user wants to add AddrType
msg = "'" & Trim(NewData) & "' is not in the list." & vbCr & vbCr
msg = msg & "Do you want to add it?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
'If the user chose not to add AddrType, set the response
'argument to supress an error message and undo changes.
Response = acDataErrContinue
MsgBox "No record added.", vbOKOnly, "Action Cancelled"
Else
'If the user chose to add AddrType, open a recordset
'using the AddrType table

Set cmd = New ADODB.Command
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB;Data Source=penland01;Initial
Catalog=groomery;Integrated Security=SSPI;"

cmd.ActiveConnection = cnn
cmd.CommandText = "spInsertAddrType"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("AddrType", adVarChar,
adParamInput, , Trim(NewData))
cmd.Execute Parameters:=prm
'Set Response argument to indicate that new data is being added
Response = acDataErrAdded

cnn.Close
Set cnn = Nothing
End If

Exit_AddrType_NotInList:
Exit Sub

Err_AddrType_NotInList:
MsgBox Err.Description
Response = acDataErrContinue
************************************************** ********

"NewData" is a text string - in this case "Test"

The stored procedure referenced in the code is:

************************************
CREATE PROCEDURE [spInsertAddrType]
(@.AddrType [nvarchar](50))

AS
INSERT INTO [groomery].[dbo].[tblAddrTypes]
([fldAddrType])

VALUES
(@.AddrType)
GO
*************************************

When I execute this code, I receive the following error

"Cannot update identity column 'fldAddrTypeID'."

fldAddrTypeID is configured as follows:

***************************
Data Type = int
Identity = Yes
Identity Seed = 1
Identity Increment = 1
***************************

The documentation I've found online concerning this error says that it is
produced when you try to supply a value for an identity field without SET
IDENTITY_INSERT on. Obviously I am NOT specifying a value, so I can't
figure why I'm getting this error.

Thanks for any help you can offer.

ToddHi,

Found the answer elsewhere but thought I'd share it here in case someone
else has this problem.

Access's upsizing wizard created a trigger on tblAddrTypes which (evidently)
was meant to emulate Access's autonumber functionality. Once I deleted that
trigger, everything worked fine.

Todd
"Todd" <infoNOSPAM@.MAPSONgroomery.biz> wrote in message
news:T1j5e.11405$FN4.303@.newssvr21.news.prodigy.co m...
> Hi,
> Using SQL Server 2000 with Windows 2000 Adv Server
> &
> Microsoft Access linked table (running stored procedure using ADO as
> follows:
> ************************************************** ********
> Private Sub cboAddrType_NotInList(NewData As String, Response As Integer)
> Dim cnn As ADODB.Connection
> Dim cmd As ADODB.Command
> Dim prm As ADODB.Parameter
> Dim msg As String
> On Error GoTo Err_AddrType_NotInList
> 'Exit the procedure if the combo box was cleared
> If Trim(NewData) = "" Then Exit Sub
> 'Confirm that the user wants to add AddrType
> msg = "'" & Trim(NewData) & "' is not in the list." & vbCr & vbCr
> msg = msg & "Do you want to add it?"
> If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
> 'If the user chose not to add AddrType, set the response
> 'argument to supress an error message and undo changes.
> Response = acDataErrContinue
> MsgBox "No record added.", vbOKOnly, "Action Cancelled"
> Else
> 'If the user chose to add AddrType, open a recordset
> 'using the AddrType table
>
> Set cmd = New ADODB.Command
> Set cnn = New ADODB.Connection
> cnn.Open "Provider=SQLOLEDB;Data Source=penland01;Initial
> Catalog=groomery;Integrated Security=SSPI;"
> cmd.ActiveConnection = cnn
> cmd.CommandText = "spInsertAddrType"
> cmd.CommandType = adCmdStoredProc
> Set prm = cmd.CreateParameter("AddrType", adVarChar,
> adParamInput, , Trim(NewData))
> cmd.Execute Parameters:=prm
> 'Set Response argument to indicate that new data is being added
> Response = acDataErrAdded
> cnn.Close
> Set cnn = Nothing
> End If
> Exit_AddrType_NotInList:
> Exit Sub
> Err_AddrType_NotInList:
> MsgBox Err.Description
> Response = acDataErrContinue
> ************************************************** ********
> "NewData" is a text string - in this case "Test"
> The stored procedure referenced in the code is:
> ************************************
> CREATE PROCEDURE [spInsertAddrType]
> (@.AddrType [nvarchar](50))
> AS
> INSERT INTO [groomery].[dbo].[tblAddrTypes]
> ([fldAddrType])
> VALUES
> (@.AddrType)
> GO
> *************************************
> When I execute this code, I receive the following error
> "Cannot update identity column 'fldAddrTypeID'."
> fldAddrTypeID is configured as follows:
> ***************************
> Data Type = int
> Identity = Yes
> Identity Seed = 1
> Identity Increment = 1
> ***************************
> The documentation I've found online concerning this error says that it is
> produced when you try to supply a value for an identity field without SET
> IDENTITY_INSERT on. Obviously I am NOT specifying a value, so I can't
> figure why I'm getting this error.
> Thanks for any help you can offer.
> Todd

Insert problem with linked server

Both servers running SQL 2000

I have set up on our local SQL server (using Enterprise Manager) a linked
server running on our ISP. Just did new linked server and added remote
password and login.

The following three queries work:

insert into LinkedServer.dbname.dbo.Table2
select *
from LinkedServer.dbname.dbo.Table1

select *
into LocalTable
from LinkedServer.dbname.dbo.Table1

insert into LocalTable
select *
from LinkedServer.dbname.dbo.Table1

This query, which is what we really want to do, does not work:

insert into LinkedServer.dbname.dbo.Table1
select *
from LocalTable

and returns the error: 'The cursor does not include the table being modified
or the table is not updatable through the cursor.'

I am new to all this and would welcome some help.

AdrianI believe I have now resolved this

In fact the example below would work

> insert into LinkedServer.dbname.dbo.Table1
> select *
> from LocalTable

I was trying to insert into a table on the linked server that was not owned
by the dbo but by the remote username. It seems that providing the owner of
the table is dbo it will be OK.

Adrian.

"Adrian" <NoSpam@.hotmail.com> wrote in message
news:ANReb.6576$8_4.54623402@.news-text.cableinet.net...
> Both servers running SQL 2000
> I have set up on our local SQL server (using Enterprise Manager) a linked
> server running on our ISP. Just did new linked server and added remote
> password and login.
> The following three queries work:
> insert into LinkedServer.dbname.dbo.Table2
> select *
> from LinkedServer.dbname.dbo.Table1
> select *
> into LocalTable
> from LinkedServer.dbname.dbo.Table1
> insert into LocalTable
> select *
> from LinkedServer.dbname.dbo.Table1
>
> This query, which is what we really want to do, does not work:
> insert into LinkedServer.dbname.dbo.Table1
> select *
> from LocalTable
> and returns the error: 'The cursor does not include the table being
modified
> or the table is not updatable through the cursor.'
> I am new to all this and would welcome some help.
> Adrian

Insert Oracle Linked Server timestamp error...

I'm trying to insert into an Oracle table, setup via a linked server.
Everything works, but I receive an error during the insert, the
statement and error are below:
INSERT INTO ORALINKSRV..FIN.FILE_XFER_STATUS_DETAIL
(FILE_SEQ_ID,FILE_RETURN_CD,OFFICE_DEPT_
ID,FILE_NM,
FILE_PROC_DTTM FILE_SUFFIX_ID)
Select FILE_SEQ_ID,FILE_RETURN_CD,OFFICE_DEPT_I
D,FILE_NM,
FILE_PROC_DTTM,FILE_SUFFIX_ID
From Temp_FIN_Detail
ERROR:
Server: Msg 7354, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied invalid metadata for column
'FILE_PROC_DTTM'. The data type is not supported.
OLE DB error trace [Non-interface error: Column 'FILE_PROC_DTTM'
(ordinal 5) of object '"FIN"."FILE_XFER_STATUS_DETAIL"' reported an
unsupported value for DBTYPE of 13].
We've updated to the latest Oracle drivers on the server. We've
switched between the Oracle and MS OleDB Providers.
Any work arounds appreciated...
Thanks
bobAdditional information.
This is Oracle 9.203.
Oracle introduced a new data type timestamp, this data type does not
seem to be updateable or insert via SQL Server linked server.
I'd appreciate if anyone else could test and confirm this. We've
contacted MS Support and they weren't very helpful, indicating we were
the only ones who have reported a problem, and that is is most likely
an Oracle problem.
I'm just looking for some guidance so we can establish a standard to
not use the Oracle time stamp data type on tables that require data to
be transferred back and forth between SQL Server and Oracle.
Thanks
bob|||Try using Openquery and wrapping the value in to_timestamp.
You should also check your nls timestamp format on the
Oracle server.
-Sue
On 28 May 2004 12:34:05 -0700, bob@.lifeasbob.com (Bob
Horkay) wrote:

>Additional information.
>This is Oracle 9.203.
>Oracle introduced a new data type timestamp, this data type does not
>seem to be updateable or insert via SQL Server linked server.
>I'd appreciate if anyone else could test and confirm this. We've
>contacted MS Support and they weren't very helpful, indicating we were
>the only ones who have reported a problem, and that is is most likely
>an Oracle problem.
>I'm just looking for some guidance so we can establish a standard to
>not use the Oracle time stamp data type on tables that require data to
>be transferred back and forth between SQL Server and Oracle.
>Thanks
>bob|||Sue,
The query can be made to work for openquery with a to_char() statement,
unfortunately this does not work for Inserts.
I finally did receive a response from Microsoft support, the timestamp data
type is not supported, there is no plan to support it. They suggested using
the .net managed providers for Oracle, which of course can not be used for
linked servers.
They referenced several kb articles about data types supported and not
supported in Oracle, but basically the timestamp, clobs and blobs were out
( i expected the blobs but was surprised by the timestamp).
Anyway at least I know Microsofts position on it's ole db provider, they
support it, but have no plans to enhance it, watch thos data types
introduced from Oracle 8 on...
Bob
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:04snb01147cfcpn394k2ufrrv9jsr953u9@.
4ax.com...
> Try using Openquery and wrapping the value in to_timestamp.
> You should also check your nls timestamp format on the
> Oracle server.
> -Sue
> On 28 May 2004 12:34:05 -0700, bob@.lifeasbob.com (Bob
> Horkay) wrote:
>
>

Insert Oracle Linked Server timestamp error...

I'm trying to insert into an Oracle table, setup via a linked server.
Everything works, but I receive an error during the insert, the
statement and error are below:
INSERT INTO ORALINKSRV..FIN.FILE_XFER_STATUS_DETAIL
(FILE_SEQ_ID,FILE_RETURN_CD,OFFICE_DEPT_ID,FILE_NM ,
FILE_PROC_DTTM FILE_SUFFIX_ID)
Select FILE_SEQ_ID,FILE_RETURN_CD,OFFICE_DEPT_ID,FILE_NM,
FILE_PROC_DTTM,FILE_SUFFIX_ID
From Temp_FIN_Detail
ERROR:
Server: Msg 7354, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied invalid metadata for column
'FILE_PROC_DTTM'. The data type is not supported.
OLE DB error trace [Non-interface error: Column 'FILE_PROC_DTTM'
(ordinal 5) of object '"FIN"."FILE_XFER_STATUS_DETAIL"' reported an
unsupported value for DBTYPE of 13].
We've updated to the latest Oracle drivers on the server. We've
switched between the Oracle and MS OleDB Providers.
Any work arounds appreciated...
Thanks
bob
Additional information.
This is Oracle 9.203.
Oracle introduced a new data type timestamp, this data type does not
seem to be updateable or insert via SQL Server linked server.
I'd appreciate if anyone else could test and confirm this. We've
contacted MS Support and they weren't very helpful, indicating we were
the only ones who have reported a problem, and that is is most likely
an Oracle problem.
I'm just looking for some guidance so we can establish a standard to
not use the Oracle time stamp data type on tables that require data to
be transferred back and forth between SQL Server and Oracle.
Thanks
bob
|||Try using Openquery and wrapping the value in to_timestamp.
You should also check your nls timestamp format on the
Oracle server.
-Sue
On 28 May 2004 12:34:05 -0700, bob@.lifeasbob.com (Bob
Horkay) wrote:

>Additional information.
>This is Oracle 9.203.
>Oracle introduced a new data type timestamp, this data type does not
>seem to be updateable or insert via SQL Server linked server.
>I'd appreciate if anyone else could test and confirm this. We've
>contacted MS Support and they weren't very helpful, indicating we were
>the only ones who have reported a problem, and that is is most likely
>an Oracle problem.
>I'm just looking for some guidance so we can establish a standard to
>not use the Oracle time stamp data type on tables that require data to
>be transferred back and forth between SQL Server and Oracle.
>Thanks
>bob
|||Sue,
The query can be made to work for openquery with a to_char() statement,
unfortunately this does not work for Inserts.
I finally did receive a response from Microsoft support, the timestamp data
type is not supported, there is no plan to support it. They suggested using
the .net managed providers for Oracle, which of course can not be used for
linked servers.
They referenced several kb articles about data types supported and not
supported in Oracle, but basically the timestamp, clobs and blobs were out
( i expected the blobs but was surprised by the timestamp).
Anyway at least I know Microsofts position on it's ole db provider, they
support it, but have no plans to enhance it, watch thos data types
introduced from Oracle 8 on...
Bob
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:04snb01147cfcpn394k2ufrrv9jsr953u9@.4ax.com...
> Try using Openquery and wrapping the value in to_timestamp.
> You should also check your nls timestamp format on the
> Oracle server.
> -Sue
> On 28 May 2004 12:34:05 -0700, bob@.lifeasbob.com (Bob
> Horkay) wrote:
>