MS SQL Server :: How To Bind Return Value From Function Call

'Return' Statement In A Function,Get,or Operator Must Return A Value....Question

I'm a Reporting Services New-Be.

I'm trying to create a report that's based on a SQL-2005 Stored Procedure.

I added the Report Designer, a Report dataset ( based on a shared datasource).

When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.

I'll only reproduce one instance of the error for the sake of brevity.

[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.

I've searched on this error and it looks like it's a Visual Basic error :

http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx

My guess is that BIDS is creating some VB code behind the scenes for the report.

I can't find any other information that seems to fit this error.

The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report.

BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables.

Can anyone help ?

Thanks,

View Replies ! View Related

Function To Call Function By Name Given As Parameter
I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz

View Replies ! View Related

Call StoreProcedure But Does Not Return Value.
I try to run the storeprocedure to get retRandomCode.Value, but it returns no value.
Using myConnection2 As New SqlConnection(connString)

myConnection2.Open()
Dim myPuzzleCmd2 As New SqlCommand("GetRandomCode", myConnection2)

myPuzzleCmd2.CommandType = CommandType.StoredProcedure
Dim retLengthParam As New SqlParameter("@Length", SqlDbType.TinyInt, 6)
retLengthParam.Direction = ParameterDirection.Input

myPuzzleCmd2.Parameters.Add(retLengthParam)
Dim retRandomCode As New SqlParameter("@RandomCode", SqlDbType.VarChar, 30)

retRandomCode.Direction = ParameterDirection.Output

myPuzzleCmd2.Parameters.Add(retRandomCode)

Try
Dim reader2 As SqlDataReader = myPuzzleCmd2.ExecuteReader()

myPuzzleCmd2.ExecuteNonQuery()Catch ex As Exception

Response.Write("sp value : " & retRandomCode.Value) <----- no value
Dim iRandomCode(1) As StringReDim Preserve iRandomCode(1)
iRandomCode(1) = Convert.ToString(retRandomCode.Value)Session.Remove("RandomCode")

HttpContext.Current.Session("RandomCode") = iRandomCode

myPuzzleCmd2 = Nothing

Finally
myConnection2.Close()

End Try

End Using

View Replies ! View Related

How To Call AS400 Stored Proc And Evaluate The Return Code?
I am trying to use SSIS to update an AS400 DB2 database by calling a stored procedure on the AS400 using an OLE DB command object. I have a select statement running against the SQL Server 2005 that brings back 20 values, all of which are character strings, and the output of this select is piped into the OLE DB command object. The call from SSIS works just fine to pass parameters into the AS400 as long as the stored procedure being called does not have an output parameter defined in its signature. There is no way that I can find to tell the OLE DB command object that one of the parameters is an output (or even an input / output) parameter. As soon as one of the parameters is changed to an output type, I get an error like this:

Code Snippet

Error: 0xC0202009 at SendDataToAs400 1, OLE DB Command [2362]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.

Error: 0xC0047022 at SendDataToAs400 1, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Command" (2362) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

Error: 0xC0047021 at SendDataToAs400 1, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0202009. There may be error messages posted before this with more information on why the thread has exited.

Information: 0x40043008 at SendDataToAs400 1, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at SendDataToAs400 1, DTS.Pipeline: Cleanup phase is beginning.

Task failed: SendDataToAs400 1

Warning: 0x80019002 at RetrieveDataForSchoolInitiatedLoans: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Warning: 0x80019002 at Load_ELEP: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Load_ELEP.dtsx" finished: Failure.

I really need to know if the call to the AS400 stored procedure succeeded or not, so I need a way to obtain and evaluate the output parameter. Is there a better way to accomplish what I am trying to do? Any help is appreciated.

View Replies ! View Related

Call A Function
Does anybody knows how to call a function from one VB source file to another VB source file??

I have create a MDI parent form, now i want to call the function of the child form from the parent form. Does anyone know this??

View Replies ! View Related

Function Call
can i make a function call from stored procedure

View Replies ! View Related

Need Help Understanding A Call To A Sql Function
Can someone help me to understand a stored procedure I am learning about? At line 12 below, the code is calling a function named"ttg_sfGroupsByPartyId" I ran the function manually and it returns several rows/records from the query. So I am wondering? does a call to the function return a temporary table? And if so, is the temporary table named PartyId? If so, the logic seems strange to me because earlier they are using the name PartyId as a variable name that is passed in.

1 ALTER PROCEDURE [dbo].[GetPortalSettings]2 (3 @PartyId uniqueidentifier,45 AS6 SET NOCOUNT ON7 CREATE TABLE #Groups8 (PartyId uniqueidentifier)910 /* Cache list of groups user belongs in */11 INSERT INTO #Groups (PartyId)12 SELECT PartyId FROM ttg_sfGroupsByPartyId(@PartyId)

View Replies ! View Related

Call Custom SQL Function In ASP.NET
I made an SQL function in MSSQL2000. This is a function that get's a calculated heat emission. When I run the Query in MSSQL2000 the function works. It calculates every emission for every row. When I call this SQL function in VS2005, it says it does not recognize the function. Does anyone know what this may cause? thank you. For the people who are bored, I added the SQL statement. The error is at the function

SELECT TOP 15 tbProducts.prod_code, tbProductProperties.prop_height, tbProductProperties.prop_length, tbProductProperties.prop_type, tbProductProperties.prop_default_emission, tbProductProperties.prop_weight, tbProductProperties.prop_water_volume, tbProductProperties.prop_n_value, GetHeatEmission(50,70,20,[prop_default_emission],[prop_n_value]) AS customEmission FROM tbProductClassification INNER JOIN tbProducts ON tbProductClassification.clprod_fk_prod_id = tbProducts.prod_id INNER JOIN tbProductProperties ON tbProducts.prod_id = tbProductProperties.prop_fk_prod_id WHERE (tbProductClassification.clprod_fk_class_id = 3327) AND (prop_height >= '030') AND (prop_height = '060') AND (prop_length

View Replies ! View Related

SQL Server Call C++ Function?
Hi,

Does anyone know if/how SQL server can call a function in a C++ library?

Cheers,

Xiaobing

View Replies ! View Related

Overload Function Call
Hi,

I want to write one function like that

dbo.function( number , 1 , 2 ) ,

but I would like to overload, and send char or number

dbo.function( number, 'abx' , ' xpto' ).

I would like to keep the same name, Can I do this? or Do I need to write to differents functions

thanks,

View Replies ! View Related

Call Function For Inner Join

I have a procedure which has query
like Query 1.

Query 1

Select Clinetid
from clinet
inner join {

select centerid from GetChildCenter(@Centerid)
union
select centerid from getParentCenter(@Centerid)
} as Center c

on c.Centerid = client.Centerid

Query 2

declare @Center table ( centerid int)
insert into @Center

select centerid from getchildCenter(@Centerid) union all select centerid from getparentcenter(@Centerid)

Select Clinetid
from clinet
inner join @Center c on c.Centerid = client.Centerid

I just want to know which one is better performance wise..
because there is millions of rows for table center which is used by function getChildCenter() and GetparentCenter()

View Replies ! View Related

Trying To Call A Function On A Weekly Timer
I'm not sure this is the place for this question, but not sure where else to go. I've written asp.net code to read from a sql server 2005 db and send out customized emails based on user info.Currently the process gets rolling by clicking a button in a web page.The client doesn't want to click a button, they want to run the email sender on a timer.How can I set up my function to run on a timer either in asp.net or more likely called from sql server?

View Replies ! View Related

Call Function From Stored Procedure
Hi All,
I'll admit that I'm not the greatest at stored procedure/functions but I want to learn as much as possible. So I have two questions:
1) I had VS2005 autogenerate a sqldatasource that created Select/Insert/Update stored procedures. When Updating a record and calling the stored procedure, I want to query another table (we'll call it tblBatchNo) that has only one record, Batchno. I want to put that current batchno into the Update statement and update the record with the current batchno. Can someone point me in the right direction? Remember that I'm still a beginner on this subject.
2) Can someone provide any links to online tutorials on t-sql?
Thanks in advance.
Curtis

View Replies ! View Related

Function Call With Table As Parameter
Hi all, I want to use a function with a tabel object as parameter. Doessomeone know a method to do this. I have read that a table as parameteris invalid.

View Replies ! View Related

Call Store Procedure From Function
Hi,is there any method to call a store procedure into a function?ThanksFabio

View Replies ! View Related

Can Delete 'dbo.' Schema When Call Function In SP
Hi:

We found the problem that when the SP call function,there must have 'dbo.' before the function.Does it necessarily?Can delete 'dbo.' schema when call function in SP?

View Replies ! View Related

How To Call A Function Using OLE DB Command Tranformation
Hello

i am trying to call a function from the SQL server using Ole DB command Transformation using [dbo].[ConvertToDate] ?,?,?,?

there are no errors while executing this transformation

but this function returns a value

Now i need to capture this value how do i do that using the OLE DB command Transformation or any other transformation

Thanks

View Replies ! View Related

Call Function In Data Flow

how can you call a sql function in data flow? I have a function that calculate age base on the data in two columns . I would like to call this function in data flow to calculate the age..

View Replies ! View Related

Trying To Call The Function A Web Service From Transact-SQL
I currently have the fllowing Stored Procedure. When I pass the the Url of the web service in the parameters, I'm having a sp_OAMethor read response failed error.

I don't know how to pass the parameter as well as the name of the function in the Web Service I'm calling. Maybe I'm all wrong here with this code too?

Thanks for any help.

ALTER PROCEDURE [dbo].[pTAPServiceWeb]

@sUrl varchar(200),

@response varchar(8000) out

AS

DECLARE @obj int

DECLARE @hr int

DECLARE @status int

DECLARE @msg varchar(255)

EXEC @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT

IF @hr < 0

BEGIN

RAISERROR('sp_OACreate MSXML2.ServerXMLHttp failed', 16, 1)

RETURN

END

EXEC @hr = sp_OAMethod @obj, 'Open', NULL, 'GET', @sUrl, false

IF @hr < 0

BEGIN

SET @msg = 'sp_OAMethod Open failed'

GOTO err

END

EXEC @hr = sp_OAMethod @obj, 'send'

IF @hr < 0

BEGIN

SET @msg = 'sp_OAMethod Send failed'

GOTO err

END

EXEC @hr = sp_OAGetProperty @obj, 'status', @status OUT

IF @hr < 0

BEGIN

SET @msg = 'sp_OAMethod read status failed'

GOTO err

END

-- IF @status <> 200

-- BEGIN

-- SET @msg = 'sp_OAMethod http status ' + str(@status)

-- GOTO err

-- END

EXEC @hr = sp_OAGetProperty @obj, 'responseText', @response OUT

IF @hr < 0

BEGIN

SET @msg = 'sp_OAMethod read response failed'

GOTO err

END

EXEC @hr = sp_OADestroy @obj

RETURN

err:

EXEC @hr = sp_OADestroy @obj

RAISERROR(@msg, 16, 1)

RETURN

GO

View Replies ! View Related

Function Call In Insert Statment
Hi

i m trying to call a function in insert statment

Insert Into (value, value1)

Value(@value, dbo.function(@value1)

dbo.function returns a value,

when i test the function in querry builder all goes fine.

In my program i become a error

"Parameterized Query '' ' expects parameter @value1 , which was not supplied."

I m using visual studio , tableadapter.update function to insert datarecords in db

thx for help

View Replies ! View Related

Function Call In Dataset Query
Hello Guys,

I have a question that seems easy but I can not figure out...

Premise:

Have Custom code that fixes Divide by Zero Errors in SSRS. I have added the code to the Custom Code area in Report Properties correctly.

I have a Dataset that has a calculation for a column within a select statement

Query Pseudocode:

select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))...
,year
from

(subquery"blah" )

Union

(Subquery"blah")

Custom Code:

Public Function SafeDiv(ByVal numerator as Double, ByVal denominator as Double) as Double
    if denominator = 0 then
        return 0
    else
        return numerator/denominator
    end if
End Function

How To use:

If you have a field that does division and you need to eliminate the divide by zero error that occurs with SSRS then type =code.SafeDiv(first,second) in the field.

Problem:

How do I add this code reference in the following dataset select statement

select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))...
,year
from

