Problem in getting result

I have an account field which has datatype string. I wantto onlyget thosevalues which areinbetween 0-199.

I used

selectsum(t.a_trans_amt) Creditfrom a_account a, a_transaction t

where a.a_account_numbetween'0'and'199'

and

t.a_account_id=a.a_account_id

and t.a_debit_credit_ind='C'

but this query also including thosevalues which have starting 3 digitinbetween 0-199.

I don't know how to fix this problem . Can anybody help me on this issue.

Thanks in Advance.

What about a convert:

WHEREConvert(int,a.a_account_num)between 0and 199

|||

i tried it but it gave me this error

Error converting data type varchar to bigint.

i think becoz some '-' is there in the name

|||

When you do a string comparison, you get what you have now. If you want to use integer to compare, you need to show all your data patterns. You can remove the hyphen if you think that will get correct result. Just post some of your data and let's see what we can do for you.

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 doing a backup of database on SQL server through Java code using jdbc

Problem in doing a backup of database on SQL server through Java code
using jdbc
Statement callBackupDbase = con.createStatement();
String dbackup = "BACKUP DATABASE databaseName TO DISK = 'Path for the
backup file";
if(callBackupDbase != null){
callBackupDbase.execute(dbackup);
}
I get the following error
[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot per
form a
backup or restore operation within a transaction.
Could anyone help me with that
BhagatI suggest you ask this in a jdbc group. The problem is that your code opens
a transaction and then
try to execute the backup command. See the error message. You need to make t
he jdbc API not open a
transaction for you. How you do that, I don't know, it would be a jdbc issue
, Perhaps in the
connection string, perhaps by using some other function calls in jdbc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bhagat" <bhagats_bhagat@.yahoo.com> wrote in message
news:1128354135.243884.15270@.g49g2000cwa.googlegroups.com...
> Problem in doing a backup of database on SQL server through Java code
> using jdbc
> Statement callBackupDbase = con.createStatement();
> String dbackup = "BACKUP DATABASE databaseName TO DISK = 'Path for the
> backup file";
> if(callBackupDbase != null){
> callBackupDbase.execute(dbackup);
> }
> I get the following error
> [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot p
erform a
> backup or restore operation within a transaction.
> Could anyone help me with that
> Bhagat
>|||Thanks Mr Tibor Karaszi,
I shall try in the JDBC group

Problem in doing a backup of database on SQL server through Java code using jdbc

Problem in doing a backup of database on SQL server through Java code
using jdbc
Statement callBackupDbase = con.createStatement();
String dbackup = "BACKUP DATABASE databaseName TO DISK = 'Path for the
backup file";
if(callBackupDbase != null){
callBackupDbase.execute(dbackup);
}
I get the following error
[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot perform a
backup or restore operation within a transaction.
Could anyone help me with that
Bhagat
I suggest you ask this in a jdbc group. The problem is that your code opens a transaction and then
try to execute the backup command. See the error message. You need to make the jdbc API not open a
transaction for you. How you do that, I don't know, it would be a jdbc issue, Perhaps in the
connection string, perhaps by using some other function calls in jdbc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bhagat" <bhagats_bhagat@.yahoo.com> wrote in message
news:1128354135.243884.15270@.g49g2000cwa.googlegro ups.com...
> Problem in doing a backup of database on SQL server through Java code
> using jdbc
> Statement callBackupDbase = con.createStatement();
> String dbackup = "BACKUP DATABASE databaseName TO DISK = 'Path for the
> backup file";
> if(callBackupDbase != null){
> callBackupDbase.execute(dbackup);
> }
> I get the following error
> [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot perform a
> backup or restore operation within a transaction.
> Could anyone help me with that
> Bhagat
>
|||Thanks Mr Tibor Karaszi,
I shall try in the JDBC group
sql

Problem in doing a backup of database on SQL server through Java code using jdbc

Problem in doing a backup of database on SQL server through Java code
using jdbc
Statement callBackupDbase = con.createStatement();
String dbackup = "BACKUP DATABASE databaseName TO DISK = 'Path for the
backup file";
if(callBackupDbase != null){
callBackupDbase.execute(dbackup);
}
I get the following error
[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot perform a
backup or restore operation within a transaction.
Could anyone help me with that
BhagatI suggest you ask this in a jdbc group. The problem is that your code opens a transaction and then
try to execute the backup command. See the error message. You need to make the jdbc API not open a
transaction for you. How you do that, I don't know, it would be a jdbc issue, Perhaps in the
connection string, perhaps by using some other function calls in jdbc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bhagat" <bhagats_bhagat@.yahoo.com> wrote in message
news:1128354135.243884.15270@.g49g2000cwa.googlegroups.com...
> Problem in doing a backup of database on SQL server through Java code
> using jdbc
> Statement callBackupDbase = con.createStatement();
> String dbackup = "BACKUP DATABASE databaseName TO DISK = 'Path for the
> backup file";
> if(callBackupDbase != null){
> callBackupDbase.execute(dbackup);
> }
> I get the following error
> [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot perform a
> backup or restore operation within a transaction.
> Could anyone help me with that
> Bhagat
>|||Thanks Mr Tibor Karaszi,
I shall try in the JDBC group

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 getting select data set for XML Auto, Elements into a table variable -

I have a simple select quesry but with 'for XML AUTO, ELEMENTS'. I want to put in the resulting xml string into a temporary table and then alter that string as per my requirements. But I am unable to put this XML string into a table variable. Please offer your suggestions.If I put it like

select @.l_variable = (select my sql statement for Auto, Elements)
it gives me syntax error.

I can't do a select into a temp table from my select statement for XML Auto.

I can't use select into as well.

I can't enclose my entire select statment in paranthesis to put it in a table variable.

How do I proceed?|||Here is the actual query. I am trying to get the result of the select statement into the table variable:

declare @.i_Customer_Id int
,@.i_Role_ID int
,@.i_Base_URL varchar(255)

declare @.l_XML_Table table (XML_String varchar (8000))

select @.i_Customer_Id = 10
,@.i_Role_ID = 1
,@.i_Base_URL = 'http://www.NewWebSite.com'

select
MM.Module_Description Topic_Type
,PM.Page_Description Title
,@.i_Base_URL + PM.Page_URL URL
from
Role_Page_Map RPM
,Module_Master MM
,Page_Master PM

where RPM.Role_ID = @.i_Role_ID
and RPM.Customer_Id = @.i_Customer_Id

and RPM.Page_ID = PM.Page_ID
and PM.Module_ID = MM.Module_ID

for XML Auto, Elements|||You can't select FOR XML into a table. See BOL for more information.|||That's true. However can't the resultant xml string be taken in a varchar variable as well?

problem getting result set through a stored procedure call using VB.

Problem regarding getting an XML script from a stored procedure that
returns XML string format of a select query on a temporary table
created by the stored procedure itself and values also inserted within
the stored procedure.See if this helps: http://www.sqlxml.org/faqs.aspx?faq=104
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"abc" <er.nehasinghal@.gmail.com> wrote in message
news:1121936278.733465.80930@.g47g2000cwa.googlegroups.com...
Problem regarding getting an XML script from a stored procedure that
returns XML string format of a select query on a temporary table
created by the stored procedure itself and values also inserted within
the stored procedure.sql

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 converting string to integer

Hi, I need to convert the values entered into textboxes by the users before I can insert into db.

have tried the following.

Dim eventnum As string 'tried using integer makes no difference what i declare the variable as

eventnum = Convert.ToInt32(txteventnum.Text)

This value needs to be inserted into the field event_number wich is datatype int

I get the following error message when I try to insert.

Conversion failed when converting the varchar value 'eventnum' to data type int.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'eventnum' to data type int.

Source Error:

Line 62:
Line 63: ' Execute(query)
Line 64: myCommand.ExecuteNonQuery()
Line 65:
Line 66: 'Close the connection

Source File: C:\Inetpub\loans\MemberPages\Request.aspx.vb Line: 64

Stack Trace:

[SqlException (0x80131904): Conversion failed when converting the varchar value 'eventnum' to data type int.]
 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857242
 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734854
 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192
 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380
 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
 Request.Button1_Click(Object sender, EventArgs e) in C:\Inetpub\loans\MemberPages\Request.aspx.vb:64
 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

based on where your error is occurring, i suspect that the value from the textbox was successfully converted to an integer. This leads me to think that there's something wrong with how your query is constructed. Please post the code for constructing the query.

|||

thanks for the reply here is the code for query.

'Connection String value
Dim conn As String = ConfigurationManager.ConnectionStrings("LoansConnectionString").ConnectionString

'Create a SqlConnection instance
Using myConnection As New SqlConnection(conn)
myConnection.Open()

' Specify the SQL query
Const sql As String = "insert into requests ( [User_Name], [NHI], [Event_Number], [ACC_Number], [Request_Date]) values ('username','nhi','eventnum','accnum','reqstdate' )"
', [Required_Date], [NHI], [Event_Number], [ACC_Number], [Request_Date]
'Create a SqlCommand instance
Dim myCommand As New SqlCommand(sql, myConnection)

' Execute(query)
myCommand.ExecuteNonQuery()

'Close the connection
myConnection.Close()

End Using

|||

resolved it was the query string

was

Const sql As String = "insert into requests ( [User_Name], [NHI],[Event_Number], [ACC_Number], [Request_Date]) values('username','nhi','eventnum','accnum','reqstdate' )"

now

Dim sql As String = "insert into requests ( [User_Name], [required_date],[NHI], [Event_Number], [ACC_Number], [Request_Date]) " & _
"values ('" & username & "','" & reqrddate & "','" & nhi & "'," & eventnum & ",'" & accnum & "','" & reqstdate & "' )"

But thanks for replying

|||

glad you're all set.

But, please read up onSQL Injection Attacks
A parameterized query helps protect from sql injection and resolves issues related to quoting.
http://msdn.microsoft.com/msdnmag/issues/04/09/SQLInjection/

Problem converting rows to a string

I try to accomplish the following:

I have two tables which are connected via a third table (N:N
relationship):

Table 1 "Locations"
LocationID (Primary Key)

Table 2 "Specialists"
SpecialistID (Primary Key)
Name (varchar)

Table 3 "SpecialistLocations"
SpecialistID (Foreign Key)
LocationID (Foreign Key)
(both together are the primary key for this table)

Issuing the following command

SELECT
L.LocationID , S.[Name]
FROM
Locations AS L
LEFT JOIN SpecialistLocations AS SL ON P.PlaceID = SL.LocationID
LEFT JOIN Specialists AS S ON SL.SpecialistID = S.SpecialistID

results in the following table:

LocationID | Name
1Specialist 1
1Specialist 2
2Specialist 3
2Specialist 4
3Specialist 1
4Specialist 4

Now my problem: I would like to have the following output:

LocationID | Names
1Specialist 1, Specialist 2
2Specialist 3, Specialist 4
3Specialist 1
4Specialist 4

...which is grouping by LocationID and concatenating the specialist
names.
Any idea on how to do this?

Thank you very much,
Dennis(dnsstaiger@.gmx.net) writes:
> Now my problem: I would like to have the following output:
> LocationID | Names
> 1 Specialist 1, Specialist 2
> 2 Specialist 3, Specialist 4
> 3 Specialist 1
> 4 Specialist 4
> ...which is grouping by LocationID and concatenating the specialist
> names.
> Any idea on how to do this?

This is one of the rare cases where you need to set up a cursor and
iterate. In SQL 2000 there is no defined way to do this. (There is
a shortcut, but it relies on undefined behaviour, so I don't recommend it.)

In SQL 2005, currently in beta, the story is different. There you
actually have a way to this in a set-based statement, although the
syntax is somewhat bewildering. (It's actually a by-product, of all
the XML stuff they thrown in.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I typically do things like this in my application code, i.e. while the
locationid is the same, keep tacking values onto the other column's
display in a comma delim format.|||pb648174 (google@.webpaul.net) writes:
> I typically do things like this in my application code, i.e. while the
> locationid is the same, keep tacking values onto the other column's
> display in a comma delim format.

Yes, that is also a very common advice. But people insists on asking
about how doing this in SQL, that I've given up telling them to use
application code. (And sometimes the application is not any more
sophisticated than Query Analyzer.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Can you post the SQL 2005 code that will do this? I've been hoping SQL
2005 would have an aggregate function for strings that would turn it
into a delimited string.|||pb648174 (google@.webpaul.net) writes:
> Can you post the SQL 2005 code that will do this? I've been hoping SQL
> 2005 would have an aggregate function for strings that would turn it
> into a delimited string.

Sure, here it is:

select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go

This gives you an output like:

ALFKI 10643,10692,10702,10835,10952,11011
ANATR 10308,10625,10759,10926
ANTON 10365,10507,10535,10573,10677,10682,10856

Now, I did definitely come with this on my own, but I got it from one
of the SQL Server developers.

The part that produces the comma separated list, is the text() function,
which is activated by the XML PATH('') at the bottom. The real point
of text() is probably not to produce a comma separated list, but it's
possible to do it.

Then then comma-separated list is combined with Customers through
CROSS APPLY. APPLY is another operator I have not fully digested
yet, but you use it when you want to call a table-valued functions
with parameters from other columns in the query; something you can't
do in SQL 2000.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

problem conneting to SQL server 2000 on Win2k3 from ASP page using dsn connectio

Hi,
the log in user has default database set and also in the
connection string information i am specifying database
name along with DSN.But still i am not able to
connect.Please help me in this

>--Original Message--
>Make sure that your login has its default database
>properly set. Another option is to ensure that your DSN
>is changing the database from the default to the one you
>wish to use.
>Sincerley,
>Invotion Engineering Team
>Advanced Microsoft Hosting Solutions
>http://www.Invotion.com
>
>connect.I
>responding
>pointing
>then
>connection
>.
>If you go into the DSN and choose "test connection" and enter the same id
and password you specify in your ASP page does the test work?
If you use trusted authentication in your ASP page connection string will
it work?
Use SQL Profiler and/or netmon to see exactly what is happening when you
see the "hang". It may not have anything to do with the connection, it may
be a problem with the code after the initial connection.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.