Calling Stored Procedure

I have a created a report that needs to call a sybase Stored Procedure, my connection string is fine. I am able to call the sp from sql advantage passing in several parameters and it runs just fine. However when I created a dataset and tried to call it through the reporting service I get the following error message.

An error occurred while retrieving the parameters in the query. Unknown SQL type - 28265. I cannot seem to find any information about this error. Does anyone have any suggestions on how to call a sp from sybase?I solved the problem!!!!|||

I am calling the sybase stored procedure i am getting string to int32 conversion error in Reporting Server 2005.

if anyone got any solution, please let me know. Thanks in advance

|||Would you like to share the answer?|||

I can't solve the same problem. Please, share the answer!

Calling Stored Procedure

I have a created a report that needs to call a sybase Stored Procedure, my connection string is fine. I am able to call the sp from sql advantage passing in several parameters and it runs just fine. However when I created a dataset and tried to call it through the reporting service I get the following error message.

An error occurred while retrieving the parameters in the query. Unknown SQL type - 28265. I cannot seem to find any information about this error. Does anyone have any suggestions on how to call a sp from sybase?I solved the problem!!!!|||

I am calling the sybase stored procedure i am getting string to int32 conversion error in Reporting Server 2005.

if anyone got any solution, please let me know. Thanks in advance

|||Would you like to share the answer?|||

I can't solve the same problem. Please, share the answer!

Calling Stored Procedure

I have a created a report that needs to call a sybase Stored Procedure, my connection string is fine. I am able to call the sp from sql advantage passing in several parameters and it runs just fine. However when I created a dataset and tried to call it through the reporting service I get the following error message.

An error occurred while retrieving the parameters in the query. Unknown SQL type - 28265. I cannot seem to find any information about this error. Does anyone have any suggestions on how to call a sp from sybase?I solved the problem!!!!|||

I am calling the sybase stored procedure i am getting string to int32 conversion error in Reporting Server 2005.

if anyone got any solution, please let me know. Thanks in advance

|||Would you like to share the answer?|||

I can't solve the same problem. Please, share the answer!

Calling Stored Procedure

I have a created a report that needs to call a sybase Stored Procedure, my connection string is fine. I am able to call the sp from sql advantage passing in several parameters and it runs just fine. However when I created a dataset and tried to call it through the reporting service I get the following error message.

An error occurred while retrieving the parameters in the query. Unknown SQL type - 28265. I cannot seem to find any information about this error. Does anyone have any suggestions on how to call a sp from sybase?I solved the problem!!!!|||

I am calling the sybase stored procedure i am getting string to int32 conversion error in Reporting Server 2005.

if anyone got any solution, please let me know. Thanks in advance

|||Would you like to share the answer?|||

I can't solve the same problem. Please, share the answer!

Calling store procedure in Report throu VB

Dears
How to call store procedure in Crystal Report through VISUAL BASIC. i have Sp which has 3 parameters one is string others are dates parameter ,
how to to in the visual basic coding.
can any one help me passing paramerters .
Thanks
AnnuFor Vb 6:

Get rs (Recordset) calling stored procedure with parameters

After:

Dim report As CRAXDRT.report

