problem in executing package in production environment

hi all!

This is my problem. My package executes fine when i set the connection string with the same database where i execute the query. If i execute with another database connection stirng if fails bacause while executing the pacakge it trys to access the same connection string at design mode.

when i try to execute through cmd prompt by setting \conn <new database connection string> it fails.

Is package configuration is the only solution. how can i change conn string depending on different server?

Any help would be appreciated.

Thanks,

Jas

As far as my knowledge, package configuration is the only one solution.|||

Package configurations will work but you'll need to reference the configuration at runtime. You can also use the /SET switch from dtexec.exe to set a property dynamically at runtime. If you're trying to set a connection string though, you can set it with the /Connection swtich. Try doing this from DtsExecUI.exe first to see if you have better luck then grab the command line from the last page down. Hope this helps!

Brian

|||

i am currently trying to set the variable with the /set and give connection string as /conn but when i do it another environment and change the conn string it does not work. Now thats my problem. Now i am trying through package configuration but that too i have some issues. I don't want to use environment variable and cannot use parent pacakge varibale for this particular pacakge cos it is the main parent pacakge. I want to use registry key but if i give the value other than current user. it does not work. I am trying to use the HKEY_local machine /software / .../ .../ value. How doi set the registry key for this in pacakge configuration. And i also tried the config file. I works well through indirect method only in another environment where i need to set the environment variable to hold the path of the config file.

Is there any suggestions?

Thanks,

JAs

sql

problem in executing package in production environment

hi all!

This is my problem. My package executes fine when i set the connection string with the same database where i execute the query. If i execute with another database connection stirng if fails bacause while executing the pacakge it trys to access the same connection string at design mode.

when i try to execute through cmd prompt by setting \conn <new database connection string> it fails.

Is package configuration is the only solution. how can i change conn string depending on different server?

Any help would be appreciated.

Thanks,

Jas

As far as my knowledge, package configuration is the only one solution.|||

Package configurations will work but you'll need to reference the configuration at runtime. You can also use the /SET switch from dtexec.exe to set a property dynamically at runtime. If you're trying to set a connection string though, you can set it with the /Connection swtich. Try doing this from DtsExecUI.exe first to see if you have better luck then grab the command line from the last page down. Hope this helps!

Brian

|||

i am currently trying to set the variable with the /set and give connection string as /conn but when i do it another environment and change the conn string it does not work. Now thats my problem. Now i am trying through package configuration but that too i have some issues. I don't want to use environment variable and cannot use parent pacakge varibale for this particular pacakge cos it is the main parent pacakge. I want to use registry key but if i give the value other than current user. it does not work. I am trying to use the HKEY_local machine /software / .../ .../ value. How doi set the registry key for this in pacakge configuration. And i also tried the config file. I works well through indirect method only in another environment where i need to set the environment variable to hold the path of the config file.

Is there any suggestions?

Thanks,

JAs

Problem in Context connection of CLR Trigger

Hi,
I'm using June CTP Visual Studio 2005.
I have Created CLR Object. In my CLR Method i have opened a
connection like below
public partial class Triggers
{
//In my CLR Method i have Opened the connection like below
[Microsoft.SqlServer.Server.SqlTrigger(Name="ClrTrigger",
Target="Triplets",Event="FOR INSERT")]
public static void CLRTrigger()
{
Triggers objTriggers = new Triggers();
objTriggers.Connection(true);
..
...
Test();
// Here i'm calling one more method Test();
...
...
objTriggers.Connection(false);
}
In this test method i'm Executing the command.
Public void Test()
{
...
SqlCommand cmd = new SqlCommand();
string cmdText = "select dirname from Docs where id=45";
cmd.CommandText = cmdText;
cmd.Connection = con;
try
{
sdr = cmd.ExecuteReader();
}
catch (Exception exp)
{
WriteLog("R E ::: "+exp.Message);
}
.....
}
//Connection open and Close method
public void Connection(bool status)
{
if (status)
{
con = new SqlConnection("context connection = true");
con.Open();
}
else
{
con.Close();
}
}
}
I'm getting the error of Connection already in use. I have properly
opened the connection in begin of
method and Closed in end of method. Inside the execution of method if i
called some method means then it
can also use the same connection. Then Why its giving this error.
Please anybody knows solution to my
problem let me know to solve it.
Thanks,
Vinoth
vinoth@.gsdindia.comHi
Support for SQL Server 2005:
http://communities.microsoft.com/ne...p=sqlserver2005
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"None" wrote:

> Hi,
> I'm using June CTP Visual Studio 2005.
> I have Created CLR Object. In my CLR Method i have opened a
> connection like below
> public partial class Triggers
> {
> //In my CLR Method i have Opened the connection like below
> [Microsoft.SqlServer.Server.SqlTrigger(Name="ClrTrigger",
> Target="Triplets",Event="FOR INSERT")]
> public static void CLRTrigger()
> {
> Triggers objTriggers = new Triggers();
> objTriggers.Connection(true);
> ...
> ....
> Test();
> // Here i'm calling one more method Test();
> ...
> ...
> objTriggers.Connection(false);
> }
> In this test method i'm Executing the command.
> Public void Test()
> {
> ...
> SqlCommand cmd = new SqlCommand();
> string cmdText = "select dirname from Docs where id=45";
> cmd.CommandText = cmdText;
> cmd.Connection = con;
> try
> {
> sdr = cmd.ExecuteReader();
> }
> catch (Exception exp)
> {
> WriteLog("R E ::: "+exp.Message);
> }
> ......
> }
> //Connection open and Close method
> public void Connection(bool status)
> {
> if (status)
> {
> con = new SqlConnection("context connection = true");
> con.Open();
> }
> else
> {
> con.Close();
> }
> }
> }
> I'm getting the error of Connection already in use. I have properly
> opened the connection in begin of
> method and Closed in end of method. Inside the execution of method if i
> called some method means then it
> can also use the same connection. Then Why its giving this error.
> Please anybody knows solution to my
> problem let me know to solve it.
> Thanks,
> Vinoth
> vinoth@.gsdindia.com
>sql

Problem in Context connection of CLR Trigger

Hi,
I'm using June CTP Visual Studio 2005.
I have Created CLR Object. In my CLR Method i have
opened a
connection like below
public partial class Triggers
{
//In my CLR Method i have Opened the connection like below
[Microsoft.SqlServer.Server.Sq­lTrigger(Name="ClrTrigger",
Target="Triplets",Event="FOR INSERT")]
public static void CLRTrigger()
{
Triggers objTriggers = new Triggers();
objTriggers.Connection(true);
...
.....
Test();
// Here i'm calling one more method Test();
....
....
objTriggers.Connection(false);
}
In this test method i'm Executing the command.
Public void Test()
{
....
SqlCommand cmd = new SqlCommand();
string cmdText = "select dirname from Docs where id=45";
cmd.CommandText = cmdText;
cmd.Connection = con;
try
{
sdr = cmd.ExecuteReader();
}
catch (Exception exp)
{
WriteLog("R E ::: "+exp.Message);
}
.......

}
//Connection open and Close method
public void Connection(bool status)
{
if (status)
{
con = new SqlConnection("context connection = true");
con.Open();
}
else
{
con.Close();
}
}
}
I'm getting the error of Connection already in use. I have
properly
opened the connection in begin of

method and Closed in end of method. Inside the execution
of method if i
called some method means then it
can also use the same connection. Then Why its giving this
error.
Please anybody knows solution to my
problem let me know to solve it.
Thanks,
Vinoth
vinoth@.gsdindia.comThere are a couple of possibilities:

1) You can only have one open context connection at a time within a clr procedure ("context connection=true"). Is it possible that in the missing code snippets for your example (...'s) that you are attempting to open another context connection? For instance, calling Triggers.Connection(true) again anywhere prior to the Triggers.Connection(false) call would result in this exception. The call stack where the "Connection already in use" exception is thrown would be useful to track down this cause.

2) June CTP has a couple of bugs where an exception, especially if due to an attention (i.e. cancel) signal, can cause the connection to not properly clean itself up from one invocation of the procedure to the next. This problem would not be apparent on first invocation of the proc, and would temporarily be cleared by running "DBCC FREEPROCCACHE".

One other question I have is how you get the SqlConnection from the CLRTriggers() method to the Test() method. It would appear that the "con" field must be static, true? If this is the case, you will almost certainly run into problems, since your trigger can fire on multiple threads, yet the SqlConnection object is valid only in the procedure in which it was created.

I'd like to note that the recommended use pattern for SqlConnection (and cmd.ExecuteReader) is to put them in a "using" statement to guarantee cleanup and help see the lifetime scope of the connection. i.e.