(subquery"blah" )

Union

(Subquery"blah") table1

I tried to do this:

from this:

[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income)) ...

to this

[FRC%]=code.Safediv(convert(decimal(13,2),sum(cost)),convert(decimal(13,2),sum(income))) ...

But it did not work...gave me this error:

TITLE: Microsoft Report Designer
------------------------------

An error occurred while executing the query.
Cannot find either column "code" or the user-defined function or aggregate "code.safediv", or the name is ambiguous.

------------------------------
ADDITIONAL INFORMATION:

Cannot find either column "code" or the user-defined function or aggregate "code.safediv", or the name is ambiguous. (Microsoft SQL Server, Error: 4121)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=4121&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Help!

P.S.

this is a Matrix report and this select statement is within one of the datasets that fill a matrix.

View Replies ! View Related

How To Call A Userdefined Function Within A Stored
Hello All,
How do i call a user defined function from within a stored procedure,
I have created a simple function which takes firstname and lastname as parameters and returns the concatenated name string.
That part works.

declare @fullname varchar(400)
@fullName=getFullName(@firstname,@lastname)

As always thanks for all your input

View Replies ! View Related

Problem With Nested Function Call (UDFs)
Hello Folks,I encountered a problem with SQL server 2000 and UDFs.I have a scalar UDF and a table UDF where I would like the scalar UDFto provide the argument for the table UDF like in:SELECT*FROMtransaction_tWHEREtrxn_gu_id in (select get_trxns_for_quarter(get_current_quarter( GetDate() ) ))'get_current_quarter' returns an integer which is a GUID in a tablecontaining business quarter definitions, like start date, end date.'get_current_quarter' is a scalar UDF.'get_trxns_for_quarter' will then get all transctions that fall intothat quarter and return their GUID's in a table.'get_trxns_for_quarter' is a table UDF.This doesn't seem to work at all. Regardless whether I provide thenamespace (schema) calling the scalar UDF or not. Error message isjust different.Both functions operate correctly invoked un-nested.The whole expression does work fine if I turn 'get_trxns_for_quarter'into a scalar UDF as well, e.g. by returning just one trxn_gu_id withe.g. MAX() in a scalar datatype. But of course that's no good to me.It also works fine if I select the result of 'get_current_quarter'into a variable and pass that variable into 'get_trxns_for_quarter'.But that's no good to me either since then I cannot use the wholething embedded into other SELECT clauses.Both UDF's are non-deterministic but I couldnt see how that would havean impact anyway.Never mind the syntax on that example or anyhting, I tried all theobvious and not so obvious stuff and it really seems to come down tothe fact that one UDF is scalar and the other one is not. However, Idid not come across any type of information saying that this cannot bedone.Have you any ideas?Any help would be greatly appreciated.Carsten

