Connect to Oracle stored procedure from SQL Server stored procedure...and vice versa.

I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.

How do I do that? Articles, code samples, etc?

Oops, the actual requirement is just to query Oracle tables from SQL Server and to query SQL Server tables from Oracle.

Setting up a linked server in SQL Server accomplishes the first and is well documented in Books Online.

The second involves using Oracle Heterogenous Services or Transparent Gateway. I have not found very good documentation about how to use those.

Connect to MSDE file on ISP Server

Are there any articles about connecting a MDB or ADP front-end to a MSDE
database that is located on our ISP's server? When I try to Google the
subject, I get a bazillion hits but non really apply to WAN connections.
Any help or pointers would be appreciated
Paul P
There is nothing special to do when connecting to MSDE over the WAN, all you
have to do is to use the connection parameters given to you by your ISP; the
only condition being that the port 1433 has not been blocked. (Because of
security concerns, more and more ISP are blocking the port 1433 at the
firewall level.)
If you want to have more details for the connection string:
http://www.able-consulting.com/ADO_Conn.htm
http://www.connectionstrings.com/
I suggest that you ask your ISP about this.
S. L.
"Paul Proefrock" <proefrock@.sbcglobal.net> wrote in message
news:OXISVonAFHA.904@.TK2MSFTNGP12.phx.gbl...
> Are there any articles about connecting a MDB or ADP front-end to a MSDE
> database that is located on our ISP's server? When I try to Google the
> subject, I get a bazillion hits but non really apply to WAN connections.
> Any help or pointers would be appreciated
> Paul P
>
|||Hi Sylvain,
I can't connect to MSDE on my ISP also. I just called them (my ISP) and
they informed me that port 1433 was not blocked. I know that the TCP/IP is
enabled on the MSDE and sql server logins are allowed. When I try to
register the msde from my local enterprise manager I can't. Is there
anything else that may cause it to fail that you are aware of? Oh - is there
a way to remotely check to see if 1433 is blocked just in case I was given
bad info?
Thanks in advance, Mike
"Sylvain Lafontaine" wrote:

> There is nothing special to do when connecting to MSDE over the WAN, all you
> have to do is to use the connection parameters given to you by your ISP; the
> only condition being that the port 1433 has not been blocked. (Because of
> security concerns, more and more ISP are blocking the port 1433 at the
> firewall level.)
> If you want to have more details for the connection string:
> http://www.able-consulting.com/ADO_Conn.htm
> http://www.connectionstrings.com/
> I suggest that you ask your ISP about this.
> S. L.
> "Paul Proefrock" <proefrock@.sbcglobal.net> wrote in message
> news:OXISVonAFHA.904@.TK2MSFTNGP12.phx.gbl...
>
>
|||You may try to add the port 1433 after the TCP/IP address (separated by a
comma) and also specify that you want to use the TCP/IP protocol by
explicitely mentionning the DBMSSOCN network library; something like:
Provider=sqloledb;Data Source=190.190.200.100,1433;Network
Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd
The Network library was mandatory on Win98, if I remember correctly, but
shouldn't be necessary with WinXP. Also, make sure that you have the latest
service pack applied, as well as MDAC 2.8 (won't hurt). Don't forget to
check for a firewall on your own side.
You can try to ping the remote address on the port 1433. Often, the address
of the SQL-Server is not the same as the address of the web site; because it
is located on another machine than the web server.
Beside that, I don't know.
S. L.
"Michael McTaggart" <Michael McTaggart@.discussions.microsoft.com> wrote in
message news:BC06CED5-780A-4B21-8D40-1C2332D796F6@.microsoft.com...[vbcol=seagreen]
> Hi Sylvain,
> I can't connect to MSDE on my ISP also. I just called them (my ISP) and
> they informed me that port 1433 was not blocked. I know that the TCP/IP
> is
> enabled on the MSDE and sql server logins are allowed. When I try to
> register the msde from my local enterprise manager I can't. Is there
> anything else that may cause it to fail that you are aware of? Oh - is
> there
> a way to remotely check to see if 1433 is blocked just in case I was given
> bad info?
> Thanks in advance, Mike
>
> "Sylvain Lafontaine" wrote:

Confused About Permission

I read a few articles on best SQL practices and they kept coming back to using a Least Privileged Account. So I did so and gave that account read only permissions. The articles also said to do updates use Stored Procedures - so I created stored procedures for updating/deleting data.

So here's my problem - I connect to the database using the Least Privileged Account, I use the Stored Procedures, but .NET keeps saying I lack permissions. If I GRANT the Least Privileged Account UPDATE/DELETE permission on the table, the Stored Procedures run perfectly. But isn't that EXACTLY what I'm trying to avoid?

My greatest concern is someone hacks my website and using the Least Privileged Account, they delete all my data using that account. So I don't want to give the Least Privileged Account the Update/Delete privileges.

Thanks a MILLION in advance!

Do you grant EXECUTE permission on the stored procedures to the account?

Once that is done you do not need UPDATE/DELETE on the tables for the account.

|||I granted execute permissions to the stored procedures, but it still fails when I check OFF the Update permission on the table (and still works when I check ON the Update permission). Any other ideas?|||

I figured out my problem (thanks to TATWORTH). I was creating the SP's while logging in as the least privileged account, so therefore the SP was [LeastPrivilegedAccount].[StoredProcedureName] - this required the Least privileged user to have the permissions. Now I created the SP's as [dbo].[StoredProcedureName] and granted the Least privileged user EXECUTE permissions.

It works perfectly! Thanks a MILLION!!1

Conflicts during replication

Hello,
I read in articles that the SQL Server Reconciler solves conflicts which can
occur during the replication.
The kind of conflicts are:
1) If more subscriber want to modify the same row on the publisher database
at the same time.
2) If a certain row is changed at the subscriber database as well as on the
publisher database - and so the question is which database (subscriber or
publisher) will be updated with the information of the other.
Can anybody explain me how such conflict solution could be look like for my
above mentioned scenarios?
thx
mat
Conflicts fall into three categories
1) primary key collisions, where the publisher and subscriber enter a row
with the same value for the primary key between merge agent runs
2) a row is updated on the publisher or subscriber and deleted on the
subscriber or publisher. When the merge agent runs it attempts to update a
row which no longer exists.
3) you update the same row on both sides of a replication solution and do
not have column level tracking enabled for your publication.
In all three conflict types these winning and loosing changes will be logged
in conflict tables and you can view them using the conflict viewer.
I hope this answers your question. I am not really able to understand your
question.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mathon" <mathon@.discussions.microsoft.com> wrote in message
news:7CCCFD74-4B03-423A-96D9-835A5B604230@.microsoft.com...
> Hello,
> I read in articles that the SQL Server Reconciler solves conflicts which
can
> occur during the replication.
> The kind of conflicts are:
> 1) If more subscriber want to modify the same row on the publisher
database
> at the same time.
> 2) If a certain row is changed at the subscriber database as well as on
the
> publisher database - and so the question is which database (subscriber or
> publisher) will be updated with the information of the other.
> Can anybody explain me how such conflict solution could be look like for
my
> above mentioned scenarios?
> thx
> mat
|||Hello
That is my problem of understanding. How does the SQL Server Reconciler
solve these three conflicts?
mathon