using (SqlConnection con = new SqlConnection("context connection = true")) {
con.Open();
...
}

and

using (SqlDataReader rdr = cmd.ExecuteReader()) {
...
}

~Alazel

problem in ConnectionString

hi,
i have problem in connectionstring in sql server.
my connection string is,
--
conn.ConnectionString = "workstation id=BARODA;packet size=4096;integrated security=SSPI;data source=BARODA\MYINSTANCE;user id=sa;password=;persist security info=True;initial catalog=SMS"
it will give error like....
login fail for user "MERIDIAN\IUSER_GROUP"

---
conn.ConnectionString = "workstation id=BARODA;packet size=4096;data source=BARODA\MYINSTANCE;user id=sa;password=;persist security info=True;initial catalog=SMS"
it will give error like....
login fail for user "sa"

---
conn.ConnectionString = "workstation id=BARODA;packet size=4096;data source=BARODA\MYINSTANCE;user id=;password=;persist security info=True;initial catalog=SMS"
it will give error like....
login fail for user "(null)"

i can do everything, but error occur everytimes.

when i use this, without instance
conn.ConnectionString = "workstation id=BARODA;packet size=4096;data source=BARODA;user id=;password=;persist security info=True;initial catalog=SMS"
it will run successfully,

but i have instance,
and i want to run with it.

plz give any idea.
it's urgent.

thanks in advance.check www.connectionstrings.com

hth|||i also check this site. but error occurs as it is.
my sql server in mix mode auth.
i also create MERIDIAN\IUSR_BARODA,inspite of this error comes.
i do everythings.

----login fail-'MERIDIAN\IUSR_BARODA'
conn.connectionString="workstation id=BARODA;packet size=4096;integrated security=SSPI;data source="BARODA\MYINSTANCE";persist security info=True;initial catalog=SMS"

----login fail-'sa'
conn.ConnectionString = "data source=BARODA\MYINSTANCE;user id=sa;password=;initial catalog=SMS;persist security info=true;workstation id=BARODA;Packet size=4096"

----login fail-'null'-not associated with trusted connection
conn.ConnectionString = "data source=BARODA\MYINSTANCE;initial catalog=SMS;persist security info=yes"

----login fail-'sa'
conn.ConnectionString = "workstation id=BARODA;packet size=4096;data source=BARODA\MYINSTANCE;user id=sa;password=;initial catalog=SMS"

----login fail-'MERIDIAN\IUSR_BARODA'
conn.ConnectionString = "workstation id=BARODA;packet size=4096;integrated security=SSPI;data source=BARODA\MYINSTANCE;user id=sa;password=XX;initial catalog=SMS"

----login fail-'sa'
conn.ConnectionString = "workstation id=BARODA;packet size=4096;integrated security=false;data source=BARODA\MYINSTANCE;user id=sa;password=;initial catalog=SMS;persist security info=false"

----login fail-'sa'
conn.ConnectionString = "data source=BARODA\MYINSTANCE;user id=sa;password=;initial catalog=SMS;trusted_connection=false"

connectionstrings which is given above i was use it but above error occurs.

plz give any idea.
it's urgent.

thanks in advance.|||you need to add the machinename\ASPNET user account to the users for your database in the enterprise manager. the connection string is correct. the user does not seem to have access to the db.

hth

problem in connection with SQLServer using DSN

Hi all,
I am using OdbcConnection for coonectivity with SQL Server db. My code
is working fine with windows application but in ASP.NET or in
webservice its raising following exception -
"ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL
Server does not exist or access denied.
ERROR [01000] [Microsoft][ODBC SQL Server Driver]
[DBNETLIB]ConnectionOpen (Connect())."
at System.Data.Odbc.OdbcConnection.Open()
Same code is working fine for Orace DSN.
My code:
OdbcConnection conn = new
OdbcConnection("dsn=MyDsn;uid=sa;pwd=stars;");
conn.Open();
Please help me to sort out this problem
Thanks
Dharmendra
I expect the reason that no one jumped on this question (which has been
asked and answered a million times) is that you're clearly not following the
advice given here and elsewhere. I suggest you do some reading about getting
connected to SQL Server. The 4th through 6th Editions of my Hitchhiker's
Guide to Visual Basic and SQL Server discuss how to connect via ODBC to SQL
Server. However, they do not talk about the fact that recent versions of SQL
Server require that you enable connectivity before attempting to connect.
This is covered in detail in my latest book and in whitepapers posted on my
blog (see www.betav.com/blogs/billva). And be sure to stop using SA
credentials to handle your customer's data...
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"tomar" <dharmendratomar2000@.gmail.com> wrote in message
news:1179209227.270257.149380@.n59g2000hsh.googlegr oups.com...
> Hi all,
> I am using OdbcConnection for coonectivity with SQL Server db. My code
> is working fine with windows application but in ASP.NET or in
> webservice its raising following exception -
> "ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL
> Server does not exist or access denied.
> ERROR [01000] [Microsoft][ODBC SQL Server Driver]
> [DBNETLIB]ConnectionOpen (Connect())."
> at System.Data.Odbc.OdbcConnection.Open()
> Same code is working fine for Orace DSN.
> My code:
> OdbcConnection conn = new
> OdbcConnection("dsn=MyDsn;uid=sa;pwd=stars;");
> conn.Open();
>
> Please help me to sort out this problem
> Thanks
> Dharmendra
>
|||Thanks for response.
I have tested it dsn through Odbcad32 . It gets connected with sql
server db but when I try to connect via ASP.NET code. It gives error.
Important thing is that
when I load the application with same code on other machine. It works
fine.
Regards
Dharmendra,
================================================== =======
On May 17, 1:20 am, "William \(Bill\) Vaughn"
<billvaRemoveT...@.betav.com> wrote:
> I expect the reason that no one jumped on this question (which has been
> asked and answered a million times) is that you're clearly not following the
> advice given here and elsewhere. I suggest you do some reading about getting
> connected to SQL Server. The 4th through 6th Editions of my Hitchhiker's
> Guide to Visual Basic and SQL Server discuss how to connect via ODBC to SQL
> Server. However, they do not talk about the fact that recent versions of SQL
> Server require that you enable connectivity before attempting to connect.
> This is covered in detail in my latest book and in whitepapers posted on my
> blog (seewww.betav.com/blogs/billva). And be sure to stop using SA
> credentials to handle your customer's data...
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speakerwww.betav.com/blog/billvawww.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> Visitwww.hitchhikerguides.netto get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----X---
> "tomar" <dharmendratomar2...@.gmail.com> wrote in message
> news:1179209227.270257.149380@.n59g2000hsh.googlegr oups.com...
>
>
>
>
>
> - Show quoted text -

problem in connection with SQLServer using DSN

Hi all,
I am using OdbcConnection for coonectivity with SQL Server db. My code
is working fine with windows application but in ASP.NET or in
webservice its raising following exception -
"ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]
SQL
Server does not exist or access denied.
ERROR [01000] [Microsoft][ODBC SQL Server Driver]
[DBNETLIB]ConnectionOpen (Connect())."
at System.Data.Odbc.OdbcConnection.Open()
Same code is working fine for Orace DSN.
My code:
OdbcConnection conn = new
OdbcConnection("dsn=MyDsn;uid=sa;pwd=stars;");
conn.Open();
Please help me to sort out this problem
Thanks
DharmendraI expect the reason that no one jumped on this question (which has been
asked and answered a million times) is that you're clearly not following the
advice given here and elsewhere. I suggest you do some reading about getting
connected to SQL Server. The 4th through 6th Editions of my Hitchhiker's
Guide to Visual Basic and SQL Server discuss how to connect via ODBC to SQL
Server. However, they do not talk about the fact that recent versions of SQL
Server require that you enable connectivity before attempting to connect.
This is covered in detail in my latest book and in whitepapers posted on my
blog (see www.betav.com/blogs/billva). And be sure to stop using SA
credentials to handle your customer's data...
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"tomar" <dharmendratomar2000@.gmail.com> wrote in message
news:1179209227.270257.149380@.n59g2000hsh.googlegroups.com...
> Hi all,
> I am using OdbcConnection for coonectivity with SQL Server db. My code
> is working fine with windows application but in ASP.NET or in
> webservice its raising following exception -
> "ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLI
B]SQL
> Server does not exist or access denied.
> ERROR [01000] [Microsoft][ODBC SQL Server Driver]
> [DBNETLIB]ConnectionOpen (Connect())."
> at System.Data.Odbc.OdbcConnection.Open()
> Same code is working fine for Orace DSN.
> My code:
> OdbcConnection conn = new
> OdbcConnection("dsn=MyDsn;uid=sa;pwd=stars;");
> conn.Open();
>
> Please help me to sort out this problem
> Thanks
> Dharmendra
>|||Thanks for response.
I have tested it dsn through Odbcad32 . It gets connected with sql
server db but when I try to connect via ASP.NET code. It gives error.
Important thing is that
when I load the application with same code on other machine. It works
fine.
Regards
Dharmendra,
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D
On May 17, 1:20 am, "William \(Bill\) Vaughn"
<billvaRemoveT...@.betav.com> wrote:
> I expect the reason that no one jumped on this question (which has been
> asked and answered a million times) is that you're clearly not following =
the
> advice given here and elsewhere. I suggest you do some reading about gett=
ing
> connected to SQL Server. The 4th through 6th Editions of my Hitchhiker's
> Guide to Visual Basic and SQL Server discuss how to connect via ODBC to S=
QL
> Server. However, they do not talk about the fact that recent versions of =
SQL
> Server require that you enable connectivity before attempting to connect.
> This is covered in detail in my latest book and in whitepapers posted on =
my
> blog (seewww.betav.com/blogs/billva). And be sure to stop using SA
> credentials to handle your customer's data...
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speakerwww.betav.com/blog/billvawww.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no right=
s=2E
> __________________________________
> Visitwww.hitchhikerguides.netto get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----=
--=AD---
> "tomar" <dharmendratomar2...@.gmail.com> wrote in message
> news:1179209227.270257.149380@.n59g2000hsh.googlegroups.com...
>
>
>
>
>
>
>
>
> - Show quoted text -sql