View Replies ! View Related

How To Call A Function From A Column Formula In My MS SQL Table
Good day!

What is the syntax on calling a function from a column formula in an MS SQL table.

I created a table, one column's value will be coming from a function. And at the same time, I will pass parameters to the function. How do I do this? Is this correct?

SELECT dbo.FunctionName([Parameter1, Parameter2])

But i can't save the table, "Error validating the formula".

Pls. help
Thanks a lot.

View Replies ! View Related

Why Does A Function Call Require Two Part Naming?
Hi,
I just found out that when I create a user defined scalar function, I must call it using dbo.[myFunctionName]. Why won't it work w/out dbo? Why are stored procedures able to use omit dbo?

Also, what is dbo specifying? I'm very unfamiliar with sql server security. Is this the user, schema, role? What's a schema? lol. Thanks.

View Replies ! View Related

How To Use The OLEDB Command To Call A Oracle Function?
HI,

I want to use the OLEDB command to call a oracle function, but i havnt found any materials about how to do that, my oracle function is as below:

CREATE OR REPLACE function GET_ZONEID_FROM_SYFZ(ycz varchar2,xc varchar2,strat_id varchar2)
return varchar2 IS
zone_id_result varchar2(10) ;
begin
PKG_DM_DQ.GET_ZONEID_FROM_SYFZ(ycz,xc,strat_id,zone_id_result);
return zone_id_result;
end;