> 1) primary key collisions, where the publisher and subscriber enter a row
> with the same value for the primary key between merge agent runs
> 2) a row is updated on the publisher or subscriber and deleted on the
> subscriber or publisher. When the merge agent runs it attempts to update a
> row which no longer exists.
> 3) you update the same row on both sides of a replication solution and do
> not have column level tracking enabled for your publication.
|||Unfortunately I do not find any good online resource where it is explained
how the Reconciler solves these conficts...?
thx
mat

Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows 2000

Microsoft has kindly posted articles on how to configure 2005 SSAS for
HTTP (formerly called XMLA) for Windows XP and Windows 2003 here:
http://www.microsoft.com/technet/pro.../httpasws.mspx
http://www.microsoft.com/technet/pro.../httpssas.mspx
However, I have not been able to find documentation to support or
refute support of this feature on Windows 2000. Is it possible to use
the native XMLA/SOAP support of SQL2005 when hosted on a Windows 2000
server?
Thanks!
Sorry, but I think you have to have XP or 2003. the native HTTP/SOAP support
in SQL Server does not work on 2000. I am not sure if SSAS for HTTP does
though. You need to check in an AS specific newsgroup.
Best regards
Michael
<russel.krause@.upsidesoft.com> wrote in message
news:1143500586.771866.101310@.j33g2000cwa.googlegr oups.com...
> Microsoft has kindly posted articles on how to configure 2005 SSAS for
> HTTP (formerly called XMLA) for Windows XP and Windows 2003 here:
> http://www.microsoft.com/technet/pro.../httpasws.mspx
> http://www.microsoft.com/technet/pro.../httpssas.mspx
> However, I have not been able to find documentation to support or
> refute support of this feature on Windows 2000. Is it possible to use
> the native XMLA/SOAP support of SQL2005 when hosted on a Windows 2000
> server?
> Thanks!
>

Configuring an SQL 2005 Cluster on a windows 2003 Active Passive c

Hi All.
I want to deploy an sql 2005 cluster on a Windows 2003 Active/Passive
Cluster.Are there any KB articles avalible that will guide in deploying sql
2005 Cluster.
Appreciate it.
Regards
Khalid
Search the Microsoft site for an article by Allan Hirt
SQL Server 2005 Failover Clustering White Paper SQLServer2005FOC.doc
Khalid wrote:
>Hi All.
>I want to deploy an sql 2005 cluster on a Windows 2003 Active/Passive
>Cluster.Are there any KB articles avalible that will guide in deploying sql
>2005 Cluster.
>Appreciate it.
>Regards