Problem in connection to sqlexpress witn ASP.nET

hi all

When I use asp.net 2.1 to connect sql 2005 express locally I got error message to say

An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact
that under the default settings SQL Server does not allow remote connections.
(provider: SQL Network Interfaces, error: 25 - Connection string is not valid)

I use following connection string

ConnectToDb.ConnectionString = "Data Source=servername;InitialCatalog=PushToMail;Integrated Security=true";

for more information : when I open SQLEXPRESS this oblige me to inter

servername : servername\sqlexpress

authentification: windowsauthentification

and when i try this connection

ConnectToDb.ConnectionString = "Data Source=servername\sqlexpress;InitialCatalog=PushToMail;Integrated

Security=true";

l got this erreur "inorganised sequence space"

next time u install sql 2005 ,use both authentication ,windows and sql.

or try to connect sql using your VS 2005 ,if u have.

|||

Read this thread,

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=327846&SiteID=1

It has answer that explains how to enable remote connections.

Problem in connecting 2 databases with sql Server 2005 Express

I am programming in VB6 using ADO 2.8. This connection and query works in MS access, SQL server 2000 and Sql Server 2005. does not work in Sql server 2005 express. Any Suggestions?

Connection String #1 Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;AttachDBFileName=C:\Program Files\Material_Management_System\DATA\Main.mdf;Data Source=Steve_Laptop\sqlexpress

Connection String #2 Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;AttachDBFileName=C:\Program Files\Material_Management_System\DATA\Items.mdf;Data Source=Steve_Laptop\sqlexpress

Sql Query: Select POLINE.ID as POLine_ID, PFMS.ID as Items_ID FROM POLINE LEFT JOIN Items.PFMS as PFMS ON POLINE.lItem_ID = PFMS.ID

Error: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Items.PFMS'

Sql Query#2: Select POLINE.ID as POLine_ID, PFMS.ID as Items_ID FROM POLINE LEFT JOIN Items.dbo.PFMS as PFMS ON POLINE.lItem_ID = PFMS.ID

Error: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Items.dbo.PFMS'.

I am running the queries directly from the 2005 Mgt window to take as many variables out of the equasion. I get the same error in Visual basic

How am I supposed to reference a join of 2 databases? Any suggestions

I did not try that yet, but I think the cross join between user instances is not possible, you either have to do an in-memory join in .NET or attach the databases to the SQL Server Express instance and then join them using the three part name.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Problem Deploying SSAS Connection cannot be made. Ensure server running

I have a couple of questions. I've gotten Our SSAS Running against SS 2000 where the Server is separate from the Web Server running IIS by installing SSAS on the Web Server and Pointing it to the instance on the Database Server. This works on both mine and my bosses systems and I finally got it working on our internal server (the described situation above, our dev computers have all running on one box).

When I implement this on one of our client configurations that is the same as the first above (DB + SSAS and connected to IIS Server with SSAS as well) I cannot get it to connect and return the data.

The error I get via my Asp.net 2.0 webpage is :

A connection cannot be made. Ensure that the server is running.

I have checked every possible setting between the two sets of servers and I'm about to lose it

Everything seems identical. Do you have any suggestions. I checked the services to see if the SSAS was running on the web server and it is.

Suggestions?

SSAS authenticates using windows authentication. In order to get an ASP.NET application to connect to SSAS, it needs to be running under an account that is in a role in SSAS that has rights to view the cube you want to query.

There are a couple of ways to do this:

For IIS6, you are best to setup an application pool running under a domain account, run your ASP.NET in that application pool and put that domain account in a role in SSAS.

For IIS5 instead of using an application pool you can set the account using the <Identity> tag in the web.config

Note: to SSAS, it will see all requests as coming from a single user so you cannot use role based security with this sort of setup. If you need role based security you would need to maybe look into setting up HTTP authentication.

|||

Ok I'm a bit confused. Sorry.

I've got on Server 1

IIS & Analysis Services

On Server 2

SQL Server 2000 & Analysis Services

As far as I got was installing Analysis Services on the Web Server.

There is an Application Pool that the website is running under but I'm not sure how to setup the Roles for that Pool.

I added user roles under the SSAS on the web server but it's still not working. Sorry for my confusion, I took over this project from another developer and have never worked with this before. Thank you so much for your assistance.

|||So why are there 2 AS servers? and which one has the database you want to connect to?

If you just want your web server to connect to the second server with AS and SQL on it then you don't need AS on the web server, you just need the OLEDB provider for Analysis Services (version 8.0 for AS 2000). The typical problem with web servers is that NTLM credentials will not do 2 hops. What this means is that the client can authenticate to the web server, but the web server cannot pass those credentials along to the second server (for security reasons).

There are actually 2 ways around this you can configure Kerberos authentication (there are a couple of whitepapers from MS on this, they should not be too hard to find). The only other thing you need is to have <Identity Impersonate="True" /> in your web.config.

Using the original method I described, I would actually create a new app pool, just for this application. In the app pool settings I think there is an Identity tab, you would set this to run as a specific domain user. Then on your AS server create a role with the same domain account and give it access to the database/cubes that the web app needs.

|||

I tried setting up Application Pool, didn't work. I'm not sure the client wants Kerberos installed on the machine. I have this working on our internal computers (2 workstations) and our development servers. I'm trying to put it on theirs and having difficulty.

I setup Roles on the AS server (I'd rather not use the same one as on the database server) on the IIS Server (running Windows Server 2003). I can connect to the DataMart and see the data. I can get to the page but it wont' connect from IIS to AS it seems.

Using <Identity...> blows it up says it's not recognized (I'm assuming you need IIS 5 or Kerberos for that to work).

I setup every way I could imagine in the App Pool settings. It defaults as Network. I tried that. Then I tried various user configurations.

So the DB server is on SQL 2000, our test was on SQL 2005.

Thanks for your help. Any more ideas?

|||

If you want to use the AS on the IIS box, then that makes things a bit easier. You only need Kerberos when IIS and AS are on separate machines.

The <Identity ...> tag is an ASP.NET setting and works for both IIS 5 and 6, but it is case sensitive and needs to be under the right section of the web.config file. But if you are using IIS 6 and don't need role based security then you are better off just using the app pool identity.

If you left the App Pool set to Network, you would need to go into the Role Manager for your database and either create a new role or add the "NETWORK" user to an existing role.

It's a lot simpler if IIS and AS are on the same box, the main "trick" is that it is the app pool account that connects to AS and needs to be in an appropriate role. If they are on the same machine you should not have trouble with firewalls and identities not crossing machine boundaries.

Problem defining a query parameter in an Oracle connection