In OLEDB command transformation component, i fill the sql command with "select GET_ZONEID_FROM_SYFZ(?,?,?) from dual", but i dont have it worked.

The error message is :provider can not derive parameter information and setparameterinfo has not been called.

Who have any idea about how to make it work?

Thanks ~~

View Replies ! View Related

Need Call A DB Function In The Middle Of The Dataflow Process

All,

I have to use a field that is calculated in a data flow process and call a database function (return a value) to do anther calculation; then return a value back to the data flow. I tried OLD DB Command but I cannot configure to return a value back to the same data flow.
If there any transformations that can call a DB function and get a value from the function in the middle of the data flow process? Need more detailed instruction.

The data flow is Like:

SourceDB Ã New_filed 1 = field1 + filed2 Ã New_filed 2= DB_function (New_filed 1) Ã Destination DB

Thanks in Advance
Jessie

View Replies ! View Related

Is Possible To Call A VB.NET Function Within Derived Transformation Editor
Hi,

In a nut shell I want to be able to instruction some Data Analysts on how to modify SSIS packages using the simpliest solutions possible. This is because there are many different data sources and some of these data sources have a huge number of fields, and yes you guessed it these data sources are subject to change on a regular basis.

A very common task they will need to do is to modify an SSIS package to do a to transform of a source date string format of "YYYYMMDD" into a date data type field within a table.