Set report = crystal.OpenReport(App.Path & _
IIf(Right(App.Path, 1) = "\", "", "\") & _
s_Rpt & GetAdditionalNamePart(s_Rpt) & ".rpt") 'OPEN REPORT

report.Database.LogOnServerEx _
"crdb_odbc.dll", _
c_Settings.GetVal(CRYSTALDSN), _
c_Settings.GetVal(DBNAME), _
c_User.Name, _
c_User.Password, , _
c_Db.ConnectionStr
report.DiscardSavedData 'CLEARS REPORT SO WE WORK FROM RECORDSET
report.Database.SetDataSource rs 'LINK REPORT TO RECORDSET

Load frmReport
'ShowSendButton s_Rpt, frmReport.crV, frmReport.cmdSend

'crV - crystal viewer control
frmReport.crV.ReportSource = report 'LINK VIEWER TO REPORT
frmReport.crV.ViewReport

Best,

ika|||This is using VB 6 and CR 8.5 with RDC...

You can get your data from the Stored Proc through ADO and then pass the ADO recordset to Crystal:

'dsrReport is the name of your dsr file
Dim Report As New dsrReport

Set cmdData = New ADODB.Command
Set rsData = New ADODB.Recordset
cmdData.ActiveConnection = cnConnection
cmdData.CommandText = "sp_StoredProcName"
cmdData.CommandType = adCmdStoredProc
cmdData.Parameters.Refresh
cmdData.Parameters("@.Param1") = strParam1
cmdData.Parameters("@.Param2") = dtmParam2
cmdData.Parameters("@.Param3") = dtmParam3

'Required for RecordCount to return an accurate count
rsData.CursorType = adOpenDynamic
rsData.CursorLocation = adUseClient
rsData.Open cmdData

If Not rsData.EOF Then
Report.DiscardSavedData
Report.Database.SetDataSource rsData, 3, 1
End Ifsql

Calling SQL Stored Procedure from ASP

Hi friends, i want to call a SP from ASP (not dot net) that returns some man
y
rows.
The conn string is:
<%
Set objCon = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objcon.connectionstring = "Provider=SQLOLEDB.1;User
ID=USER;Password=XXXX;Persist Security Info=True;Initial Catalog=opessa;Data
Source=SERVER01"
iCursorType = 1
iLockType = 3
objCon.Open
%>
Then i use the following sentence to call the SP from ASP
SQLtxt = "exec SPNAME '" & PARAM1 & "','" & PARAM2 & "','" & PARAM3 & "'"
objRS.open SQLTXT, objcon
And last, i try to print the results:
WHILE NOT ObjRS.EOF
Response.Write "" & ObjRS("XXXXX").VALUE & "" &
ObjRS("XXXXX").VALUE & "" & ObjRS("XXXXX").VALUE & " Mb" & "" &
ObjRS("XXXXX").VALUE & " Mb" & "" & ObjRS("XXXXX").VALUE & " %" & ""
ObjRS.Movenext
WEND
ObjRS.CLOSE
But i receive the following error:
ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.
Whats wrong? Anyone can help me?
Thanks in advance.Tincho wrote:
> Hi friends, i want to call a SP from ASP (not dot net) that returns
> some many rows.
Here is my canned reply on this topic:
http://groups.google.com/groups?hl=...FTNGP12.phx.gbl
Bob barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||I found the solution:
I forgot the command:
set nocount inside the stored_procedure.
Best regards!
"Bob Barrows [MVP]" wrote:

> Tincho wrote:
> Here is my canned reply on this topic:
> http://groups.google.com/groups?hl=...FTNGP12.phx.gbl
> Bob barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||Make sure the stored procedure has SET NOCOUNT ON.
"Tincho" <Tincho@.discussions.microsoft.com> wrote in message
news:D15145D5-A0CF-4BA3-B211-6532936B1E92@.microsoft.com...
> Hi friends, i want to call a SP from ASP (not dot net) that returns some
> many
> rows.
> The conn string is:
> <%
> Set objCon = CreateObject("ADODB.Connection")
> Set objRS = CreateObject("ADODB.Recordset")
> objcon.connectionstring = "Provider=SQLOLEDB.1;User
> ID=USER;Password=XXXX;Persist Security Info=True;Initial
> Catalog=opessa;Data
> Source=SERVER01"
> iCursorType = 1
> iLockType = 3
> objCon.Open
> %>
> Then i use the following sentence to call the SP from ASP
> SQLtxt = "exec SPNAME '" & PARAM1 & "','" & PARAM2 & "','" & PARAM3 & "'"
> objRS.open SQLTXT, objcon
> And last, i try to print the results:
> WHILE NOT ObjRS.EOF
> Response.Write "" & ObjRS("XXXXX").VALUE & "" &
> ObjRS("XXXXX").VALUE & "" & ObjRS("XXXXX").VALUE & " Mb" & "" &
> ObjRS("XXXXX").VALUE & " Mb" & "" & ObjRS("XXXXX").VALUE & " %" & ""
> ObjRS.Movenext
> WEND
> ObjRS.CLOSE
> But i receive the following error:
> ADODB.Recordset error '800a0e78'
> Operation is not allowed when the object is closed.
> Whats wrong? Anyone can help me?
> Thanks in advance.
>
>

Calling SQL editor dialog from application

The SQL (or query) editor in Visual Studio is a great tool. Is there a way to call the editor from my application to edit a SQL string?

This is not for the end user but as a developer tool for those who have a copy of Visual Studio. I would like to be able to visually design the SQL commands we are storing in a database.

Thanks, Dave C.

Hi Dave,
There is no direct way to launch the SQL Query editor based on a string in your application. The best you can do is save the string to a file and then use the command line parameters for loading that file into SSMS using SQLWB.EXE.
Thank you,
Bill Ramos, PM, SSMS

Calling a SP in UDF and Storing Value Return By SP In UDF

Hi EveryBody,
I need to call a Stored Procedure in a UDF.This input of SP would be a string and in turn that output Of SP would also be a string.Also i need to store the output of SP in a Calling UDF.Is it possible if Yes Please tell me How can i make It.Please help.
Thanks!!!
DheerajDECLARE @.outstring varchar(500), @.instring varchar(500)
EXEC dbo.usp_YourStoredProc @.instring,@.outstring OUTPUT
--now you can use the @.outstring in your code as you want.
|||

Hi Thanks for reply,
I have done this also but its giving me Error - "Only functions and extended stored procedures can be executed from within a function." .I think I have to make SP As Extended SP I dont know how to make it can you provide any kind of sample for it.It would get more clear to me.
Thanks,
Dheeraj Verma

|||rather than trying to convert a stored proc to extended stored proc you could convert your stored proc to a function, unless you have some DML statements in your function?

Call or Reference a Stored Procedure from a published report

Hi All,
Got a report published that uses a stored procedure which is on a different
server in our Domain. I have the correct connection string info entered via
the Report Manager / Data Sources tab, but how and where does one reference
the SP to execute it? The SP does work in BIDS.
Thanx,
~SDDid you check if the user that reads the report has the execute permission
on the SP?
What error message are you getting?
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"iamscott" <iamscott@.discussions.microsoft.com> wrote in message
news:D3B5C2A0-49AC-4786-AE1C-68990FF487D1@.microsoft.com...
> Hi All,
> Got a report published that uses a stored procedure which is on a
> different
> server in our Domain. I have the correct connection string info entered
> via
> the Report Manager / Data Sources tab, but how and where does one
> reference
> the SP to execute it? The SP does work in BIDS.
> Thanx,
> ~SD
>|||The reference to the SP is in the report. If the report uses a shared
datasource then the datasource is used to get the connection and the report
RDL specifies the stored procedure. The rendering engine then executes the
SP. There is no way to force the SP to execute other than trying to run the
report.
But, it is also possible to embed the datasource into the report rather than
used a shared datasource. If that is the case and it is wrong or has some
issue with it then you will have problems. Most people use shared data
sources.
You say it has the correct connection information. What type of credentials
are you using. Are you securely storing the credentials or are you using
integrated security. If integrated then it will be using the windows
credentials of the user running the report which might not have correct
permissions for SQL Server. I always securely stored the credentials. I use
a SQL account that I have as a read only account which is given execute
permission on any necessary stored procedures. You can also do this with a
domain account but I prefer running in mixed mode and uses a SQL account.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"iamscott" <iamscott@.discussions.microsoft.com> wrote in message
news:D3B5C2A0-49AC-4786-AE1C-68990FF487D1@.microsoft.com...
> Hi All,
> Got a report published that uses a stored procedure which is on a
> different
> server in our Domain. I have the correct connection string info entered
> via
> the Report Manager / Data Sources tab, but how and where does one
> reference
> the SP to execute it? The SP does work in BIDS.
> Thanx,
> ~SD
>

Calculation Fore color test against a percentage fails

I have a calculation which creates a field with a Format string of "Percent". This works fine.

I have a Fore color test which reads

IIf ([Measures].[Avg Capitalisation] > '85%', 65280 /*Lime*/, 255 /*Red*/)

but ALL values are coloured RED. Any clues. I suspect it is in the way I am testing the 85% threshhold.

Thanks, in advance, for any assistance.

IIf ([Measures].[Avg Capitalisation] > 0.85, 65280 /*Lime*/, 255 /*Red*/)

|||

Deepak, thanks that is working now.

Graham

Calculating total from string

I don't know if this is a right place for this kind of question, but here we go...

I have a function that gets string ("2/3*(1500.0000+(-218.3300)+(-89.7000)+(-24.0000)-585.0000)"). Problem is that this returns 0:

select 2/3*(1500.0000+(-218.3300)+(-89.7000)+(-24.0000)-585.0000)

This gives right answer:

select 2.0/3.0*(1500.0000+(-218.3300)+(-89.7000)+(-24.0000)-585.0000)

Is there easy way to fix this?

check the datatype used for the parameter/calculation... if it is integer change to float

Madhu

|||Hi,

you will have to CAST the integer values to decimal types vefore calculating, otherwise SQL Server will assume that the result will be integer as well, calculating everything using the integer type.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

No that's not the point...

SELECT 2/3 returns also 0 if you write it in Query Analyzer.

|||And there is no other ways to do it in sql server?|||

check this

declare @.i1 int

declare @.i2 int

declare @.i3 int

declare @.d1 float

declare @.d2 float

declare @.d3 float

Set @.d1=2

Set @.d2=3

set @.d3=@.d1/@.d2

print @.d3

Set @.i1=2

Set @.i2=3

set @.i3=@.i1/@.i2

print @.i3

Madhu

|||

Yes I understand what you mean, but I have only one parameter (string) that includes formula. This formula can be short or very long... example 100+100*9*(100/0.608-100) and so on...

Calculating a string

Hi guys,

this is my first message on the forum, hope you can help.

I am trying to calculate a string.

ie

declare @.Mycalcstring varchar(50)

set @.Mycalcstring = "(10-5)"

How do I calculate mystring to get the result 5.

@.Mycalcstring is built of parameters which is why it is in string format

any offersWhat about this?

create table #tmp(result int)
declare @.Mycalcstring varchar(50)
set @.Mycalcstring = '(10-5)'
insert #tmp
exec('select '+@.Mycalcstring)
select * from #tmp|||Thank you for responding,

I have tried that it query analyzer and that worked BUT
I then put in into my trigger and it fails, any clues.

Also do I have to DROP the table after use

Thank you in advance

Originally posted by snail
What about this?

create table #tmp(result int)
declare @.Mycalcstring varchar(50)
set @.Mycalcstring = '(10-5)'
insert #tmp
exec('select '+@.Mycalcstring)
select * from #tmp|||PS I get the message incorrect syntax near SELECT.

Its as if it doesn't like the varchar being passed to it.

Using this:

create table #tmp(result int)
declare @.Mycalcstring varchar(50)
set @.Mycalcstring = '(10-5)'
insert #tmp
exec('select '+@.Mycalcstring)
select * from #tmp

Will work but as soon as I try to pass a built string it errors.|||Assume that you will insert row by row:

create table test(id int,code varchar(500),result varchar(25))
go
create trigger i_test on test
for insert
as
declare @.line varchar(500)
create table #tmp(result int)
select @.line = code from inserted
insert #tmp
exec('select '+@.line)
update test set result=(select max(result) from #tmp)
where id=(select max(id) from inserted)
go
insert test select 1,'(45-32)',0
select * from test
insert test select 2,'(45-32)*65',0
select * from test

You do not need to drop #tmp - it exists only in trigger|||Thank you Snail

That worked a treat.

You are truly a genius.

We are not worthy.