Hi all...
I have an Oracle Type connection whose connection string is only:
data source=Pluton.
I created a dataset with a query parameter, this way:
SELECT NUMPOL
FROM POLIZA
WHERE NUMPOL = @.NUMERO_POLIZA
Here I have got some problems:
1.- The parameter wasn't created automatically, so I had to go to the query
properties and under Parameters tab I added:
Name: @.NUMERO_POLIZA
Value: =Parameters!NUMERO_POLIZA.Value
NUMERO_POLIZA parameter is defined this way:
Name: NUMERO_POLIZA
Prompt: "Número de Póliza"
Data Type: Integer
Available values: non-queried (list empty)
Default values: None
2.- When I run the query, a popup dialog is shown that lets me to define
Query Parameters. @.NUMERO_POLIZA is listed there with a combobox at the
Parameter Value column. I entered a number in that field and pressed OK.
Immediately an popup error is shown: ORA-00936: missing expression
It seems that the query reachs Oracle provider with the name @.NUMERO_POLIZA,
not the value of it.
I tried using OLEDB provider instead. In that case, query parameters are
specified using "?" (interrrogation mark). When I used it and ran the query,
after specifying the query parameter value, query is executed correctly. No
problem, but when I preview the report, and enter the parameter, #Error word
appears instead of a field resulting from the query.
Any help would be greatly appreciated (I want the Oracle type connection to
work, since I have read this is the most efficient method)
Thanks
JaimeOracle has a few unique things going on. First, my recommendation is to use
the generic data designer (2 panes). The button to switch to this is to the
right of the ...
Second, because the development environment was not designed for managed
providers they got tricky with what is used under the covers (hence my
recommendation to use the generic designer). Here is a description from
Robert Bruckner [MSFT].
/Snip
Note: the behavior of PREVIEW in Report Designer is identical to the
ReportServer behavior! However the DATA view in Report Designer is
different for the visual designer: * the visual query designer with 4 panes
will internally always use OleDB providers for verifying and executing
queries directly in "Data" view. (Main reason: the visual query designer
does not work with managed providers). Example: if you choose "Oracle" in
the data source dialog, the Data view has to use the OleDB provider for
Oracle behind the scenes, but Preview and Server will use the managed Oracle
provider. The generic text-based query designer (2 panes) will _always_ use
the data provider you specified.
/End Snip
Just a little background for you. OK, now, from the generic query designer.
He then had this to say about stored procedures:
/Snip
In addition, how do you return the data from your stored procedure? Note:
only an out ref cursor is supported. 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.
/End Snip
Hope that helps. Definitely not intuitive but it works.
One last thing. The MS managed provider for Oracle need 8.1.7 or higher (8i)
client installed for it to work.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
news:C534517B-A9A4-483E-AB41-2348FC3914AC@.microsoft.com...
> Hi all...
> I have an Oracle Type connection whose connection string is only:
> data source=Pluton.
> I created a dataset with a query parameter, this way:
> SELECT NUMPOL
> FROM POLIZA
> WHERE NUMPOL = @.NUMERO_POLIZA
> Here I have got some problems:
> 1.- The parameter wasn't created automatically, so I had to go to the
> query
> properties and under Parameters tab I added:
> Name: @.NUMERO_POLIZA
> Value: =Parameters!NUMERO_POLIZA.Value
> NUMERO_POLIZA parameter is defined this way:
> Name: NUMERO_POLIZA
> Prompt: "Número de Póliza"
> Data Type: Integer
> Available values: non-queried (list empty)
> Default values: None
> 2.- When I run the query, a popup dialog is shown that lets me to define
> Query Parameters. @.NUMERO_POLIZA is listed there with a combobox at the
> Parameter Value column. I entered a number in that field and pressed OK.
> Immediately an popup error is shown: ORA-00936: missing expression
> It seems that the query reachs Oracle provider with the name
> @.NUMERO_POLIZA,
> not the value of it.
> I tried using OLEDB provider instead. In that case, query parameters are
> specified using "?" (interrrogation mark). When I used it and ran the
> query,
> after specifying the query parameter value, query is executed correctly.
> No
> problem, but when I preview the report, and enter the parameter, #Error
> word
> appears instead of a field resulting from the query.
> Any help would be greatly appreciated (I want the Oracle type connection
> to
> work, since I have read this is the most efficient method)
> Thanks
> Jaime|||Just a few additions to what Bruce said already:
Managed Oracle provider (named parameters):
select * from table where ename = :parameter
OleDB for Oracle (unnamed parameters):
select * from table where ename = ?
The managed Oracle data provider uses a ':' to mark named parameters
(instead of '@.'); the OleDB provider for Oracle only allows unnamed
parameters (using '?'). The following KB article explains more details:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
Note: the Visual Data Tools (VDT) query designer (2 panes) actually uses OLE
DB in the preview pane. The text-based generic query designer (GQD; 4 panes)
uses the .NET provider for Oracle. Generally, you will achieve better
results when using GQD with Oracle.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O4c3omYfFHA.2444@.tk2msftngp13.phx.gbl...
> Oracle has a few unique things going on. First, my recommendation is to
> use the generic data designer (2 panes). The button to switch to this is
> to the right of the ...
> Second, because the development environment was not designed for managed
> providers they got tricky with what is used under the covers (hence my
> recommendation to use the generic designer). Here is a description from
> Robert Bruckner [MSFT].
> /Snip
> Note: the behavior of PREVIEW in Report Designer is identical to the
> ReportServer behavior! However the DATA view in Report Designer is
> different for the visual designer: * the visual query designer with 4
> panes will internally always use OleDB providers for verifying and
> executing queries directly in "Data" view. (Main reason: the visual query
> designer does not work with managed providers). Example: if you choose
> "Oracle" in the data source dialog, the Data view has to use the OleDB
> provider for Oracle behind the scenes, but Preview and Server will use the
> managed Oracle provider. The generic text-based query designer (2 panes)
> will _always_ use the data provider you specified.
> /End Snip
> Just a little background for you. OK, now, from the generic query
> designer. He then had this to say about stored procedures:
> /Snip
> In addition, how do you return the data from your stored procedure? Note:
> only an out ref cursor is supported. 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.
> /End Snip
> Hope that helps. Definitely not intuitive but it works.
> One last thing. The MS managed provider for Oracle need 8.1.7 or higher
> (8i) client installed for it to work.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
> news:C534517B-A9A4-483E-AB41-2348FC3914AC@.microsoft.com...
>> Hi all...
>> I have an Oracle Type connection whose connection string is only:
>> data source=Pluton.
>> I created a dataset with a query parameter, this way:
>> SELECT NUMPOL
>> FROM POLIZA
>> WHERE NUMPOL = @.NUMERO_POLIZA
>> Here I have got some problems:
>> 1.- The parameter wasn't created automatically, so I had to go to the
>> query
>> properties and under Parameters tab I added:
>> Name: @.NUMERO_POLIZA
>> Value: =Parameters!NUMERO_POLIZA.Value
>> NUMERO_POLIZA parameter is defined this way:
>> Name: NUMERO_POLIZA
>> Prompt: "Número de Póliza"
>> Data Type: Integer
>> Available values: non-queried (list empty)
>> Default values: None
>> 2.- When I run the query, a popup dialog is shown that lets me to define
>> Query Parameters. @.NUMERO_POLIZA is listed there with a combobox at the
>> Parameter Value column. I entered a number in that field and pressed OK.
>> Immediately an popup error is shown: ORA-00936: missing expression
>> It seems that the query reachs Oracle provider with the name
>> @.NUMERO_POLIZA,
>> not the value of it.
>> I tried using OLEDB provider instead. In that case, query parameters are
>> specified using "?" (interrrogation mark). When I used it and ran the
>> query,
>> after specifying the query parameter value, query is executed correctly.
>> No
>> problem, but when I preview the report, and enter the parameter, #Error
>> word
>> appears instead of a field resulting from the query.
>> Any help would be greatly appreciated (I want the Oracle type connection
>> to
>> work, since I have read this is the most efficient method)
>> Thanks
>> Jaime
>|||Not to confuse things but VDT is 4 panes and GQD is 2 panes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23%23DFHqYfFHA.3584@.TK2MSFTNGP09.phx.gbl...
> Just a few additions to what Bruce said already:
> Managed Oracle provider (named parameters):
> select * from table where ename = :parameter
> OleDB for Oracle (unnamed parameters):
> select * from table where ename = ?
> The managed Oracle data provider uses a ':' to mark named parameters
> (instead of '@.'); the OleDB provider for Oracle only allows unnamed
> parameters (using '?'). The following KB article explains more details:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
> Note: the Visual Data Tools (VDT) query designer (2 panes) actually uses
> OLE DB in the preview pane. The text-based generic query designer (GQD; 4
> panes) uses the .NET provider for Oracle. Generally, you will achieve
> better results when using GQD with Oracle.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:O4c3omYfFHA.2444@.tk2msftngp13.phx.gbl...
>> Oracle has a few unique things going on. First, my recommendation is to
>> use the generic data designer (2 panes). The button to switch to this is
>> to the right of the ...
>> Second, because the development environment was not designed for managed
>> providers they got tricky with what is used under the covers (hence my
>> recommendation to use the generic designer). Here is a description from
>> Robert Bruckner [MSFT].
>> /Snip
>> Note: the behavior of PREVIEW in Report Designer is identical to the
>> ReportServer behavior! However the DATA view in Report Designer is
>> different for the visual designer: * the visual query designer with 4
>> panes will internally always use OleDB providers for verifying and
>> executing queries directly in "Data" view. (Main reason: the visual query
>> designer does not work with managed providers). Example: if you choose
>> "Oracle" in the data source dialog, the Data view has to use the OleDB
>> provider for Oracle behind the scenes, but Preview and Server will use
>> the managed Oracle provider. The generic text-based query designer (2
>> panes) will _always_ use the data provider you specified.
>> /End Snip
>> Just a little background for you. OK, now, from the generic query
>> designer. He then had this to say about stored procedures:
>> /Snip
>> In addition, how do you return the data from your stored procedure? Note:
>> only an out ref cursor is supported. 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.
>> /End Snip
>> Hope that helps. Definitely not intuitive but it works.
>> One last thing. The MS managed provider for Oracle need 8.1.7 or higher
>> (8i) client installed for it to work.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
>> news:C534517B-A9A4-483E-AB41-2348FC3914AC@.microsoft.com...
>> Hi all...
>> I have an Oracle Type connection whose connection string is only:
>> data source=Pluton.
>> I created a dataset with a query parameter, this way:
>> SELECT NUMPOL
>> FROM POLIZA
>> WHERE NUMPOL = @.NUMERO_POLIZA
>> Here I have got some problems:
>> 1.- The parameter wasn't created automatically, so I had to go to the
>> query
>> properties and under Parameters tab I added:
>> Name: @.NUMERO_POLIZA
>> Value: =Parameters!NUMERO_POLIZA.Value
>> NUMERO_POLIZA parameter is defined this way:
>> Name: NUMERO_POLIZA
>> Prompt: "Número de Póliza"
>> Data Type: Integer
>> Available values: non-queried (list empty)
>> Default values: None
>> 2.- When I run the query, a popup dialog is shown that lets me to define
>> Query Parameters. @.NUMERO_POLIZA is listed there with a combobox at the
>> Parameter Value column. I entered a number in that field and pressed OK.
>> Immediately an popup error is shown: ORA-00936: missing expression
>> It seems that the query reachs Oracle provider with the name
>> @.NUMERO_POLIZA,
>> not the value of it.
>> I tried using OLEDB provider instead. In that case, query parameters are
>> specified using "?" (interrrogation mark). When I used it and ran the
>> query,
>> after specifying the query parameter value, query is executed correctly.
>> No
>> problem, but when I preview the report, and enter the parameter, #Error
>> word
>> appears instead of a field resulting from the query.
>> Any help would be greatly appreciated (I want the Oracle type connection
>> to
>> work, since I have read this is the most efficient method)
>> Thanks
>> Jaime
>>
>|||Thanks Bruce and Robert for explanations. I use Generic Designer and replaced
@. by :. When I ran the query, I was finally asked to enter parameters. All
that was fine, but when query tried to execute, I got the error "Fetch out of
sequence" :-( as I asked before in this newsgroup.
What you said makes sense for me now, because when I ran the query in Visual
Designer, it works (using unnamed parameters). That was because it is using
OLEDB provider and the others Oracle provider.
In one of the tests I've made (to try to solve "Fetch out of sequence"
error), I have configured the connection to be OLEDB and queries ran, but I
had problems with parameters. I used "?", but I got very confused about the
usage of the "?" given by the designer. I tried to rename those ? (in
Parameters tab of the DataSet) to something more meaningful, but in that way,
parameters didn't work, so I got back to test using Oracle Provider.
Do you know why I get that error when I run the query? I have read that this
error may occur when Autocommit property of the provider is set to true and
SELECT FOR UPDATE instruction is used, but this is not the case.
Jaime
"Bruce L-C [MVP]" wrote:
> Not to confuse things but VDT is 4 panes and GQD is 2 panes.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:%23%23DFHqYfFHA.3584@.TK2MSFTNGP09.phx.gbl...
> > Just a few additions to what Bruce said already:
> >
> > Managed Oracle provider (named parameters):
> > select * from table where ename = :parameter
> > OleDB for Oracle (unnamed parameters):
> > select * from table where ename = ?
> >
> > The managed Oracle data provider uses a ':' to mark named parameters
> > (instead of '@.'); the OleDB provider for Oracle only allows unnamed
> > parameters (using '?'). The following KB article explains more details:
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
> >
> > Note: the Visual Data Tools (VDT) query designer (2 panes) actually uses
> > OLE DB in the preview pane. The text-based generic query designer (GQD; 4
> > panes) uses the .NET provider for Oracle. Generally, you will achieve
> > better results when using GQD with Oracle.
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:O4c3omYfFHA.2444@.tk2msftngp13.phx.gbl...
> >> Oracle has a few unique things going on. First, my recommendation is to
> >> use the generic data designer (2 panes). The button to switch to this is
> >> to the right of the ...
> >>
> >> Second, because the development environment was not designed for managed
> >> providers they got tricky with what is used under the covers (hence my
> >> recommendation to use the generic designer). Here is a description from
> >> Robert Bruckner [MSFT].
> >> /Snip
> >> Note: the behavior of PREVIEW in Report Designer is identical to the
> >> ReportServer behavior! However the DATA view in Report Designer is
> >> different for the visual designer: * the visual query designer with 4
> >> panes will internally always use OleDB providers for verifying and
> >> executing queries directly in "Data" view. (Main reason: the visual query
> >> designer does not work with managed providers). Example: if you choose
> >> "Oracle" in the data source dialog, the Data view has to use the OleDB
> >> provider for Oracle behind the scenes, but Preview and Server will use
> >> the managed Oracle provider. The generic text-based query designer (2
> >> panes) will _always_ use the data provider you specified.
> >> /End Snip
> >>
> >> Just a little background for you. OK, now, from the generic query
> >> designer. He then had this to say about stored procedures:
> >> /Snip
> >> In addition, how do you return the data from your stored procedure? Note:
> >> only an out ref cursor is supported. 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.
> >> /End Snip
> >>
> >> Hope that helps. Definitely not intuitive but it works.
> >>
> >> One last thing. The MS managed provider for Oracle need 8.1.7 or higher
> >> (8i) client installed for it to work.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
> >> news:C534517B-A9A4-483E-AB41-2348FC3914AC@.microsoft.com...
> >> Hi all...
> >>
> >> I have an Oracle Type connection whose connection string is only:
> >> data source=Pluton.
> >>
> >> I created a dataset with a query parameter, this way:
> >>
> >> SELECT NUMPOL
> >> FROM POLIZA
> >> WHERE NUMPOL = @.NUMERO_POLIZA
> >>
> >> Here I have got some problems:
> >>
> >> 1.- The parameter wasn't created automatically, so I had to go to the
> >> query
> >> properties and under Parameters tab I added:
> >>
> >> Name: @.NUMERO_POLIZA
> >> Value: =Parameters!NUMERO_POLIZA.Value
> >>
> >> NUMERO_POLIZA parameter is defined this way:
> >>
> >> Name: NUMERO_POLIZA
> >> Prompt: "Número de Póliza"
> >> Data Type: Integer
> >> Available values: non-queried (list empty)
> >> Default values: None
> >>
> >> 2.- When I run the query, a popup dialog is shown that lets me to define
> >> Query Parameters. @.NUMERO_POLIZA is listed there with a combobox at the
> >> Parameter Value column. I entered a number in that field and pressed OK.
> >> Immediately an popup error is shown: ORA-00936: missing expression
> >>
> >> It seems that the query reachs Oracle provider with the name
> >> @.NUMERO_POLIZA,
> >> not the value of it.
> >>
> >> I tried using OLEDB provider instead. In that case, query parameters are
> >> specified using "?" (interrrogation mark). When I used it and ran the
> >> query,
> >> after specifying the query parameter value, query is executed correctly.
> >> No
> >> problem, but when I preview the report, and enter the parameter, #Error
> >> word
> >> appears instead of a field resulting from the query.
> >>
> >> Any help would be greatly appreciated (I want the Oracle type connection
> >> to
> >> work, since I have read this is the most efficient method)
> >>
> >> Thanks
> >> Jaime
> >>
> >>
> >
> >
>
>|||I remember your previous post. I believe you are going against a 7.x
database? I assume you do have a recent client installed. What client are
you using? MS requires 8i or greater client. It could be something to do
with the combination here. MS managed provider, Oracle 8i or greater client,
but a very old Oracle database. My suggestion is to stop trying to use the
managed provider and either use OLEDB or use ODBC. I use ODBC against
Sybase and the performance is not a problem. In most cases the amount of
time in rendering exceeds the time retrieving the data by a significant
amount.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
news:BCACE9D7-9F57-4C2A-869F-4BC54F61A43D@.microsoft.com...
> Thanks Bruce and Robert for explanations. I use Generic Designer and
> replaced
> @. by :. When I ran the query, I was finally asked to enter parameters. All
> that was fine, but when query tried to execute, I got the error "Fetch out
> of
> sequence" :-( as I asked before in this newsgroup.
> What you said makes sense for me now, because when I ran the query in
> Visual
> Designer, it works (using unnamed parameters). That was because it is
> using
> OLEDB provider and the others Oracle provider.
> In one of the tests I've made (to try to solve "Fetch out of sequence"
> error), I have configured the connection to be OLEDB and queries ran, but
> I
> had problems with parameters. I used "?", but I got very confused about
> the
> usage of the "?" given by the designer. I tried to rename those ? (in
> Parameters tab of the DataSet) to something more meaningful, but in that
> way,
> parameters didn't work, so I got back to test using Oracle Provider.
> Do you know why I get that error when I run the query? I have read that
> this
> error may occur when Autocommit property of the provider is set to true
> and
> SELECT FOR UPDATE instruction is used, but this is not the case.
> Jaime
> "Bruce L-C [MVP]" wrote:
>> Not to confuse things but VDT is 4 panes and GQD is 2 panes.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
>> news:%23%23DFHqYfFHA.3584@.TK2MSFTNGP09.phx.gbl...
>> > Just a few additions to what Bruce said already:
>> >
>> > Managed Oracle provider (named parameters):
>> > select * from table where ename = :parameter
>> > OleDB for Oracle (unnamed parameters):
>> > select * from table where ename = ?
>> >
>> > The managed Oracle data provider uses a ':' to mark named parameters
>> > (instead of '@.'); the OleDB provider for Oracle only allows unnamed
>> > parameters (using '?'). The following KB article explains more details:
>> > http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
>> >
>> > Note: the Visual Data Tools (VDT) query designer (2 panes) actually
>> > uses
>> > OLE DB in the preview pane. The text-based generic query designer (GQD;
>> > 4
>> > panes) uses the .NET provider for Oracle. Generally, you will achieve
>> > better results when using GQD with Oracle.
>> >
>> > -- Robert
>> > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >
>> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> > news:O4c3omYfFHA.2444@.tk2msftngp13.phx.gbl...
>> >> Oracle has a few unique things going on. First, my recommendation is
>> >> to
>> >> use the generic data designer (2 panes). The button to switch to this
>> >> is
>> >> to the right of the ...
>> >>
>> >> Second, because the development environment was not designed for
>> >> managed
>> >> providers they got tricky with what is used under the covers (hence my
>> >> recommendation to use the generic designer). Here is a description
>> >> from
>> >> Robert Bruckner [MSFT].
>> >> /Snip
>> >> Note: the behavior of PREVIEW in Report Designer is identical to the
>> >> ReportServer behavior! However the DATA view in Report Designer is
>> >> different for the visual designer: * the visual query designer with 4
>> >> panes will internally always use OleDB providers for verifying and
>> >> executing queries directly in "Data" view. (Main reason: the visual
>> >> query
>> >> designer does not work with managed providers). Example: if you choose
>> >> "Oracle" in the data source dialog, the Data view has to use the OleDB
>> >> provider for Oracle behind the scenes, but Preview and Server will use
>> >> the managed Oracle provider. The generic text-based query designer (2
>> >> panes) will _always_ use the data provider you specified.
>> >> /End Snip
>> >>
>> >> Just a little background for you. OK, now, from the generic query
>> >> designer. He then had this to say about stored procedures:
>> >> /Snip
>> >> In addition, how do you return the data from your stored procedure?
>> >> Note:
>> >> only an out ref cursor is supported. 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.
>> >> /End Snip
>> >>
>> >> Hope that helps. Definitely not intuitive but it works.
>> >>
>> >> One last thing. The MS managed provider for Oracle need 8.1.7 or
>> >> higher
>> >> (8i) client installed for it to work.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:C534517B-A9A4-483E-AB41-2348FC3914AC@.microsoft.com...
>> >> Hi all...
>> >>
>> >> I have an Oracle Type connection whose connection string is only:
>> >> data source=Pluton.
>> >>
>> >> I created a dataset with a query parameter, this way:
>> >>
>> >> SELECT NUMPOL
>> >> FROM POLIZA
>> >> WHERE NUMPOL = @.NUMERO_POLIZA
>> >>
>> >> Here I have got some problems:
>> >>
>> >> 1.- The parameter wasn't created automatically, so I had to go to the
>> >> query
>> >> properties and under Parameters tab I added:
>> >>
>> >> Name: @.NUMERO_POLIZA
>> >> Value: =Parameters!NUMERO_POLIZA.Value
>> >>
>> >> NUMERO_POLIZA parameter is defined this way:
>> >>
>> >> Name: NUMERO_POLIZA
>> >> Prompt: "Número de Póliza"
>> >> Data Type: Integer
>> >> Available values: non-queried (list empty)
>> >> Default values: None
>> >>
>> >> 2.- When I run the query, a popup dialog is shown that lets me to
>> >> define
>> >> Query Parameters. @.NUMERO_POLIZA is listed there with a combobox at
>> >> the
>> >> Parameter Value column. I entered a number in that field and pressed
>> >> OK.
>> >> Immediately an popup error is shown: ORA-00936: missing expression
>> >>
>> >> It seems that the query reachs Oracle provider with the name
>> >> @.NUMERO_POLIZA,
>> >> not the value of it.
>> >>
>> >> I tried using OLEDB provider instead. In that case, query parameters
>> >> are
>> >> specified using "?" (interrrogation mark). When I used it and ran the
>> >> query,
>> >> after specifying the query parameter value, query is executed
>> >> correctly.
>> >> No
>> >> problem, but when I preview the report, and enter the parameter,
>> >> #Error
>> >> word
>> >> appears instead of a field resulting from the query.
>> >>
>> >> Any help would be greatly appreciated (I want the Oracle type
>> >> connection
>> >> to
>> >> work, since I have read this is the most efficient method)
>> >>
>> >> Thanks
>> >> Jaime
>> >>
>> >>
>> >
>> >
>>|||Thanks bruce...You were right, I'm using Oracle 9i client trying to connect
to Oracle 7.3.4 database. I have finally solved the problem using OLEDB
provider. But the solution wasn't that trivial. When I deployed the report
to IIS, I got so many different and strange errors. All that errors were due
to permissions problems of the IUSR_machine user to Oracle directory. At
last, I could configure all so that I can view reports both in preview mode
and in web. Thanks again.
Jaime
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OflioZafFHA.3256@.TK2MSFTNGP12.phx.gbl...
>I remember your previous post. I believe you are going against a 7.x
>database? I assume you do have a recent client installed. What client are
>you using? MS requires 8i or greater client. It could be something to do
>with the combination here. MS managed provider, Oracle 8i or greater
>client, but a very old Oracle database. My suggestion is to stop trying to
>use the managed provider and either use OLEDB or use ODBC. I use ODBC
>against Sybase and the performance is not a problem. In most cases the
>amount of time in rendering exceeds the time retrieving the data by a
>significant amount.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
> news:BCACE9D7-9F57-4C2A-869F-4BC54F61A43D@.microsoft.com...
>> Thanks Bruce and Robert for explanations. I use Generic Designer and
>> replaced
>> @. by :. When I ran the query, I was finally asked to enter parameters.
>> All
>> that was fine, but when query tried to execute, I got the error "Fetch
>> out of
>> sequence" :-( as I asked before in this newsgroup.
>> What you said makes sense for me now, because when I ran the query in
>> Visual
>> Designer, it works (using unnamed parameters). That was because it is
>> using
>> OLEDB provider and the others Oracle provider.
>> In one of the tests I've made (to try to solve "Fetch out of sequence"
>> error), I have configured the connection to be OLEDB and queries ran, but
>> I
>> had problems with parameters. I used "?", but I got very confused about
>> the
>> usage of the "?" given by the designer. I tried to rename those ? (in
>> Parameters tab of the DataSet) to something more meaningful, but in that
>> way,
>> parameters didn't work, so I got back to test using Oracle Provider.
>> Do you know why I get that error when I run the query? I have read that
>> this
>> error may occur when Autocommit property of the provider is set to true
>> and
>> SELECT FOR UPDATE instruction is used, but this is not the case.
>> Jaime
>> "Bruce L-C [MVP]" wrote:
>> Not to confuse things but VDT is 4 panes and GQD is 2 panes.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
>> news:%23%23DFHqYfFHA.3584@.TK2MSFTNGP09.phx.gbl...
>> > Just a few additions to what Bruce said already:
>> >
>> > Managed Oracle provider (named parameters):
>> > select * from table where ename = :parameter
>> > OleDB for Oracle (unnamed parameters):
>> > select * from table where ename = ?
>> >
>> > The managed Oracle data provider uses a ':' to mark named parameters
>> > (instead of '@.'); the OleDB provider for Oracle only allows unnamed
>> > parameters (using '?'). The following KB article explains more
>> > details:
>> > http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
>> >
>> > Note: the Visual Data Tools (VDT) query designer (2 panes) actually
>> > uses
>> > OLE DB in the preview pane. The text-based generic query designer
>> > (GQD; 4
>> > panes) uses the .NET provider for Oracle. Generally, you will achieve
>> > better results when using GQD with Oracle.
>> >
>> > -- Robert
>> > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >
>> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> > news:O4c3omYfFHA.2444@.tk2msftngp13.phx.gbl...
>> >> Oracle has a few unique things going on. First, my recommendation is
>> >> to
>> >> use the generic data designer (2 panes). The button to switch to this
>> >> is
>> >> to the right of the ...
>> >>
>> >> Second, because the development environment was not designed for
>> >> managed
>> >> providers they got tricky with what is used under the covers (hence
>> >> my
>> >> recommendation to use the generic designer). Here is a description
>> >> from
>> >> Robert Bruckner [MSFT].
>> >> /Snip
>> >> Note: the behavior of PREVIEW in Report Designer is identical to the
>> >> ReportServer behavior! However the DATA view in Report Designer is
>> >> different for the visual designer: * the visual query designer with
>> >> 4
>> >> panes will internally always use OleDB providers for verifying and
>> >> executing queries directly in "Data" view. (Main reason: the visual
>> >> query
>> >> designer does not work with managed providers). Example: if you
>> >> choose
>> >> "Oracle" in the data source dialog, the Data view has to use the
>> >> OleDB
>> >> provider for Oracle behind the scenes, but Preview and Server will
>> >> use
>> >> the managed Oracle provider. The generic text-based query designer (2
>> >> panes) will _always_ use the data provider you specified.
>> >> /End Snip
>> >>
>> >> Just a little background for you. OK, now, from the generic query
>> >> designer. He then had this to say about stored procedures:
>> >> /Snip
>> >> In addition, how do you return the data from your stored procedure?
>> >> Note:
>> >> only an out ref cursor is supported. 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.
>> >> /End Snip
>> >>
>> >> Hope that helps. Definitely not intuitive but it works.
>> >>
>> >> One last thing. The MS managed provider for Oracle need 8.1.7 or
>> >> higher
>> >> (8i) client installed for it to work.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:C534517B-A9A4-483E-AB41-2348FC3914AC@.microsoft.com...
>> >> Hi all...
>> >>
>> >> I have an Oracle Type connection whose connection string is only:
>> >> data source=Pluton.
>> >>
>> >> I created a dataset with a query parameter, this way:
>> >>
>> >> SELECT NUMPOL
>> >> FROM POLIZA
>> >> WHERE NUMPOL = @.NUMERO_POLIZA
>> >>
>> >> Here I have got some problems:
>> >>
>> >> 1.- The parameter wasn't created automatically, so I had to go to
>> >> the
>> >> query
>> >> properties and under Parameters tab I added:
>> >>
>> >> Name: @.NUMERO_POLIZA
>> >> Value: =Parameters!NUMERO_POLIZA.Value
>> >>
>> >> NUMERO_POLIZA parameter is defined this way:
>> >>
>> >> Name: NUMERO_POLIZA
>> >> Prompt: "Número de Póliza"
>> >> Data Type: Integer
>> >> Available values: non-queried (list empty)
>> >> Default values: None
>> >>
>> >> 2.- When I run the query, a popup dialog is shown that lets me to
>> >> define
>> >> Query Parameters. @.NUMERO_POLIZA is listed there with a combobox at
>> >> the
>> >> Parameter Value column. I entered a number in that field and pressed
>> >> OK.
>> >> Immediately an popup error is shown: ORA-00936: missing expression
>> >>
>> >> It seems that the query reachs Oracle provider with the name
>> >> @.NUMERO_POLIZA,
>> >> not the value of it.
>> >>
>> >> I tried using OLEDB provider instead. In that case, query parameters
>> >> are
>> >> specified using "?" (interrrogation mark). When I used it and ran
>> >> the
>> >> query,
>> >> after specifying the query parameter value, query is executed
>> >> correctly.
>> >> No
>> >> problem, but when I preview the report, and enter the parameter,
>> >> #Error
>> >> word
>> >> appears instead of a field resulting from the query.
>> >>
>> >> Any help would be greatly appreciated (I want the Oracle type
>> >> connection
>> >> to
>> >> work, since I have read this is the most efficient method)
>> >>
>> >> Thanks
>> >> Jaime
>> >>
>> >>
>> >
>> >
>>
>|||Please refer to my post.I am stuck with the same issue.
As of now I have been able to run Oracle SP in Generic designer.But when I
deploy it on reporting services, does not populate with value at all.
my post is at
http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?pg=2&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&fltr=
"Jaime Stuardo" wrote:
> Thanks bruce...You were right, I'm using Oracle 9i client trying to connect
> to Oracle 7.3.4 database. I have finally solved the problem using OLEDB
> provider. But the solution wasn't that trivial. When I deployed the report
> to IIS, I got so many different and strange errors. All that errors were due
> to permissions problems of the IUSR_machine user to Oracle directory. At
> last, I could configure all so that I can view reports both in preview mode
> and in web. Thanks again.
> Jaime
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OflioZafFHA.3256@.TK2MSFTNGP12.phx.gbl...
> >I remember your previous post. I believe you are going against a 7.x
> >database? I assume you do have a recent client installed. What client are
> >you using? MS requires 8i or greater client. It could be something to do
> >with the combination here. MS managed provider, Oracle 8i or greater
> >client, but a very old Oracle database. My suggestion is to stop trying to
> >use the managed provider and either use OLEDB or use ODBC. I use ODBC
> >against Sybase and the performance is not a problem. In most cases the
> >amount of time in rendering exceeds the time retrieving the data by a
> >significant amount.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
> > news:BCACE9D7-9F57-4C2A-869F-4BC54F61A43D@.microsoft.com...
> >> Thanks Bruce and Robert for explanations. I use Generic Designer and
> >> replaced
> >> @. by :. When I ran the query, I was finally asked to enter parameters.
> >> All
> >> that was fine, but when query tried to execute, I got the error "Fetch
> >> out of
> >> sequence" :-( as I asked before in this newsgroup.
> >>
> >> What you said makes sense for me now, because when I ran the query in
> >> Visual
> >> Designer, it works (using unnamed parameters). That was because it is
> >> using
> >> OLEDB provider and the others Oracle provider.
> >>
> >> In one of the tests I've made (to try to solve "Fetch out of sequence"
> >> error), I have configured the connection to be OLEDB and queries ran, but
> >> I
> >> had problems with parameters. I used "?", but I got very confused about
> >> the
> >> usage of the "?" given by the designer. I tried to rename those ? (in
> >> Parameters tab of the DataSet) to something more meaningful, but in that
> >> way,
> >> parameters didn't work, so I got back to test using Oracle Provider.
> >>
> >> Do you know why I get that error when I run the query? I have read that
> >> this
> >> error may occur when Autocommit property of the provider is set to true
> >> and
> >> SELECT FOR UPDATE instruction is used, but this is not the case.
> >>
> >> Jaime
> >>
> >> "Bruce L-C [MVP]" wrote:
> >>
> >> Not to confuse things but VDT is 4 panes and GQD is 2 panes.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> >> news:%23%23DFHqYfFHA.3584@.TK2MSFTNGP09.phx.gbl...
> >> > Just a few additions to what Bruce said already:
> >> >
> >> > Managed Oracle provider (named parameters):
> >> > select * from table where ename = :parameter
> >> > OleDB for Oracle (unnamed parameters):
> >> > select * from table where ename = ?
> >> >
> >> > The managed Oracle data provider uses a ':' to mark named parameters
> >> > (instead of '@.'); the OleDB provider for Oracle only allows unnamed
> >> > parameters (using '?'). The following KB article explains more
> >> > details:
> >> > http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
> >> >
> >> > Note: the Visual Data Tools (VDT) query designer (2 panes) actually
> >> > uses
> >> > OLE DB in the preview pane. The text-based generic query designer
> >> > (GQD; 4
> >> > panes) uses the .NET provider for Oracle. Generally, you will achieve
> >> > better results when using GQD with Oracle.
> >> >
> >> > -- Robert
> >> > This posting is provided "AS IS" with no warranties, and confers no
> >> > rights.
> >> >
> >> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> >> > news:O4c3omYfFHA.2444@.tk2msftngp13.phx.gbl...
> >> >> Oracle has a few unique things going on. First, my recommendation is
> >> >> to
> >> >> use the generic data designer (2 panes). The button to switch to this
> >> >> is
> >> >> to the right of the ...
> >> >>
> >> >> Second, because the development environment was not designed for
> >> >> managed
> >> >> providers they got tricky with what is used under the covers (hence
> >> >> my
> >> >> recommendation to use the generic designer). Here is a description
> >> >> from
> >> >> Robert Bruckner [MSFT].
> >> >> /Snip
> >> >> Note: the behavior of PREVIEW in Report Designer is identical to the
> >> >> ReportServer behavior! However the DATA view in Report Designer is
> >> >> different for the visual designer: * the visual query designer with
> >> >> 4
> >> >> panes will internally always use OleDB providers for verifying and
> >> >> executing queries directly in "Data" view. (Main reason: the visual
> >> >> query
> >> >> designer does not work with managed providers). Example: if you
> >> >> choose
> >> >> "Oracle" in the data source dialog, the Data view has to use the
> >> >> OleDB
> >> >> provider for Oracle behind the scenes, but Preview and Server will
> >> >> use
> >> >> the managed Oracle provider. The generic text-based query designer (2
> >> >> panes) will _always_ use the data provider you specified.
> >> >> /End Snip
> >> >>
> >> >> Just a little background for you. OK, now, from the generic query
> >> >> designer. He then had this to say about stored procedures:
> >> >> /Snip
> >> >> In addition, how do you return the data from your stored procedure?
> >> >> Note:
> >> >> only an out ref cursor is supported. 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.
> >> >> /End Snip
> >> >>
> >> >> Hope that helps. Definitely not intuitive but it works.
> >> >>
> >> >> One last thing. The MS managed provider for Oracle need 8.1.7 or
> >> >> higher
> >> >> (8i) client installed for it to work.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:C534517B-A9A4-483E-AB41-2348FC3914AC@.microsoft.com...
> >> >> Hi all...
> >> >>
> >> >> I have an Oracle Type connection whose connection string is only:
> >> >> data source=Pluton.
> >> >>
> >> >> I created a dataset with a query parameter, this way:
> >> >>
> >> >> SELECT NUMPOL
> >> >> FROM POLIZA
> >> >> WHERE NUMPOL = @.NUMERO_POLIZA
> >> >>
> >> >> Here I have got some problems:
> >> >>
> >> >> 1.- The parameter wasn't created automatically, so I had to go to
> >> >> the
> >> >> query
> >> >> properties and under Parameters tab I added:
> >> >>
> >> >> Name: @.NUMERO_POLIZA
> >> >> Value: =Parameters!NUMERO_POLIZA.Value
> >> >>
> >> >> NUMERO_POLIZA parameter is defined this way:
> >> >>
> >> >> Name: NUMERO_POLIZA
> >> >> Prompt: "Número de Póliza"
> >> >> Data Type: Integer
> >> >> Available values: non-queried (list empty)
> >> >> Default values: None
> >> >>
> >> >> 2.- When I run the query, a popup dialog is shown that lets me to
> >> >> define
> >> >> Query Parameters. @.NUMERO_POLIZA is listed there with a combobox at
> >> >> the
> >> >> Parameter Value column. I entered a number in that field and pressed
> >> >> OK.
> >> >> Immediately an popup error is shown: ORA-00936: missing expression
> >> >>
> >> >> It seems that the query reachs Oracle provider with the name
> >> >> @.NUMERO_POLIZA,
> >> >> not the value of it.
> >> >>
> >> >> I tried using OLEDB provider instead. In that case, query parameters
> >> >> are
> >> >> specified using "?" (interrrogation mark). When I used it and ran
> >> >> the
> >> >> query,
> >> >> after specifying the query parameter value, query is executed
> >> >> correctly.
> >> >> No
> >> >> problem, but when I preview the report, and enter the parameter,
> >> >> #Error
> >> >> word
> >> >> appears instead of a field resulting from the query.
> >> >>
> >> >> Any help would be greatly appreciated (I want the Oracle type
> >> >> connection
> >> >> to
> >> >> work, since I have read this is the most efficient method)
> >> >>
> >> >> Thanks
> >> >> Jaime
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
> >
> >
>
>