Similar threads have advised the use of the Data Flow Transformations->Derived Column for this sort of thing.

So within the Expression Text box I have inserted the following SSIS compatible SQL to convert the above string into a british format date data type; -

Code Snippet
(SUBSTRING(DOB_SRC,8,2) + "/" + SUBSTRING(DOB_SRC,5,2) + "/" + SUBSTRING(DOB_SRC,1,4))

But really what I want to be able to do is to instruct the Data Analysts to do is something like; -

ConvertTextToDate(DOB_SRC)

Where I previously defined that behaviour of ConvertTextToDate as a public VB.NET function.

Can someone please help. I'm pretty certain I'm not the only one with this type of requirement.

Thanks in advance,

Kieran.

View Replies ! View Related

Inncorrect Or Unssuported Http Function Call
I am creating a vb.net app for a windows ce handheld device. I am using replication to create the database on the handheld. I am getting this error when this error:

View Replies ! View Related

Call Excel Function - XIRR From The Report
Hi,
I need to call an excell function, xIRR, from my report.
How can I do that?

Thanks,

Igor

View Replies ! View Related

SOLVED: How To Call Scalar Function From JDBC
As an example, I have a scalar function called TRIM that takes a VARCHAR parameter, does a LTRIM(TRIM(VARCHAR)), and returns the result.

How can I call this function from java using JDBC? I have only had luck calling basic stored procedures, but I need to call functions as well.

Thanks, Ken

View Replies ! View Related

Function Call To Progress Linked Server
I have a Progress DB set up as a linked server.

To get the data through to SQL Server 2005 in a useable format i need to use the progress PRO_ELEMENT function call. How do I delimit this so it gets passed to the progress DB.

I've tried

SELECT

{fn PRO_ELEMENT(fldarr1,1,1)} as fld1

from ls1..pub.tab

This just returns an unknown function message which I believe is on the SQL Server end of the call.

This statement works fine through Business Objects.

Any help greatfully received.

View Replies ! View Related

User Defined Function To Stored Procedure Call?
Hello,

Can we call stored procedure from user defined function and vice-versa??

Thanks in advance.

View Replies ! View Related

What Type Of Permission Needed To Call ListJobs() Function
I'm working on Application that requires me to check and display status of reports running on report server. My application calling ListJobs() function of Job class part of Reporting Services Web Service API. When i run my application i'm getting insufficient previleges error. So i need to find out what type of permission i need to excute ListJobs().

