Link to Oracle database

Hi all,
I have installed Oracle client tool on the SQL server and have checked the tns file. Everythings look fine, even from the server I can run SQL Plus and connect to the Oracle database. When I create a link and try to refresh tables under linked serve, I ge
t this error: Error 7399:OLE DB provider 'MSDAORA' reported an error. I reinstalled MDAC 2.8, but I get the same error.
Any help would be appreciated.
Shahri
That's a generic error. Try turning on trace flag 7300 or
us SQL Profiler to capture the OLEDB Errors event. This
should give you to get more information about the error.
Additionally, you'll want to check the following article:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in
SQL Server
http://support.microsoft.com/?id=280106
-Sue
On Wed, 16 Jun 2004 08:43:37 -0700, Shahri
<Shahri@.discussions.microsoft.com> wrote:

>Hi all,
>I have installed Oracle client tool on the SQL server and have checked the tns file. Everythings look fine, even from the server I can run SQL Plus and connect to the Oracle database. When I create a link and try to refresh tables under linked serve, I g
et this error: Error 7399:OLE DB provider 'MSDAORA' reported an error. I reinstalled MDAC 2.8, but I get the same error.
>Any help would be appreciated.
>Shahri

Link to Oracle database

Hi all,
I have installed Oracle client tool on the SQL server and have checked the t
ns file. Everythings look fine, even from the server I can run SQL Plus and
connect to the Oracle database. When I create a link and try to refresh tabl
es under linked serve, I ge
t this error: Error 7399:OLE DB provider 'MSDAORA' reported an error. I rein
stalled MDAC 2.8, but I get the same error.
Any help would be appreciated.
ShahriThat's a generic error. Try turning on trace flag 7300 or
us SQL Profiler to capture the OLEDB Errors event. This
should give you to get more information about the error.
Additionally, you'll want to check the following article:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in
SQL Server
http://support.microsoft.com/?id=280106
-Sue
On Wed, 16 Jun 2004 08:43:37 -0700, Shahri
<Shahri@.discussions.microsoft.com> wrote:

>Hi all,
>I have installed Oracle client tool on the SQL server and have checked the tns file
. Everythings look fine, even from the server I can run SQL Plus and connect to the
Oracle database. When I create a link and try to refresh tables under linked serve,
I g
et this error: Error 7399:OLE DB provider 'MSDAORA' reported an error. I reinstalled MDAC 2.
8, but I get the same error.
>Any help would be appreciated.
>Shahri

Link to Oracel database

Hi all
I am running SQL Server 2000 Enterprise Edition and I want to create a link server to an oracle database. I have installed Oracle client on the server, but when try to test the connection using Oracle Net Configuration Assistant, I'm getting error: "TNS:listener could not resolve SERVICE_NAME given in connect description"
I have read article 280106 (troubleshooting) that didn't help
I appreciate any guidance
RayThe Net Assistant doesn't always generate correct
tnsnames.ora and listener.ora files. You most likely have
something missing in one of these files.
Make sure the Oracle home is specified correctly and that
you have a SID_LIST_LISTENER entry which specifies the sid
that the listener is listening for.
-Sue
On Wed, 9 Jun 2004 12:46:10 -0700, Ray
<anonymous@.discussions.microsoft.com> wrote:
>Hi all,
>I am running SQL Server 2000 Enterprise Edition and I want to create a link server to an oracle database. I have installed Oracle client on the server, but when try to test the connection using Oracle Net Configuration Assistant, I'm getting error: "TNS:listener could not resolve SERVICE_NAME given in connect description".
>I have read article 280106 (troubleshooting) that didn't help.
>I appreciate any guidance.
>Ray

Link to Oracel database

Hi all,
I am running SQL Server 2000 Enterprise Edition and I want to create a link server to an oracle database. I have installed Oracle client on the server, but when try to test the connection using Oracle Net Configuration Assistant, I'm getting error: "TNS:l
istener could not resolve SERVICE_NAME given in connect description".
I have read article 280106 (troubleshooting) that didn't help.
I appreciate any guidance.
Ray
The Net Assistant doesn't always generate correct
tnsnames.ora and listener.ora files. You most likely have
something missing in one of these files.
Make sure the Oracle home is specified correctly and that
you have a SID_LIST_LISTENER entry which specifies the sid
that the listener is listening for.
-Sue
On Wed, 9 Jun 2004 12:46:10 -0700, Ray
<anonymous@.discussions.microsoft.com> wrote:

>Hi all,
>I am running SQL Server 2000 Enterprise Edition and I want to create a link server to an oracle database. I have installed Oracle client on the server, but when try to test the connection using Oracle Net Configuration Assistant, I'm getting error: "TNS:
listener could not resolve SERVICE_NAME given in connect description".
>I have read article 280106 (troubleshooting) that didn't help.
>I appreciate any guidance.
>Ray

Link to Oracel database

Hi all,
I am running SQL Server 2000 Enterprise Edition and I want to create a link
server to an oracle database. I have installed Oracle client on the server,
but when try to test the connection using Oracle Net Configuration Assistant
, I'm getting error: "TNS:l
istener could not resolve SERVICE_NAME given in connect description".
I have read article 280106 (troubleshooting) that didn't help.
I appreciate any guidance.
RayThe Net Assistant doesn't always generate correct
tnsnames.ora and listener.ora files. You most likely have
something missing in one of these files.
Make sure the Oracle home is specified correctly and that
you have a SID_LIST_LISTENER entry which specifies the sid
that the listener is listening for.
-Sue
On Wed, 9 Jun 2004 12:46:10 -0700, Ray
<anonymous@.discussions.microsoft.com> wrote:

>Hi all,
>I am running SQL Server 2000 Enterprise Edition and I want to create a link server
to an oracle database. I have installed Oracle client on the server, but when try to
test the connection using Oracle Net Configuration Assistant, I'm getting error: "T
NS:
listener could not resolve SERVICE_NAME given in connect description".
>I have read article 280106 (troubleshooting) that didn't help.
>I appreciate any guidance.
>Ray

limit size of image column

I'm trying to limit the size of images that can be inserted into an Image
type column. The client side tools are not letting me do this so I thought I
could do it in a trigger or constraint in the backend. So I discovered that
I can't use Image datatypes in constraints. And also discovering that I
can't use it the way I thought I could in a trigger. HEre's what I was goign
to do:
IF UPDATE(ItemPic)
BEGIN
IF (SELECT DATALENGTH(ItemPic) AS ItemPicSize FROM INSERTED) > 100000
BEGIN
ROLLBACK TRAN
SET @.sMsg = 'Item Picture cannot exceed 100Kb.'
RAISERROR (@.sMsg, 16, 1)
RETURN
END
END
Which of course doesn't work because I get the error "Cannot use text,
ntext, or image columns in the 'inserted' and 'deleted' tables."
Is there a good way to do this? I won't be allowing very large images so
maybe a different data type would work?
Thanks,
KeithHow are they getting the image file to the database server? Presumably,
they are using a web app or a windows app (I don't know what "client side
tools" are, and why they don't let you do something), in which case surely
there is a place along the pipeline that you can validate the size BEFORE
you run the INSERT statement.
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:OUJSYHTaGHA.3328@.TK2MSFTNGP02.phx.gbl...
> I'm trying to limit the size of images that can be inserted into an Image
> type column. The client side tools are not letting me do this so I thought
> I
> could do it in a trigger or constraint in the backend. So I discovered
> that
> I can't use Image datatypes in constraints. And also discovering that I
> can't use it the way I thought I could in a trigger. HEre's what I was
> goign
> to do:
> IF UPDATE(ItemPic)
> BEGIN
> IF (SELECT DATALENGTH(ItemPic) AS ItemPicSize FROM INSERTED) > 100000
> BEGIN
> ROLLBACK TRAN
> SET @.sMsg = 'Item Picture cannot exceed 100Kb.'
> RAISERROR (@.sMsg, 16, 1)
> RETURN
> END
> END
> Which of course doesn't work because I get the error "Cannot use text,
> ntext, or image columns in the 'inserted' and 'deleted' tables."
> Is there a good way to do this? I won't be allowing very large images so
> maybe a different data type would work?
> Thanks,
> Keith
>|||Read more about Image column
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx[/url
]
Madhivanan|||I've already spoken to the developers of the client side tools I'm using
(3rd party windows app dev tools). I'm using one of their image controls
which has no ability to test or limit the size before the fact.
The only other part of the pipeline is in ado. I'd have to do it in the
OnBeforePost event of the ado dataset but accordign to the Delphi ado help,
there's no way to determine the data size of a blob control's data. The
following is from the help file (note the line "this does not work for BLOB
fields"):
with Field1 do
begin
if not IsBlob { this does not work for BLOB fields }
begin
{ Allocate space }
GetMem(MyBuffer, DataSize);
try
if not GetData(MyBuffer) then
MessageDlg(DisplayName + ' is NULL', mtInformation, [mbOK], 0)
else
{ Do something with the data };
finally
{ Free the space }
FreeMem(MyBuffer, DataSize);
end;
end;
end;
Which is why I came here as a last resort because I agree, the situation
should be handled before it ever gets to the db.
Keith
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OcB$30TaGHA.1200@.TK2MSFTNGP03.phx.gbl...
How are they getting the image file to the database server? Presumably,
they are using a web app or a windows app (I don't know what "client side
tools" are, and why they don't let you do something), in which case surely
there is a place along the pipeline that you can validate the size BEFORE
you run the INSERT statement.|||Try using an INSTEAD OF tigger. In pseudo code:
if no images larger than 10000 exist
insert rows
else
rollback
raiserror
ML
http://milambda.blogspot.com/|||That was actually the very first sql thing I tried. But I have foreign keys
with cascade updates/deletes between this and other tables that are not
allowing me to hae an INSTEAD OF UPDATE, INSERT trigger on this table.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:2FFA09F9-2D72-4AE9-8FE2-BC1FE6D42A76@.microsoft.com...
Try using an INSTEAD OF tigger. In pseudo code:
if no images larger than 10000 exist
insert rows
else
rollback
raiserror
ML
http://milambda.blogspot.com/|||Well, I think the answer for me at this point is to call a stored procedure
from the trigger and test teh size that way. That seems to be working sort
of. There are a couple of glitches but those are in a new post. I'm all set
in this one. Thanks for the answers folks. - keith|||Another approach may be to get the ADO call from Delphi to call a stored
procedure instead. Then insert into that stored procedure the code to
validate the size of the file.
If that's possible, then surely it would be possible to check the file size
before even making the call to a stored procedure. You know, what's the
point sending a 10Mb Graphic to the SQL Server cloging up the network, only
to have SQL say know and through the whole lot away.
Regards
Colin Dawson
www.cjdawson.com
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:es9MQtUaGHA.428@.TK2MSFTNGP02.phx.gbl...
> Well, I think the answer for me at this point is to call a stored
> procedure
> from the trigger and test teh size that way. That seems to be working sort
> of. There are a couple of glitches but those are in a new post. I'm all
> set
> in this one. Thanks for the answers folks. - keith
>