Hi,
we are trying to get some data from a linked server via an ODBC connection.
This server has 3 intances of the same software, we load 2 tables from each
installation. From one instance we get both tables, from the two others we
can only acces one of the two.
I explain that only because I want to make clear the the ODBC driver
"normally" should be able to be used with Linked servers.
When we try to load the two other tables, we get an error 7317:
OLE/DB provider returned an invalid schema definition
The ODBC driver is based on the SIMBA ODBC framework.
Any idea what I can do? What's about the "schema definition"?
Thanks,
Thomas1. Can you do simple select like "SELECT * from
servername.dbname.owner_name.table_name"
2. Does the account that has been used in defining the linked server has
proper security on the linked server tables.
"Thomas Pagel" <tpagel@.software4you.com> wrote in message
news:uiSX6u3VDHA.1180@.TK2MSFTNGP11.phx.gbl...
> Hi,
> we are trying to get some data from a linked server via an ODBC
connection.
> This server has 3 intances of the same software, we load 2 tables from
each
> installation. From one instance we get both tables, from the two others we
> can only acces one of the two.
> I explain that only because I want to make clear the the ODBC driver
> "normally" should be able to be used with Linked servers.
> When we try to load the two other tables, we get an error 7317:
> OLE/DB provider returned an invalid schema definition
> The ODBC driver is based on the SIMBA ODBC framework.
> Any idea what I can do? What's about the "schema definition"?
>
> Thanks,
>
> Thomas
>|||Ammar,
1.) This works with some of the tables, others don't
2.) The queries work well if I direclty use the ODBC driver i.e. with DTS
with the same user and it has all rights, too. So this couldn't be a
security issue...
Thanks,
Thomas
"Ammar" <ammar.ansari@.anthem.com> schrieb im Newsbeitrag
news:%23CeduY4VDHA.2544@.tk2msftngp13.phx.gbl...
> 1. Can you do simple select like "SELECT * from
> servername.dbname.owner_name.table_name"
> 2. Does the account that has been used in defining the linked server has
> proper security on the linked server tables.
>
> "Thomas Pagel" <tpagel@.software4you.com> wrote in message
> news:uiSX6u3VDHA.1180@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > we are trying to get some data from a linked server via an ODBC
> connection.
> > This server has 3 intances of the same software, we load 2 tables from
> each
> > installation. From one instance we get both tables, from the two others
we
> > can only acces one of the two.
> >
> > I explain that only because I want to make clear the the ODBC driver
> > "normally" should be able to be used with Linked servers.
> >
> > When we try to load the two other tables, we get an error 7317:
> >
> > OLE/DB provider returned an invalid schema definition
> >
> > The ODBC driver is based on the SIMBA ODBC framework.
> >
> > Any idea what I can do? What's about the "schema definition"?
> >
> >
> > Thanks,
> >
> >
> > Thomas
> >
> >
>
Showing posts with label via. Show all posts
Showing posts with label via. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Problem with Insert trigger
I have an insert trigger that works 99.999% of the time. The trigger is
fired via a dts process that runs every 10 minutes which inserts data into a
ForecastTonnageChanges table. Based on changes to this
ForecastTonnageChanges, I want to update the live Forecast table. The
trigger fires this event. My basic logic in the trigger is:
Delete from Forecast
where exists (select *
from inserted
where <joining key columns> )
Insert into Forecast
select ...
from inserted
When this doesn't work, I am ending up with extra records in my forecast
table. I'm wondering if another insert has happened and between the delete
and the insert in the trigger, an additional row is in the inserted table.
Is this possible? I would think implied locking via the trigger would
prevent this. But, I'm grasping for straws here.
If you have ideas on a more reliable way to implement this... please share.Hi
A trigger is fired for each statement, therefore you will not get extra rows
in the inserted/deleted tables.
As you don't give DDL and all the trigger code it is hard to say where you
are going wrong, but you may want to use profiler to see what is happening.
You should also implement error handling to make sure that the statement
succeeds and all the relivent statements in the transaction are rolled back
if a failure occurs.
John
"Erin" wrote:
> I have an insert trigger that works 99.999% of the time. The trigger is
> fired via a dts process that runs every 10 minutes which inserts data into
a
> ForecastTonnageChanges table. Based on changes to this
> ForecastTonnageChanges, I want to update the live Forecast table. The
> trigger fires this event. My basic logic in the trigger is:
> Delete from Forecast
> where exists (select *
> from inserted
> where <joining key columns> )
> Insert into Forecast
> select ...
> from inserted
> When this doesn't work, I am ending up with extra records in my forecast
> table. I'm wondering if another insert has happened and between the delet
e
> and the insert in the trigger, an additional row is in the inserted table.
> Is this possible? I would think implied locking via the trigger would
> prevent this. But, I'm grasping for straws here.
> If you have ideas on a more reliable way to implement this... please share.[/color
]|||Without any more information, could you have a forecast VIEW of the
most current rows in the ForecastTonnageChanges table instead of
physically shuffling all this data around? You obviously have a time
stamp on the new data, so that should be easy enough.
fired via a dts process that runs every 10 minutes which inserts data into a
ForecastTonnageChanges table. Based on changes to this
ForecastTonnageChanges, I want to update the live Forecast table. The
trigger fires this event. My basic logic in the trigger is:
Delete from Forecast
where exists (select *
from inserted
where <joining key columns> )
Insert into Forecast
select ...
from inserted
When this doesn't work, I am ending up with extra records in my forecast
table. I'm wondering if another insert has happened and between the delete
and the insert in the trigger, an additional row is in the inserted table.
Is this possible? I would think implied locking via the trigger would
prevent this. But, I'm grasping for straws here.
If you have ideas on a more reliable way to implement this... please share.Hi
A trigger is fired for each statement, therefore you will not get extra rows
in the inserted/deleted tables.
As you don't give DDL and all the trigger code it is hard to say where you
are going wrong, but you may want to use profiler to see what is happening.
You should also implement error handling to make sure that the statement
succeeds and all the relivent statements in the transaction are rolled back
if a failure occurs.
John
"Erin" wrote:
> I have an insert trigger that works 99.999% of the time. The trigger is
> fired via a dts process that runs every 10 minutes which inserts data into
a
> ForecastTonnageChanges table. Based on changes to this
> ForecastTonnageChanges, I want to update the live Forecast table. The
> trigger fires this event. My basic logic in the trigger is:
> Delete from Forecast
> where exists (select *
> from inserted
> where <joining key columns> )
> Insert into Forecast
> select ...
> from inserted
> When this doesn't work, I am ending up with extra records in my forecast
> table. I'm wondering if another insert has happened and between the delet
e
> and the insert in the trigger, an additional row is in the inserted table.
> Is this possible? I would think implied locking via the trigger would
> prevent this. But, I'm grasping for straws here.
> If you have ideas on a more reliable way to implement this... please share.[/color
]|||Without any more information, could you have a forecast VIEW of the
most current rows in the ForecastTonnageChanges table instead of
physically shuffling all this data around? You obviously have a time
stamp on the new data, so that should be easy enough.
Wednesday, March 21, 2012
Problem with HTML slightly garbled sending email as web archive
I'm experiencing a problem with HTML code being slightly messed up when
sending a subscription via email in web archive format.
The report displays fine in the Report Manager. However, went emailed some
whitespace is seemingly randomly added to the HTML page. Depending on where
that whitespace is, this might not be a problem. But if the whitespace is
inserted in some places within an HTML tag, then the tag isn't rendered
properly.
Example: a space is added following the "<" in "</DIV>", thus giving "<
/DIV>". Since this is invalid HTML, it displays as text in the report.
Example: a cell in a table is defined as right-aligned and in a specific
font. All 25 rows in the table (x 3 columns = 75 cells) all display
correctly:
<P class=MsoNormal style="TEXT-ALIGN: right" align=right><FONT
face="Eras Medium ITC" color=black size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Eras
Medium ITC'">
*EXCEPT* one cell, where the following HTML is used:
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt">
Why did RS render this cell differently?This might be an issue with the local SMTP. Are you using the pickup
directory to send email?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Columbia Kai" <Columbia Kai@.discussions.microsoft.com> wrote in message
news:A0190874-9273-4F1C-9BD1-CF0CEB79616D@.microsoft.com...
> I'm experiencing a problem with HTML code being slightly messed up when
> sending a subscription via email in web archive format.
> The report displays fine in the Report Manager. However, went emailed
> some
> whitespace is seemingly randomly added to the HTML page. Depending on
> where
> that whitespace is, this might not be a problem. But if the whitespace is
> inserted in some places within an HTML tag, then the tag isn't rendered
> properly.
> Example: a space is added following the "<" in "</DIV>", thus giving "<
> /DIV>". Since this is invalid HTML, it displays as text in the report.
> Example: a cell in a table is defined as right-aligned and in a specific
> font. All 25 rows in the table (x 3 columns = 75 cells) all display
> correctly:
> <P class=MsoNormal style="TEXT-ALIGN: right" align=right><FONT
> face="Eras Medium ITC" color=black size=2><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Eras
> Medium ITC'">
> *EXCEPT* one cell, where the following HTML is used:
> <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
> style="FONT-SIZE: 12pt">
> Why did RS render this cell differently?|||Thanks for your reply, Daniel.
I've tried this with two SMTP mail servers, both with the same result. I
changed the definition via the RSReportServer.config file.
Neither SMTP server is on my local machine.
One resides on a Win2003 box and is a MS mail server; the other resides on a
Win2000 Server box and is a product called Merak Mail Server. Ultimately
they send via the Columbia University mail server, which is sendmail, I
believe.
(I'm not a systems guy so sorry if my answers aren't dead-on what you asked
for.)
Please also see my comments on another post title "Web archive subrsciption
gets messed up" (note misspelling of 'subscription'). I ran into an
identical problem like this when sending email from an Oracle PL/SQL program.
The resolution is to intersprese CRLF into the msg at least every 990 bytes.
Thanks Daniel. This is the one problem that's keeping me from implementing
RS and beginning to retire Crystal Enterprise.
"Daniel Reib [MSFT]" wrote:
> This might be an issue with the local SMTP. Are you using the pickup
> directory to send email?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Columbia Kai" <Columbia Kai@.discussions.microsoft.com> wrote in message
> news:A0190874-9273-4F1C-9BD1-CF0CEB79616D@.microsoft.com...
> > I'm experiencing a problem with HTML code being slightly messed up when
> > sending a subscription via email in web archive format.
> >
> > The report displays fine in the Report Manager. However, went emailed
> > some
> > whitespace is seemingly randomly added to the HTML page. Depending on
> > where
> > that whitespace is, this might not be a problem. But if the whitespace is
> > inserted in some places within an HTML tag, then the tag isn't rendered
> > properly.
> >
> > Example: a space is added following the "<" in "</DIV>", thus giving "<
> > /DIV>". Since this is invalid HTML, it displays as text in the report.
> >
> > Example: a cell in a table is defined as right-aligned and in a specific
> > font. All 25 rows in the table (x 3 columns = 75 cells) all display
> > correctly:
> > <P class=MsoNormal style="TEXT-ALIGN: right" align=right><FONT
> > face="Eras Medium ITC" color=black size=2><SPAN
> > style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Eras
> > Medium ITC'">
> > *EXCEPT* one cell, where the following HTML is used:
> > <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
> > style="FONT-SIZE: 12pt">
> > Why did RS render this cell differently?
>
>|||I haven't done this yet but what was suggested to me (I too am having
problems and am using an external smtp server too) was to use Windows 2003
smtp service and have it forward/route (not sure the term) to the external
smtp server. The Windows 2003 smtp server is configured to be looking at a
directory and forwarding things on from there. Then you have your report
saved to the pickup directory. Note that I haven't done this so I am a
little vague on how it all works.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Columbia Kai" <ColumbiaKai@.discussions.microsoft.com> wrote in message
news:8995F878-CB68-4766-BB00-654D7580BAF1@.microsoft.com...
> Thanks for your reply, Daniel.
> I've tried this with two SMTP mail servers, both with the same result. I
> changed the definition via the RSReportServer.config file.
> Neither SMTP server is on my local machine.
> One resides on a Win2003 box and is a MS mail server; the other resides on
> a
> Win2000 Server box and is a product called Merak Mail Server. Ultimately
> they send via the Columbia University mail server, which is sendmail, I
> believe.
> (I'm not a systems guy so sorry if my answers aren't dead-on what you
> asked
> for.)
> Please also see my comments on another post title "Web archive
> subrsciption
> gets messed up" (note misspelling of 'subscription'). I ran into an
> identical problem like this when sending email from an Oracle PL/SQL
> program.
> The resolution is to intersprese CRLF into the msg at least every 990
> bytes.
> Thanks Daniel. This is the one problem that's keeping me from
> implementing
> RS and beginning to retire Crystal Enterprise.
>
> "Daniel Reib [MSFT]" wrote:
>> This might be an issue with the local SMTP. Are you using the pickup
>> directory to send email?
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Columbia Kai" <Columbia Kai@.discussions.microsoft.com> wrote in message
>> news:A0190874-9273-4F1C-9BD1-CF0CEB79616D@.microsoft.com...
>> > I'm experiencing a problem with HTML code being slightly messed up when
>> > sending a subscription via email in web archive format.
>> >
>> > The report displays fine in the Report Manager. However, went emailed
>> > some
>> > whitespace is seemingly randomly added to the HTML page. Depending on
>> > where
>> > that whitespace is, this might not be a problem. But if the whitespace
>> > is
>> > inserted in some places within an HTML tag, then the tag isn't rendered
>> > properly.
>> >
>> > Example: a space is added following the "<" in "</DIV>", thus giving "<
>> > /DIV>". Since this is invalid HTML, it displays as text in the report.
>> >
>> > Example: a cell in a table is defined as right-aligned and in a
>> > specific
>> > font. All 25 rows in the table (x 3 columns = 75 cells) all display
>> > correctly:
>> > <P class=MsoNormal style="TEXT-ALIGN: right" align=right><FONT
>> > face="Eras Medium ITC" color=black size=2><SPAN
>> > style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY:
>> > 'Eras
>> > Medium ITC'">
>> > *EXCEPT* one cell, where the following HTML is used:
>> > <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
>> > style="FONT-SIZE: 12pt">
>> > Why did RS render this cell differently?
>>sql
sending a subscription via email in web archive format.
The report displays fine in the Report Manager. However, went emailed some
whitespace is seemingly randomly added to the HTML page. Depending on where
that whitespace is, this might not be a problem. But if the whitespace is
inserted in some places within an HTML tag, then the tag isn't rendered
properly.
Example: a space is added following the "<" in "</DIV>", thus giving "<
/DIV>". Since this is invalid HTML, it displays as text in the report.
Example: a cell in a table is defined as right-aligned and in a specific
font. All 25 rows in the table (x 3 columns = 75 cells) all display
correctly:
<P class=MsoNormal style="TEXT-ALIGN: right" align=right><FONT
face="Eras Medium ITC" color=black size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Eras
Medium ITC'">
*EXCEPT* one cell, where the following HTML is used:
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt">
Why did RS render this cell differently?This might be an issue with the local SMTP. Are you using the pickup
directory to send email?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Columbia Kai" <Columbia Kai@.discussions.microsoft.com> wrote in message
news:A0190874-9273-4F1C-9BD1-CF0CEB79616D@.microsoft.com...
> I'm experiencing a problem with HTML code being slightly messed up when
> sending a subscription via email in web archive format.
> The report displays fine in the Report Manager. However, went emailed
> some
> whitespace is seemingly randomly added to the HTML page. Depending on
> where
> that whitespace is, this might not be a problem. But if the whitespace is
> inserted in some places within an HTML tag, then the tag isn't rendered
> properly.
> Example: a space is added following the "<" in "</DIV>", thus giving "<
> /DIV>". Since this is invalid HTML, it displays as text in the report.
> Example: a cell in a table is defined as right-aligned and in a specific
> font. All 25 rows in the table (x 3 columns = 75 cells) all display
> correctly:
> <P class=MsoNormal style="TEXT-ALIGN: right" align=right><FONT
> face="Eras Medium ITC" color=black size=2><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Eras
> Medium ITC'">
> *EXCEPT* one cell, where the following HTML is used:
> <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
> style="FONT-SIZE: 12pt">
> Why did RS render this cell differently?|||Thanks for your reply, Daniel.
I've tried this with two SMTP mail servers, both with the same result. I
changed the definition via the RSReportServer.config file.
Neither SMTP server is on my local machine.
One resides on a Win2003 box and is a MS mail server; the other resides on a
Win2000 Server box and is a product called Merak Mail Server. Ultimately
they send via the Columbia University mail server, which is sendmail, I
believe.
(I'm not a systems guy so sorry if my answers aren't dead-on what you asked
for.)
Please also see my comments on another post title "Web archive subrsciption
gets messed up" (note misspelling of 'subscription'). I ran into an
identical problem like this when sending email from an Oracle PL/SQL program.
The resolution is to intersprese CRLF into the msg at least every 990 bytes.
Thanks Daniel. This is the one problem that's keeping me from implementing
RS and beginning to retire Crystal Enterprise.
"Daniel Reib [MSFT]" wrote:
> This might be an issue with the local SMTP. Are you using the pickup
> directory to send email?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Columbia Kai" <Columbia Kai@.discussions.microsoft.com> wrote in message
> news:A0190874-9273-4F1C-9BD1-CF0CEB79616D@.microsoft.com...
> > I'm experiencing a problem with HTML code being slightly messed up when
> > sending a subscription via email in web archive format.
> >
> > The report displays fine in the Report Manager. However, went emailed
> > some
> > whitespace is seemingly randomly added to the HTML page. Depending on
> > where
> > that whitespace is, this might not be a problem. But if the whitespace is
> > inserted in some places within an HTML tag, then the tag isn't rendered
> > properly.
> >
> > Example: a space is added following the "<" in "</DIV>", thus giving "<
> > /DIV>". Since this is invalid HTML, it displays as text in the report.
> >
> > Example: a cell in a table is defined as right-aligned and in a specific
> > font. All 25 rows in the table (x 3 columns = 75 cells) all display
> > correctly:
> > <P class=MsoNormal style="TEXT-ALIGN: right" align=right><FONT
> > face="Eras Medium ITC" color=black size=2><SPAN
> > style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Eras
> > Medium ITC'">
> > *EXCEPT* one cell, where the following HTML is used:
> > <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
> > style="FONT-SIZE: 12pt">
> > Why did RS render this cell differently?
>
>|||I haven't done this yet but what was suggested to me (I too am having
problems and am using an external smtp server too) was to use Windows 2003
smtp service and have it forward/route (not sure the term) to the external
smtp server. The Windows 2003 smtp server is configured to be looking at a
directory and forwarding things on from there. Then you have your report
saved to the pickup directory. Note that I haven't done this so I am a
little vague on how it all works.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Columbia Kai" <ColumbiaKai@.discussions.microsoft.com> wrote in message
news:8995F878-CB68-4766-BB00-654D7580BAF1@.microsoft.com...
> Thanks for your reply, Daniel.
> I've tried this with two SMTP mail servers, both with the same result. I
> changed the definition via the RSReportServer.config file.
> Neither SMTP server is on my local machine.
> One resides on a Win2003 box and is a MS mail server; the other resides on
> a
> Win2000 Server box and is a product called Merak Mail Server. Ultimately
> they send via the Columbia University mail server, which is sendmail, I
> believe.
> (I'm not a systems guy so sorry if my answers aren't dead-on what you
> asked
> for.)
> Please also see my comments on another post title "Web archive
> subrsciption
> gets messed up" (note misspelling of 'subscription'). I ran into an
> identical problem like this when sending email from an Oracle PL/SQL
> program.
> The resolution is to intersprese CRLF into the msg at least every 990
> bytes.
> Thanks Daniel. This is the one problem that's keeping me from
> implementing
> RS and beginning to retire Crystal Enterprise.
>
> "Daniel Reib [MSFT]" wrote:
>> This might be an issue with the local SMTP. Are you using the pickup
>> directory to send email?
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Columbia Kai" <Columbia Kai@.discussions.microsoft.com> wrote in message
>> news:A0190874-9273-4F1C-9BD1-CF0CEB79616D@.microsoft.com...
>> > I'm experiencing a problem with HTML code being slightly messed up when
>> > sending a subscription via email in web archive format.
>> >
>> > The report displays fine in the Report Manager. However, went emailed
>> > some
>> > whitespace is seemingly randomly added to the HTML page. Depending on
>> > where
>> > that whitespace is, this might not be a problem. But if the whitespace
>> > is
>> > inserted in some places within an HTML tag, then the tag isn't rendered
>> > properly.
>> >
>> > Example: a space is added following the "<" in "</DIV>", thus giving "<
>> > /DIV>". Since this is invalid HTML, it displays as text in the report.
>> >
>> > Example: a cell in a table is defined as right-aligned and in a
>> > specific
>> > font. All 25 rows in the table (x 3 columns = 75 cells) all display
>> > correctly:
>> > <P class=MsoNormal style="TEXT-ALIGN: right" align=right><FONT
>> > face="Eras Medium ITC" color=black size=2><SPAN
>> > style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY:
>> > 'Eras
>> > Medium ITC'">
>> > *EXCEPT* one cell, where the following HTML is used:
>> > <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
>> > style="FONT-SIZE: 12pt">
>> > Why did RS render this cell differently?
>>sql
Friday, March 9, 2012
problem with duplicate keys
Hello,
There is a program which performs some scripted actions via ODBC on tables
in some database on mssql 2000. Sometimes that program tries to insert
record with key that is already present in the database. The error comes up
and the program stops.
Is there any way to globally configure the database or the whole mssql
server to ignore such attempts and let the script continue without any error
when the script tries to insert duplicate-key records?
Thank you for any suggestions.
Pawel BanysOn Thu, 5 Feb 2004 09:13:28 +0100 in comp.databases.ms-sqlserver,
"Pawel Banys" <voland@.dmz.com.pl> wrote:
>Hello,
>There is a program which performs some scripted actions via ODBC on tables
>in some database on mssql 2000. Sometimes that program tries to insert
>record with key that is already present in the database. The error comes up
>and the program stops.
>Is there any way to globally configure the database or the whole mssql
>server to ignore such attempts and let the script continue without any error
>when the script tries to insert duplicate-key records?
Maybe you should change your script to check for the keys first,
instead of blindly whacking stuff into them e.g.
insert into table1 (pk, column)
select pk, column from table2
where table1.pk not in (select pk from table2)
--
A)bort, R)etry, I)nfluence with large hammer.|||"Pawel Banys" <voland@.dmz.com.pl> wrote in message news:<bvsu0g$3fp$1@.atlantis.news.tpi.pl>...
> Hello,
> There is a program which performs some scripted actions via ODBC on tables
> in some database on mssql 2000. Sometimes that program tries to insert
> record with key that is already present in the database. The error comes up
> and the program stops.
> Is there any way to globally configure the database or the whole mssql
> server to ignore such attempts and let the script continue without any error
> when the script tries to insert duplicate-key records?
> Thank you for any suggestions.
> Pawel Banys
Are you talking about error 2627 (violation of primary key
constraint)? If so, then the current batch should continue anyway,
unless of course the code has error handling to stop immediately on an
error.
Assuming you have control of the code, you should really avoid the
error instead:
insert into dbo.Destination (col1, col2, ...)
select col1, col2, ...
from dbo.Source s
where not exists (select * from dbo.Destination d
where d.PrimaryKey = s.PrimaryKey)
If you don't control the code, then I don't think there's much you can
do - the error can't be suppressed, and needs to be handled on the
client. Perhaps you can clean up the data somehow before passing it to
the program?
Simon|||Pawel Banys (voland@.dmz.com.pl) writes:
> There is a program which performs some scripted actions via ODBC on
> tables in some database on mssql 2000. Sometimes that program tries to
> insert record with key that is already present in the database. The
> error comes up and the program stops.
> Is there any way to globally configure the database or the whole mssql
> server to ignore such attempts and let the script continue without any
> error when the script tries to insert duplicate-key records?
For a unique index - but not a PK or UNIQUE constraint - you can include
the option IGNORE_DUP_KEY. With this setting, SQL Server does not find
it an error condition if you try to insert a duplicate, but leaves @.@.error
unchanged. Unfortunately, though, the message that is raised, incorrectly
has severity level 16 and not 10, so a client may still belive that
things went wrong.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I too would like to be able to have it ignore errors thrown on an
insert. I'm doing the insert over a WAN, so checking for all records on
the destination server takes forever.
insert into server2.database.dbo.table select * from
server1.database.dbo.table
Adding a where clause would take much MUCH longer to perform vs. a
straight insert.
I've been deleting all records from the destination table, but that's
not a great way of doing it. Any way of having the insert simply ignore
errors and continue?
================
Jordan Bowness
================
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Jordan Bowness (jb_at_bizeworld_dot_ca) writes:
> I too would like to be able to have it ignore errors thrown on an
> insert. I'm doing the insert over a WAN, so checking for all records on
> the destination server takes forever.
> insert into server2.database.dbo.table select * from
> server1.database.dbo.table
> Adding a where clause would take much MUCH longer to perform vs. a
> straight insert.
> I've been deleting all records from the destination table, but that's
> not a great way of doing it. Any way of having the insert simply ignore
> errors and continue?
There is the option that I discussed in the article you replied to. That
is, replace the primary key with a unique index with IGNORE_DUP_KEY.
Another possibility is to insert the data into a staging table on the
target server, and then call a stored procedure on the target server
which performs an INSERT targettbl SELECT * FROM staging WHERE NOT EXISTS.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I'm having a similar issue here, i'm using a Insert Trigger to add a row to an EXCEPTIONS table when a condition is met during the insert process. The condition could be met multiple times, but I only need 1 record to go into the EXCEPTIONS table per PK combination. I could limit this using a WHERE clause however the EXCEPTIONS table has a composite primary key and I'm not sure how to check for that.
INSERT INTO STAFFING_EXCEPTIONS ...
SELECT ...
FROM INSERTED I
INNER JOIN STAFFING_3W_REQ REQ ON (I.PROJECT_CODE = REQ.PROJECT_CODE AND I.LOB_CODE = REQ.LOB_CODE AND I.ROW_DATETIME = REQ.ROW_DATETIME AND I.FTE_GOAL != REQ.FTE_GOAL)
Wednesday, March 7, 2012
problem with distributed transaction via linked server
hi,
i am trying to connect to oracle 9i using sqlserver 2000 linked server.
here the linked server name is intersql. when i put this statement in begin
and end transaction block and try to execute it is giving the following
error
Server: Msg 7391, Level 16, State 1, Procedure
ihmtproc_emp_assmbly_interface, Line 93
The operation could not be performed because the OLE DB provider 'MSDAORA'
was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
INSERT INTO eemp_assembly_interface
(
[drawing_file_nm],
[part_number_txt],
[drawing_status_txt],
[drawing_revision_txt],
[model_file_nm],
[model_title_txt],
[parent_part_number_txt],
[release_status_txt],
[emp_desc],
[created_by_user_id],
[created_on_dt]
)
SELECT drawing_filename,
part_number,
drawing_status,
drawing_revision,
model_filename,
model_title ,
parent_part_number,
release_status,
description,
999999999 as created_by_user_id,
GETDATE() as created_on_dt
FROM OPENQUERY(productcenter,
'SELECT
drawing_filename,
part_number,
drawing_status,
drawing_revision,
model_filename,
model_title ,
parent_part_number,
release_status,
description
FROM EMP1 WHERE FLAG=''N'''
)
please help me
- Thanks
ravi sankar gvsIs it because you are specifying [productcenter] instead of [intersql] as
your linked server name?
Raj Moloye
"Nishanth" <cvnishanth@.hotmail.com> wrote in message
news:uha5aF2aFHA.1456@.TK2MSFTNGP15.phx.gbl...
> hi,
> i am trying to connect to oracle 9i using sqlserver 2000 linked server.
> here the linked server name is intersql. when i put this statement in
> begin
> and end transaction block and try to execute it is giving the following
> error
> Server: Msg 7391, Level 16, State 1, Procedure
> ihmtproc_emp_assmbly_interface, Line 93
> The operation could not be performed because the OLE DB provider 'MSDAORA'
> was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8004d01b].
> INSERT INTO eemp_assembly_interface
> (
> [drawing_file_nm],
> [part_number_txt],
> [drawing_status_txt],
> [drawing_revision_txt],
> [model_file_nm],
> [model_title_txt],
> [parent_part_number_txt],
> [release_status_txt],
> [emp_desc],
> [created_by_user_id],
> [created_on_dt]
> )
> SELECT drawing_filename,
> part_number,
> drawing_status,
> drawing_revision,
> model_filename,
> model_title ,
> parent_part_number,
> release_status,
> description,
> 999999999 as created_by_user_id,
> GETDATE() as created_on_dt
> FROM OPENQUERY(productcenter,
> 'SELECT
> drawing_filename,
> part_number,
> drawing_status,
> drawing_revision,
> model_filename,
> model_title ,
> parent_part_number,
> release_status,
> description
> FROM EMP1 WHERE FLAG=''N'''
> )
> please help me
> - Thanks
> ravi sankar gvs
>
i am trying to connect to oracle 9i using sqlserver 2000 linked server.
here the linked server name is intersql. when i put this statement in begin
and end transaction block and try to execute it is giving the following
error
Server: Msg 7391, Level 16, State 1, Procedure
ihmtproc_emp_assmbly_interface, Line 93
The operation could not be performed because the OLE DB provider 'MSDAORA'
was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
INSERT INTO eemp_assembly_interface
(
[drawing_file_nm],
[part_number_txt],
[drawing_status_txt],
[drawing_revision_txt],
[model_file_nm],
[model_title_txt],
[parent_part_number_txt],
[release_status_txt],
[emp_desc],
[created_by_user_id],
[created_on_dt]
)
SELECT drawing_filename,
part_number,
drawing_status,
drawing_revision,
model_filename,
model_title ,
parent_part_number,
release_status,
description,
999999999 as created_by_user_id,
GETDATE() as created_on_dt
FROM OPENQUERY(productcenter,
'SELECT
drawing_filename,
part_number,
drawing_status,
drawing_revision,
model_filename,
model_title ,
parent_part_number,
release_status,
description
FROM EMP1 WHERE FLAG=''N'''
)
please help me
- Thanks
ravi sankar gvsIs it because you are specifying [productcenter] instead of [intersql] as
your linked server name?
Raj Moloye
"Nishanth" <cvnishanth@.hotmail.com> wrote in message
news:uha5aF2aFHA.1456@.TK2MSFTNGP15.phx.gbl...
> hi,
> i am trying to connect to oracle 9i using sqlserver 2000 linked server.
> here the linked server name is intersql. when i put this statement in
> begin
> and end transaction block and try to execute it is giving the following
> error
> Server: Msg 7391, Level 16, State 1, Procedure
> ihmtproc_emp_assmbly_interface, Line 93
> The operation could not be performed because the OLE DB provider 'MSDAORA'
> was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8004d01b].
> INSERT INTO eemp_assembly_interface
> (
> [drawing_file_nm],
> [part_number_txt],
> [drawing_status_txt],
> [drawing_revision_txt],
> [model_file_nm],
> [model_title_txt],
> [parent_part_number_txt],
> [release_status_txt],
> [emp_desc],
> [created_by_user_id],
> [created_on_dt]
> )
> SELECT drawing_filename,
> part_number,
> drawing_status,
> drawing_revision,
> model_filename,
> model_title ,
> parent_part_number,
> release_status,
> description,
> 999999999 as created_by_user_id,
> GETDATE() as created_on_dt
> FROM OPENQUERY(productcenter,
> 'SELECT
> drawing_filename,
> part_number,
> drawing_status,
> drawing_revision,
> model_filename,
> model_title ,
> parent_part_number,
> release_status,
> description
> FROM EMP1 WHERE FLAG=''N'''
> )
> please help me
> - Thanks
> ravi sankar gvs
>
Subscribe to:
Posts (Atom)