This is very important part of my app. Please help me out.

Thanks,

Viral Patel

View Replies ! View Related

Setting The Column Property Description With A SQL Function Call
I am trying to figure out how to set the Description of a Column in my database table by making a SQL function call. I know that I can go into Microsoft Studio Express and type in each desciption for each column. I just have about 1000 variables and each variable's description is in an Excel spreadsheet. I want to be able to build SQL code that will set each of the 1000 variables own description.

Thanks for any help.

Wesley Marshall

View Replies ! View Related

Unsupported HTTP Function Call Was Made Error.

Hi experts,

I'm new to this forum. I have been searching around for the solution to the problem that i'm having, and I can solve it. I'm having the same problem as the title say....unsupported HTTP function call....The thing is that it worked on my development server. When I transfer all the source to a new pc and execute it, it failed with the error in the subject. I uninstalled and reinstalled everything. I ran the http://iissvrname/iisvirtualdir/sscesa20.dll and I get this "SQL Server CE Server Agent" which tell me that IIS & SQL CE are setup correctly. Here is the error log:

Source: Microsoft SQL Server 2000 windows CE edition
Number: 80004005
NativeError: 28017
Description: An incorrect or unsupported HTTP function call was made.
[,,,,,]
Param = 0
Param = 0
Param = 0
Param =
Param =
Param =

I'm running SQL 7 on the PC and using RDA to pull data from SQL7 db to the PocketPC.

Please help!!!

Thanks!

View Replies ! View Related

SSIS Hard Time Getting Back XML Return Data From Stored Procedure Call Executed By Execute SQL Task
I'm having a hard time to getting back an xml data back from a stored procedure executed by an Execute SQL task.

I'm passing in an XML data as a parameter and getting back resulting XML data as a parameter. The Execute SQL task is using ADO connection to do this job. The two parameters(in/out) are type of "string" and mapped as string.

When I execute the task, I get the following error message.

[Execute SQL Task] Error: Executing the query "dbo.PromissorPLEDataUpload" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@LogXML"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I also tried mapping the parameter as XML type, but that didn't work either.

If anyone knows what's going on or how to fix this problem please let me know. All I want to do is save returning XML data in the parameter to a local package variable.

Thanks

View Replies ! View Related

An Incorrect Or Unsupported HTTP Function Call Was Made Error
Hi All,

I've got this error "An incorrect or unsupported HTTP function call was made"

I can't browse SQL CE Agent using IE also.

How to solve this error? TQ

View Replies ! View Related

Can I Apply A Database Function Or Assembly Call In An Expression For Filter Data?
I need to translate a user€™s regional setting into one of our own language codes before I send it through as a filter to the model query. If our language codes were the same, the filter would look like this in the report filter -
                     Language Code = GetUserCulture()
Which translates to this in the database query (for us english) -
                        table.language_code = 'EN-us'
And of course I need it to look like this -
                             table.language_code = 'ENG'

I would like the logic to be globally available to all report writers (ie not forcing each report writer to have an iif or case stataement). I was thinking custom assemblies or maybe a database function, but at this level of the filter, I cannot seem to figure out how to embed a database function call to apply to the filter criteria like this
              Language Code = dbo.ConvertFcnIWrote(GetUserCulture())
Or how I would access the custom assembly in the filter expression.

Do you have a recommended implementation for this situation?

Thanks,
Toni Fielder

View Replies ! View Related

