Connect to Oracle database.

Hi!

Recently I started to use Reporting Services for a project I am working on. I have a set of Oracle stored procedures that I have to use. All of them return the data I need in output variables.

Any idea about how to use this kind of procedures with Reporting Services.

Thanks in advance for your help!First, make sure that you are running at least RS 2000 with SP1 installed on report designer and report server.

Check the following article for general information about how to connect to Oracle from RS: http://support.microsoft.com/default.aspx?scid=kb;en-us;834305

Choose "Oracle" as data source type which will give you the managed provider for Oracle. Also make sure that you use the text-based generic query designer (2 panes !) instead of the visual query designer (4 panes) - you can switch between them through an icon on the toolbar in the data view of report designer.

In addition, how do you return the data from your stored procedure? Note: only an out ref cursor is supported (but not Output parameters!). Please follow the guidelines in the following article on MSDN (scroll down to the section where it talks about "Oracle REF CURSORs") on how to design the Oracle stored procedure:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp

To use a stored procedure with regular out parameters, you should either remove the parameter (if it is possible) or write a little wrapper around the original stored procedure which checks the result of the out parameter and just returns the out ref cursor but no out parameter.

Finally, in the generic query designer, just specify the name of the stored procedure without arguments and the parameters should get detected automatically.

There also have been several discussion threads about Oracle stored procedures on the following newsgroup:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs
You may want to search for these in case you are running into issues.
-- Robert|||

Hello Robert,

I have the problem to connect Oracle data for my Report designer.

1st I used ODBC, it does not work, and my reference book "MS SQL server 2000 Reporting service step by step" tells me that I must select Oracle data provide instead of the generic ODBC.

I created a new data source and after I enter the userId/pswd and click "Test Connection" button, it is OK. But when I click "run" icon to try, it gives me the following error messages:

An error occurred while executing the query, ORA-00936:Missing expression

Additiona information:

ORA-00936:missing expression(system.Data.OracleClient)

My version of reporting service is 2005. The setting of "Connection Properties" as follows:

Data Source: Oracle (OracleClient) Server name: creonpra Log On to the database: UserId/Pswd= qmprodviewer/*********

I have very less knowledge about Oracle. I do not know Oracle Client software's coverage. I checked my program and only find Oracle 8i of "Net8 Assistant", "Net8 Config Assistant" and Oracle ODBC test" on my desktop PC.

Please help me to resolve the problem

|||

Have you tried executing the stored procedure from your report designer? It may be that you are able to connect, but erroring out on something specific with the stored procedure.

I'd execute it from the Data tab and check if it returns data first and rule out that the stored procedure works or not.

Connect to ODBC DB from SQL Server SPROC

Hello,
I have taken on a project where I'm wondering if I'm in a little over my
head. Using SQL Mail I need to send out e-mails every hour for orders that
have shipped. There are two databases. One is SQL Server and the other is
only accessible via ODBC (I'm not even sure what type of database it is yet,
but it's not SQL Server). The SQL Server database contains the basic order
information and the other database contains the order status.
I have the queries I need, but how do I connect via ODBC to the other
database from SQL Server? I know (very) little about SQL Mail. Do I set it up
as a job under SQL Server Agent jobs so it will run every hour? Can someone
give me a push in the right direction?
Thank you very much for any help, I really appreciate it.
Thanks,
Nick
Hello,
I think you can perform the following steps:
1. Run the query in SQL query analyzer(QA) to make sure it works fine. You
can create a stored procedure.
2. Configure the SQL mail.
3. Run xp_sendmail in SQL QA to send the query results via email.
4. Create a job in SQL agent so it will run every hour.
Generally we can create a linked server to access other data source.
However, it denpends on what database you use. If it is a third party
database, I suggest that you contact the third-party support to consult
with them abouht the issue.
The following information is for your reference:
SQL Server E-mail
http://msdn.microsoft.com/library/de...us/dnovba01/ht
ml/SQLServerE-mail.asp
INF: How to Configure SQL Mail
http://support.microsoft.com/kb/263556
Support WebCast: Troubleshooting Microsoft SQL Mail
http://support.microsoft.com/kb/324689
xp_sendmail
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
xp_aa-sz_6hbg.asp
sp_addlinkedserver
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
sp_adda_8gqa.asp
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Connect to ODBC DB from SQL Server SPROC

Hello,
I have taken on a project where I'm wondering if I'm in a little over my
head. Using SQL Mail I need to send out e-mails every hour for orders that
have shipped. There are two databases. One is SQL Server and the other is
only accessible via ODBC (I'm not even sure what type of database it is yet,
but it's not SQL Server). The SQL Server database contains the basic order
information and the other database contains the order status.
I have the queries I need, but how do I connect via ODBC to the other
database from SQL Server? I know (very) little about SQL Mail. Do I set it u
p
as a job under SQL Server Agent jobs so it will run every hour? Can someone
give me a push in the right direction?
Thank you very much for any help, I really appreciate it.
Thanks,
NickHello,
I think you can perform the following steps:
1. Run the query in SQL query analyzer(QA) to make sure it works fine. You
can create a stored procedure.
2. Configure the SQL mail.
3. Run xp_sendmail in SQL QA to send the query results via email.
4. Create a job in SQL agent so it will run every hour.
Generally we can create a linked server to access other data source.
However, it denpends on what database you use. If it is a third party
database, I suggest that you contact the third-party support to consult
with them abouht the issue.
The following information is for your reference:
SQL Server E-mail
http://msdn.microsoft.com/library/d...-us/dnovba01/ht
ml/SQLServerE-mail.asp
INF: How to Configure SQL Mail
http://support.microsoft.com/kb/263556
Support WebCast: Troubleshooting Microsoft SQL Mail
http://support.microsoft.com/kb/324689
xp_sendmail
http://msdn.microsoft.com/library/d...-us/tsqlref/ts_
xp_aa-sz_6hbg.asp
sp_addlinkedserver
http://msdn.microsoft.com/library/d...-us/tsqlref/ts_
sp_adda_8gqa.asp
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Connect to an SQL 2005 server through proxy

Ok, here is the situation...
I have created an Access Project application which connects to our SQL
Server.
SQL server is not behind a proxy, it listens to the default port
Most of our users are outside the LAN and thus they connect through the
internet.
One of our users works in a university environment which controls the
outbound connections through proxy.
I want this user to be able to connect to the SQL server through this
proxy (which i cant control ofcourse)
Is this possible? What settings are required in the proxy (no idea if
it is an MS proxy or not). Does the user has to change anything inside
the MS access connection dialog?
Plz help me on this
If it is not possible please provide some alternatives...
Thanks in advance
Noone knows anything about that? Plz help me on this
On Oct 21, 4:45 pm, IoannisKirmitzog...@.gmail.com wrote:
> Ok, here is the situation...
> I have created an Access Project application which connects to our SQL
> Server.
> SQL server is not behind a proxy, it listens to the default port
> Most of our users are outside the LAN and thus they connect through the
> internet.
> One of our users works in a university environment which controls the
> outbound connections through proxy.
> I want this user to be able to connect to the SQL server through this
> proxy (which i cant control ofcourse)
> Is this possible? What settings are required in the proxy (no idea if
> it is an MS proxy or not). Does the user has to change anything inside
> the MS access connection dialog?
> Plz help me on this
> If it is not possible please provide some alternatives...
> Thanks in advance
sqlsql

Connect to an SQL 2005 server through proxy

Ok, here is the situation...
I have created an Access Project application which connects to our SQL
Server.
SQL server is not behind a proxy, it listens to the default port
Most of our users are outside the LAN and thus they connect through the
internet.
One of our users works in a university environment which controls the
outbound connections through proxy.
I want this user to be able to connect to the SQL server through this
proxy (which i cant control ofcourse)
Is this possible? What settings are required in the proxy (no idea if
it is an MS proxy or not). Does the user has to change anything inside
the MS access connection dialog'
Plz help me on this
If it is not possible please provide some alternatives...
Thanks in advanceNoone knows anything about that? Plz help me on this
On Oct 21, 4:45 pm, IoannisKirmitzog...@.gmail.com wrote:
> Ok, here is the situation...
> I have created an Access Project application which connects to our SQL
> Server.
> SQL server is not behind a proxy, it listens to the default port
> Most of our users are outside the LAN and thus they connect through the
> internet.
> One of our users works in a university environment which controls the
> outbound connections through proxy.
> I want this user to be able to connect to the SQL server through this
> proxy (which i cant control ofcourse)
> Is this possible? What settings are required in the proxy (no idea if
> it is an MS proxy or not). Does the user has to change anything inside
> the MS access connection dialog'
> Plz help me on this
> If it is not possible please provide some alternatives...
> Thanks in advance

Connect SQLRPS2005 to SQL2k DB?

How do I go about connecting SQLRPS2005 in SPS3.0 integration mode to
a SQL2k DB?
(Actually I want to report from a Project server 2003 instance, which
is based on sql2k.
I have SQLRPS2k5 installed on the sharepoint server and have lots of
green ticks for the installation. I have also installed the SPS3.0
plug-in and cofigured its paramters through SPS central.
Then I create a data source within my SPS report libary with:
Data source type: Microsoft SQL Server
connection string: data source=act01projsvr01;initial
catalog=ProjectServer
Credentials: windows integrated
However, when I try to create a report builder model using this data
source I get:
Cannot create a connection to data source 'EPM Pilot.rsds'.
(rsErrorOpeningConnection)
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
So obviously the current users windows credentials are not getting
passed through to the remote SQL server correctly?
Any ideas?It looks like double hop issue. Real (and hard) solution is to use Kerberos.
As workaround, you can try to use stored credentials instead of Windows
Integrated.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"scubaal" <al@.blakes.net> wrote in message
news:1173848679.463141.29360@.n59g2000hsh.googlegroups.com...
> How do I go about connecting SQLRPS2005 in SPS3.0 integration mode to
> a SQL2k DB?
> (Actually I want to report from a Project server 2003 instance, which
> is based on sql2k.
> I have SQLRPS2k5 installed on the sharepoint server and have lots of
> green ticks for the installation. I have also installed the SPS3.0
> plug-in and cofigured its paramters through SPS central.
> Then I create a data source within my SPS report libary with:
> Data source type: Microsoft SQL Server
> connection string: data source=act01projsvr01;initial
> catalog=ProjectServer
> Credentials: windows integrated
> However, when I try to create a report builder model using this data
> source I get:
> Cannot create a connection to data source 'EPM Pilot.rsds'.
> (rsErrorOpeningConnection)
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> So obviously the current users windows credentials are not getting
> passed through to the remote SQL server correctly?
> Any ideas?
>

Confused with primary keys and foreign keys

Hi, I am currently doing a database project for my company. The database contains 5 tables inside, let's say A,B,C,D,E. The primary key of table A is referenced by all other tables. If an instance is created in other tables except in table A, it means that the foreign keys are not null. However, if an instance is created in table A, it doesn't necessarily mean that the primary key of that particular instance must be referenced by another instance in other tables. In other words, the foreign keys in other tables have null value.

Now, the question is how can I display this primary key in table A together with primary keys of other tables since some of them may not even exist yet? By displaying all the primary keys, I can therefore check that the primary keys in other tables still don't exist and with this, I can input a value to it.

P.S. I am connecting the database with VB 2005 Express Edition and I always get blank value when I tried to retrieve the value.

Thank you very much for the help

Foreign Key:

Is the advanced check constraint which will check the set of values from the another table. The another/reference table is called parent/master table & the current table is called child/detailed table. The Master table which holds the set of value should be not null unique. (you can’t have the more than one Parent for single Child). But your child table column can hold NULL value (which is not yet born)

Primary key:

Is the table levelconstraint which will ensure your table columns have a unique & not null values.

Display the Parent & child values (which is not yet born):

You have to use OUTER JOIN to achieve this. If you post more information we can provide the rite information to you.

|||

The basic problem is that IF there is not a row in the Child table (table with FK), or there is a row, but its FK value is NULL, there is no way to 'connect' the row to the Parent table (table with PK).

It is possible to 'simulate' the missing rows by using a LEFT JOIN, and referring to the Parent table first.

Try out this sample code to see how to combine tables where both tables have matching values, and also where one table is missing a FK value from the other table.

Code Snippet

--***********************************
--Problem: Demonstrate PK-FK JOINS

CREATE TABLE Parent
( RowID int IDENTITY PRIMARY KEY,
MyValue varchar(20)
)
GO

CREATE TABLE Child
( RowID int IDENTITY PRIMARY KEY,
ParentID int REFERENCES Parent(Rowid),
MyValue varchar(20)
)
GO

SET NOCOUNT ON

INSERT INTO Parent VALUES ( 'Parent1' )
INSERT INTO Parent VALUES ( 'Parent2' )
INSERT INTO Parent VALUES ( 'Parent3' )
INSERT INTO CHILD VALUES ( 2, 'Child1' )
INSERT INTO CHILD VALUES ( 3, 'Child2' )
INSERT INTO CHILD VALUES ( 2, 'Child3' )
INSERT INTO CHILD VALUES ( NULL, 'Child4' )

-- To find ONLY Parents AND their Children
SELECT
ParentID = p.RowID,
ParentValue = p.MyValue,
ChildID = c.RowID,
ChildValue = c.MyValue
FROM Parent p
JOIN Child c
ON p.RowID = c.ParentID

-- To find All Parents and Children (if Any)
SELECT
ParentID = p.RowID,
ParentValue = p.MyValue,
ChildID = c.RowID,
ChildValue = c.MyValue
FROM Parent p
LEFT JOIN Child c
ON p.RowID = c.ParentID

-- To find Parents without Children
SELECT
ParentID = p.RowID,
ParentValue = p.MyValue,
ChildID = c.RowID,
ChildValue = c.MyValue
FROM Parent p
LEFT JOIN Child c
ON p.RowID = c.ParentID
WHERE c.ParentID IS NULL

-- To find Children without Parents (Orphans)
SELECT
ParentID = p.RowID,
ParentValue = p.MyValue,
ChildID = c.RowID,
ChildValue = c.MyValue
FROM Parent p
RIGHT JOIN Child c
ON p.RowID = c.ParentID
WHERE c.ParentID IS NULL

DROP TABLE Child
DROP TABLE Parent

|||Thanks for both your suggestions, but I don't have any idea how to perform LEFT JOIN on 5 tables. Can you gimme some idea on this? Thanks..|||

Manivannan.D.Sekaran wrote:

Foreign Key:

Is the advanced check constraint which will check the set of values from the another table. The another/reference table is called parent/master table & the current table is called child/detailed table. The Master table which holds the set of value should be not null unique. (you can’t have the more than one Parent for single Child). But your child table column can hold NULL value (which is not yet born)

Primary key:

Is the table level constraint which will ensure your table columns have a unique & not null values.

Display the Parent & child values (which is not yet born):

You have to use OUTER JOIN to achieve this. If you post more information we can provide the rite information to you.

The child values are actually not null, they are only not yet born. However, I need to display both the parent table which already has a child and which has no child at all. I will try to use OUTER JOIN in the code and see how the result will be. Thanks a lot.

Configuring users

Hi,
I am connecting to sql server 2000 from an Access 2000 project. I want to
have certain users edit a stored procedure. I can give them execute
permission but this doesn't allow them edit. I don't want to give them owner
permissions on the sp.
How do I allow them edit this sp only?
Thanks
Tom
Currently only the owner, the DB owner or someone with system administration
permissions can alter an sp...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tom Swanick" <tom.swanick@.activant.com> wrote in message
news:OFRuinn5EHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am connecting to sql server 2000 from an Access 2000 project. I want to
> have certain users edit a stored procedure. I can give them execute
> permission but this doesn't allow them edit. I don't want to give them
owner
> permissions on the sp.
> How do I allow them edit this sp only?
> Thanks
> Tom
>
|||Or db_ddladmin; however, this will also give them edit permissions on all
objects in the database.
Sincerely,
Anthony Thomas

"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eIaw0Jp5EHA.1408@.TK2MSFTNGP10.phx.gbl...
Currently only the owner, the DB owner or someone with system administration
permissions can alter an sp...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tom Swanick" <tom.swanick@.activant.com> wrote in message
news:OFRuinn5EHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am connecting to sql server 2000 from an Access 2000 project. I want to
> have certain users edit a stored procedure. I can give them execute
> permission but this doesn't allow them edit. I don't want to give them
owner
> permissions on the sp.
> How do I allow them edit this sp only?
> Thanks
> Tom
>

Configuring users

Hi,
I am connecting to sql server 2000 from an Access 2000 project. I want to
have certain users edit a stored procedure. I can give them execute
permission but this doesn't allow them edit. I don't want to give them owner
permissions on the sp.
How do I allow them edit this sp only?
Thanks
TomCurrently only the owner, the DB owner or someone with system administration
permissions can alter an sp...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tom Swanick" <tom.swanick@.activant.com> wrote in message
news:OFRuinn5EHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am connecting to sql server 2000 from an Access 2000 project. I want to
> have certain users edit a stored procedure. I can give them execute
> permission but this doesn't allow them edit. I don't want to give them
owner
> permissions on the sp.
> How do I allow them edit this sp only?
> Thanks
> Tom
>|||Or db_ddladmin; however, this will also give them edit permissions on all
objects in the database.
Sincerely,
Anthony Thomas
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eIaw0Jp5EHA.1408@.TK2MSFTNGP10.phx.gbl...
Currently only the owner, the DB owner or someone with system administration
permissions can alter an sp...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tom Swanick" <tom.swanick@.activant.com> wrote in message
news:OFRuinn5EHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am connecting to sql server 2000 from an Access 2000 project. I want to
> have certain users edit a stored procedure. I can give them execute
> permission but this doesn't allow them edit. I don't want to give them
owner
> permissions on the sp.
> How do I allow them edit this sp only?
> Thanks
> Tom
>