problem creating a sqldatasource connection

I am able to connect but when I try to use the advanced sql generation options the two check boxes are non enabled (generate insert, update, and delete statements

use optimistic concurrency

what is happening the user id has permissions to update/delete/select from the selected table

Are you using a SQL statement that has a JOIN in it? The sql generator does not work with joined tables.

problem copying database from sql server 2000 to sql server 2005

Hi,

I have right now sql server 2000 instance installed on a server (serverone) which is accessed on all nodes through an ODBC connection.

Now we have upgraded our server to windows server 2005 which will be on other machine servertwo. I have right now installed sql server 2005 in servertwo. Wish to copy the database from serverone to servertwo. Once it is copied and everything is working fine. I want to uninstal sql server 2000 from serverone.

What is the best way to do this? I am trying different things like tried running code Advisor on serverone which is not recognising the sql server 2005 instance of servertwo. It's giving message as since the sql server is installed on default settings it can't be accessed remotely. I see that SQL server 2000 database is accessible on all nodes including servertwo. Why is it that servertwo sql server is no where accessible?

What it is that I am missing?

Kindly help me.

Thanks, regards.

Shobha

You're really asking two questions, I think.

1. Why is servertwo not accessible?

With SQL Server 2005, we disabled a number of features by default to provide better security. You can easily adjust the settings at any time by using the Surface Area Configuration tool, available from the Start menu.

2. How do I move my databases over to servertwo?

There are several ways to do this. One way is to use sp_detach_db on serverone, then copy mdf and ldf files to servertwo and use sp_attach_db.

Paul

|||

Thanks for the reply Paul.

Is it that the datatbase should be exclusively available when doing sp_detach_db?

thanks

Shobha

|||

Hmm, good question, and a glance at BOL didn't pop the answer.

I don't believe you need exclusive access; I assume the operation is transactional so it'll wait for open transactions to complete before executing. But as I said above, the documentation of sp_detach_db doesn't make this clear. The documentation does list a few other requirements (e.g., if the database is replicated, it must be unpublished).

|||

Thanks Paul for that answer.

If you don't mind can I have sample code or instruction list on how to use the sp_deattach_db and sp_attach_db commands, this will be the first time that I will be using these commands since earlier I was working only with MSAccess database.

will be greatful if you can give me directions.

thanks

Problem consuming SQL Server SSL Endpoint

I try to consume a SSL Endpoint that is locate in localhost, but i get the follow error msg:

The underlying connection was closed: An unexpected error occurred on a receive.

m_safeCertContext is an invalid handle.

I do not know what gone wrong with my VS2005, i tried in IE with https://localhost/test?wsdl, and it was okay and display the wsdl document successfully. Beside i also tried with CLEAR port, everything is ok and i managed to consume the web service too.

any suggestion would be appreciated. Thank q

You might have more luck asking in the Visual Studio Forum for this. Seems to be a VS issue. However, looking at some of the forums, I was able to find http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=232760&SiteID=1.

HTH

Riyaz

|||

I would also recommend specifying the machine name in the URL in your VS2005 application. I would imagine that the SSL certificate installed on the SQL Server machine is issued to the machine name and not "localhost". One possibility is that since you specified "localhost" in the URL, Visual Studio may be trying to validate the SSL certificate info (machine name) against "localhost" which do not match; thus causing the error.

Jimmy