Getting A Return Value From A Function.
Im a self proclaimed newb and Im stuck on returning a value from a function. I want to get the AttendID that the SQL statement returns and dump it into strAttendID: Response.Redirect("ClassSurvey.aspx?Pupil=" & strAttendID)I cant seem to accomplish this. It returns nothing. Please help.TIA,Stue<code>Function Get_AttendID(ByVal strAttendID As String) As SqlDataReaderDim connString As String = ConfigurationSettings.AppSettings("ClassDB")Dim sqlConn As New SqlConnection(connString)Dim sqlCmd As SqlCommandDim dr As SqlDataReader
sqlConn.Open()Dim strSQL As String = "Select AttendID from attendees Where FirstName=@FirstName and LastName=@LastName and classbegdt = @classbegdt and survey = '0'"
sqlCmd = New SqlCommand(strSQL, sqlConn)
sqlCmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50)sqlCmd.Parameters("@FirstName").Value = tbFirstName.TextsqlCmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50)sqlCmd.Parameters("@LastName").Value = tbLastName.TextsqlCmd.Parameters.Add("@classbegdt", SqlDbType.DateTime, 8)sqlCmd.Parameters("@classbegdt").Value = calBegDate.SelectedDate.ToShortDateStringdr = sqlCmd.ExecuteReader()dr.Close()sqlConn.Close()
Return dr
End Function</code>

View Replies ! View Related

Function Return Value
I want to write a function that returns the physical filepath of the master database for its MDF and LDF files respectively. This information will then be used to create a new database in the same location as the master database for those servers that do not have the MDF and LDF files in the default locations.

Below I have the T-SQL for the function created and a test query I am using to test the results. If I print out the value of @MDF_FILE_PATH within the funtion, I get the result needed. When making a call to the function and printing out the variable, all I get is the first letter of the drive and nothing else.

You may notice that in the function how CHARINDEX is being used. I am not sure why, but if I put a backslash "" as expression1 within the SELECT statement, I do not get the value of the drive. In other words I get "MSSQLData" instead of "D:MSSQLData" I then supply the backslash in the SET statement. I assume that this has something to do with my question.

Any suggestions? Thank you.

HERE IS T-SQL FOR THE FUNCTION
IF OBJECT_ID('fn_sqlmgr_get_mdf_filepath') IS NOT NULL
BEGIN
DROP FUNCTION fn_sqlmgr_get_mdf_filepath
END
GO

CREATE FUNCTION fn_sqlmgr_get_mdf_filepath (
@MDF_FILE_PATH NVARCHAR(1000)--Variable to hold the path of the MDF File of a database.
)
RETURNS NVARCHAR
AS

BEGIN

--Extract the file path for the database MDF physical file.
SELECT @MDF_FILE_PATH = SUBSTRING(mdf.filename, CHARINDEX('', filename)+1, LEN(filename))
FROM master..sysfiles mdf
WHERE mdf.groupid = 1

SET @MDF_FILE_PATH = SUBSTRING(@MDF_FILE_PATH, 1, LEN(@MDF_FILE_PATH) - CHARINDEX('', REVERSE(@MDF_FILE_PATH)))

RETURN @MDF_FILE_PATH

END

HERE IS THE TEST I AM USING AGAINST THE FUNCTION
SET NOCOUNT ON

DECLARE
@MDF_FILE_PATH NVARCHAR(1000)--Variable to hold the path of the MDF File of a database.

SELECT @MDF_FILE_PATH = dbo.fn_sqlmgr_get_mdf_filepath ( @MDF_FILE_PATH )
PRINT @MDF_FILE_PATH

View Replies ! View Related

Please Help Me To Resolve The 28017 Error (An Incorrect Or Unsupported HTTP Function Call Was Made), Thanks A Lot.
Now I need to synchonize the data in Pocket PC to the remote sql server database.

I have configured the publication and the virtual directory in the server. When the Pocket PC directly connects to the server (that to say, the Pocket PC connects to the server through the ActiveSync), the data can be synchronized successfully. But when the Pockec PC connects to another computer () through the ActiveSync (this computer connects to the server through the network), the data cannot be synchronized. But when I run the application in the emulator in the same computer, the data can be synchonized with the server.

using the following codes, the error message is:

Error Code: 80004005

Message:

Minor Err: 28017

Source: Microsoft SQL Server 2005 Mobile Edtion.

private void ShowErrors(SqlCeException ex)
        {
            SqlCeErrorCollection oErrors = ex.Errors;

            StringBuilder oStrBld = new StringBuilder();

            Exception oInner = ex.InnerException;

            foreach (SqlCeError oErr in oErrors)
            {
                oStrBld.Append("Error Code: " + oErr.HResult.ToString("X"));
                oStrBld.Append("Message   : " + oErr.Message);
                oStrBld.Append("Minor Err.: " + oErr.NativeError);
                oStrBld.Append("Source    : " + oErr.Source);

                foreach (int iNumPar in oErr.NumericErrorParameters)
                {
                    if (iNumPar != 0)
                        oStrBld.Append("Num. Par. : " + iNumPar);
                }
                foreach (String sErrPar in oErr.ErrorParameters)
                {
                    if (sErrPar != String.Empty)
                        oStrBld.Append(" Err. Par. : " + sErrPar);
                }
                MessageBox.Show(oStrBld.ToString(), "SqlCeException");
                oStrBld.Remove(0, oStrBld.Length);
            }
        }

The environment is:

Client:

Sql mobile (sql ce 3.0)

Server:

windows 2003 server

sql server 2000 with sp4

IIS 6

And I'm quite sure the virtual directory is correct.

Any body can help me? It's urget for me, thanks in advance.

View Replies ! View Related

How To Return 0 Instead Of Null When Using A Sum Function?
Hi,
I basically do not want to return a null value as a result of using a sum function (using sum against 0 rows).
Is there a common way to avoid this?
Thanx

View Replies ! View Related

I Need The Function To Return A Srting
Hii every one
When i use the function of (select) from the data bass it return dataset or some thing else
But I need it to return string or the data element which in the query not all the query

like

I dont need that
_____________
| Id | Name |
-----------------
| 1 | Bill |
--------------------
I dont need All of that to display But I need to display the name only in Label or textbox
like
Bill

Thanks
Maro

View Replies ! View Related

Return @@identity For Another Function
What I'm trying to do is provide a solution where users can upload an image and a description, to a database, so I'm trying to insert the title and description then return the @@identity for the image upload function which will name the image like this
image_23.jpg (23 being the @@identity) resize it and save it to specified directory

I cant seem to get the identity to return to my script.
This is my SP

CREATE PROCEDURE SP_Insertad
(
@catid int,
@subcatid int,
@areaid int,
@uid int,
@adtitle varchar(255),
@addescription varchar(1000)
)

AS
Insert Into Tbl_ad
(ad_title, ad_description,ad_area,ad_ui_id,ad_active,ad_date,ad_ct_id,ad_sc_id,ad_location)
VALUES
(@adtitle,@addescription,@areaid, @uid, 0,convert(varchar, GETUTCDATE(), 101), @catid, @subcatid, 1)

select @@identity
return
GO

I tested in query analyser, and it works fine, so It must be my code. this is my function

Sub Insert_pic(sender as object, e as eventargs)

Dim catid = Request.form("ddcats")
Dim subcatid = Request.form("subcatrad")
Dim adtitle = Request.Form("txttitle")
Dim AdDescription = Request.form("txtdescription")
Dim uid = getUID(Context.User.Identity.Name)
Dim areaid = Request.form("ddarea")
SQLConnect = new SqlConnection(ConfigurationSettings.Appsettings("mydB"))

SQLCommander = New SQLCommand("SP_INSERTad", SQLConnect)

SQLCommander.Commandtype = Commandtype.StoredProcedure

SQLCommander.Parameters.add("@adtitle", adtitle)
SQLCommander.Parameters.add("@addescription", addescription)
SQLCommander.Parameters.add("@catid", catid)
SQLCommander.Parameters.add("@subcatid", subcatid)
SQLCommander.Parameters.add("@uid", uid)
SQLCommander.Parameters.add("@areaid", areaid)

'// this section not working right, it wont write return id

Dim paramreturn as SQLParameter
paramreturn = SQLCommander.Parameters.Add("ReturnValue", SQLDBType.Int)
ParamReturn.Direction = ParameterDirection.ReturnValue

response.write(SQLCommander.Parameters("ReturnValue").Value)

SQLConnect.open()
SQLCommander.ExecuteNonQuery()
SQLConnect.close()

End sub

Can anybody see anything I missing? I appreciate any imput

View Replies ! View Related