Failed Because T-SQL And CLR Types For Return Value Do Not Match. Need Help.
Oct 24, 2007
I created a function to call my CLR Object (See below). I keep getting the following error (failed because T-SQL and CLR types for return value do not match) no matter what data type I try in the function. The Dll is passing back a string data type. What am I doing wrong?
CREATE FUNCTION [dbo].[fnGenerateTheoreticalValue]
(@LiborSpread float,
@Maturity int,
@LGD float,
@OID int,
@ForwardEDF1Yr float,
@ForwardEDF2Yr float,
@ForwardEDF3Yr float,
@ForwardEDF4Yr float,
@ForwardEDF5Yr float,
@LIBOR float)
Returns nvarchar(Max)
AS
EXTERNAL NAME [SQLCLRTheoretical_Values].[SQLCLRTheoretical_Values.Theoretical].[CalcValue]
I am trying to create a CLR function to call a webservice, the CLR function return data type is double, whether I try to create this as a table valued funcion or a scalar to return a distance travelled value I am receiving the error below.
I've tried changing data types around in the CLR side and the SQL side but keep receiving the same error message:
[Microsoft.SqlServer.Server.SqlFunction(Name = "DistanceCalc")] public static Double DistanceCalc(Double SrcLat, Double SrcLong, Double DestLat, Double DestLong) { MileageWS ws = new MileageWS();
[Code] ....
Error received when try to Create function ... 1, Level 16, State 2, Procedure pcMiler, Line 6 CREATE FUNCTION for "pcMiler" failed because T-SQL and CLR types for return value do not match.
I´m exporting an ms-excel file, then I use a lookup transformation to get a field from a SQL Server 2005 table. The Lookup transformation editor, after selecting the table, shows a warning that says:
at least one mapping between a column from available input columns ans a column from available lookup columns must be defined on the columns page.
So I try to make a relationship in the Lookup transformation editor's column tab where I find the Available input columns and the available lookup columns but I get the following error:
The following columns cannot be mapped: [Department, DEP_CLEGALCODE] One or more columns do not have supported data types, or their data types do not match.
The field in SLQ Server is varchar(10) and the input field is a derived column transformation; I have tried different Data Types but I always have the same error.
The DataFlow is: ExcelSource --> Derived Column --> Lookup --> Flat file destination
I have a flatfile source with qty,title and author..i add a lookup and in that i establish relation between title and titel of pubs database..but i am getting an error.. one or more columns do not hav supported data types,or their datatypes do not match..i checked both hav dt_str,and in database pubs title is varchr..so why this eror?
hi..i'm new in sql progaming,i try to make make a query that in table field "match" return to "1"if no member record in another table and return to "0" if there is anyrecord member :extable member:member idA 12B 14Table Incoming.member note matchC bla..bla 1A bla..bla 0D bla..bla 1...... ....... .....can anyone help me please?D
I've just applied CU7 to a set of SQL2005 SP2 servers with no problems, except on one server. I've now fixed the problem, but couldn't find description of it elsewhere so post it here in case it helps anyone else with the same problem.
Here's the problem I had. The server is Windows 2003 with two instances of SQL 2005 SP2 (9.00.3186) 1) Ran the patch (SQLServer2005-KB949095-x86-ENU.exe) 2) C: drive ran out of space whilst patch was running against instance A 3) Cleared down space on C: drive 4) Patch had hung, after 45 minutes of no activity I killed it 5) Re-ran patch, it reported instance A as upgraded already and just upgraded instance B 6) After reboot instance A failed to start. Instance B started with no problem.
The service was failing to start with the following recorded in the log: Server Microsoft SQL Server 2005 - 9.00.3239.00 (Intel X86) [Ś] spid5s The resource database build version is 9.00.3186. This is an informational message only. No user action is required. spid5s Error: 5173, Severity: 16, State: 1. spid5s One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup. spid5s Error: 5173, Severity: 16, State: 1. spid5s One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup. spid5s Log file 'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAmssqlsystemresource.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously. spid5s The log cannot be rebuilt when the primary file is read-only. spid5s Error: 945, Severity: 14, State: 2. spid5s Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
I think that the patch must have got part-way through instance A, enough to mark it as being patched, but not far enough to patch the mssqlsystemresource system database (note the version numbers in the log above - 9.00.3239 vs 9.00.3186). The hotfix log records an error opening the registry, but the detailed log was overwritten by the subsequent (successful) run of the hotfix so theres no more detail available.
To fix this I copied in the mssqlsystemresource mdf and ldf files from instance B (which wasnt patched the first time round, and was subsequently patched successfully). From what I understand mssqlsystemresource is read-only and holds copies of system sprocs etc, so a copy will suffice. Plus I am assuming the SQL Server is clever enough to complain if its not happy with the instance of the file.
Note that the problem recorded elsewhere for this error, where mssqlsystemresource or other system DBs have been moved from their original installation folders, was not the cause here. Not sure if this is the 'correct' fix, but it got Instance A up and running. Obviously YMMV
Hi. We're experiencing the exact same problem described in this post: http://tinyurl.com/ynqv7o We've tried the 11 steps described there and it didn't fix the problem -- just as it didn't fix it for that person.
I've been writing this stuff for a while, and can't seem to come to the conclusion of how I should be retrieving data and assigning this data to variables.
Since i'm using SQL Server, I'm convinced that I should be using the datareaders GetSqlDouble (or whatever) function, but this would mean i need my local variables to be one of the SQL types. The problem with that is, that there will have to be lots of conversions done by me to be able to use a SQL type in my application.
For instance, I have a class where i'm retrieving dates. In order to retrieve them correctly (Null values included), I need to retrieve them with GetSqlDateTime(), then when it comes time to display the date in a table, i must first check for nulls, then convert to a string. This seems to be very cumbersome. Would I be better off just using GetDateTime(), and the .ToString method, and ignoring Sql Types all together?
so, basically, how are you guys using your sql server data? with the supplied sql types, and doing all of the post-processing work manually? I feel like i'm having trouble conveying my issue...hopefully someone knows what i mean....i'd just like some direction to save trouble in the long run, since i feel like there's got to be a better way...
I want to get Email address from sql database. But whenever I executed stored procedure I get an error message "Conversion failed when converting the nvarchar value 'xxxxxx@xxxxxx' to data type int" If I want some numeric ID it is worked.
I also change my SP like this but results same.
ALTER procedure [dbo].[findConsultantMail]
(
@PerID nvarchar(18),
@perMail nvarchar(100) OUTPUT
)
as
SELECT @perMail=PerMail FROM Personel
WHERE (PerID =cast(@PerID as numeric(18,0)))
return cast(@perMail as nvarchar(100))
How can I get a string value form stored procedure.
I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.
I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT) The sql task rowset is set to NONE. It is a OLEB connection.
I have two parameters mapped:
tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO. returnvalue output long 1
I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.
The first part of the sp is below and I set the value @i and return.
Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.
This is my function, it returns SQLDataReader to DATALIST control. How to return page number with the SQLDataReader set ? sql server 2005, asp.net 2.0
Function get_all_events() As SqlDataReader Dim myConnection As New SqlConnection(ConfigurationManager.AppSettings("...........")) Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection) myCommand.CommandType = CommandType.StoredProcedure
Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2) parameterState.Value = Request.Params("State") myCommand.Parameters.Add(parameterState)
Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4) parameterPagesize.Value = 20 myCommand.Parameters.Add(parameterPagesize)
Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4) parameterPagenum.Value = pn1.SelectedPage myCommand.Parameters.Add(parameterPagenum)
Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4) parameterPageCount.Direction = ParameterDirection.ReturnValue myCommand.Parameters.Add(parameterPageCount)
myConnection.Open() 'myCommand.ExecuteReader(CommandBehavior.CloseConnection) 'pages = CType(myCommand.Parameters("@pagecount").Value, Integer) Return myCommand.ExecuteReader(CommandBehavior.CloseConnection) End Function
Variable Pages is global integer.
This is what i am calling DataList1.DataSource = get_all_events() DataList1.DataBind()
How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck
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 :
I am trying to bring my stored proc's into the 21st century with try catch and the output clause. In the past I have returned info like the new timestamp, the new identity (if an insert sproc), username with output params and a return value as well. I have checked if error is a concurrency violation(I check if @@rowcount is 0 and if so my return value is a special number.)
I have used the old goto method for trapping errors committing or rolling back the transaction.
Now I want to use the try,catch with transactions. This is easy enough but how do I do what I had done before?
I get an error returning the new timestamp in the Output clause (tstamp is my timestamp field -- so I am using inserted.tstamp).
Plus how do I check for concerrency error. Is it the same as before and if so would the check of @@rowcount be in the catch section?
So how to return timestamp and a return value and how to check for concurrency all in the try/catch.
by the way I read that you could not return an identity in the output clause but I had no problem.
i have using BCP to output SP return data into txt file, however, when it return nothing , it give SQLException like "no rows affected" , i have try to find out the solution , which include put "SET NOCOUNT ON" command before select statement, but it doesn't help :(
anyone know how to handle the problem when SP return no data ?
I have a table having single column id and n number of rows and 1 want to know all combination of id's where sum or subtraction of any two id's is equal to 7.
I've built SSIS package and made a job to execute it automatically but it always returns an error. The job returns OK but when we looked at the Log File viewer, it conatins this error log :
Key not valid for use in specified state
Failed to decrypt protected XML node "DTS Password" with error
What's wrong with the package ? Thanks in advance.
I wrote an application using Visual Studio 2005 beta 2 which uses a SQL Express .mdf file, included in the project, for the database. After installing Visual Studio 2005 RC and the SQL Express that comes with it (I followed all of the uninstall instructions first) I can no longer add a SQL Express database to any of my projects, nor can I open the SQL Express database in my original project. When I try either of these tasks, I get the "Failed to generate user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed." I read the thread which dealt with this error, and changed the connection string for the database file that had worked before to User Instance = false. The moment I click Test Connection or OK, I get the error: "An attempt to attach an auto-named database for file C:<path to project>EngSQL.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share." (<path to project> is actually a full path, I didn't want to type the whole thing out. :) ). A search for this error turns up a solution of setting User Instance = false, which puts me in a catch 22 position.
I have log shipping set up between 2 SQL 2000 SP1 Servers on Win 2000. The db is small 10 meg, and when the restore job on the backup server fails I am getting "sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed." as the message, the maint paln on the primary server show no error. Anyone seen this before? The restore has worked 3 out of 5 times
Hello, I am writing a SP, inside the SP i have a select statement. I also have another SP, when i run it, it sends a list back with all the users that are currently online. Now in my first SP, i want to construct a select statement that returns a users data, but only for the users that are online.
This is the first SP (GetAllOnlineUsers), that produces a list with users online (just abit rewritten from the membership function total users online): SELECT u.Username FROM dbo.aspnet_Users u(NOLOCK), dbo.aspnet_Applications a(NOLOCK), dbo.aspnet_Membership m(NOLOCK) WHERE u.ApplicationId = a.ApplicationId AND LastActivityDate > @DateActive AND a.LoweredApplicationName = LOWER(@ApplicationName) AND u.UserId = m.UserId
This is my second SP, in witch i want to get the online users data: SELECT m.username, d.data1, d.data2 FROM m.userstable INNER JOIN data ON (data.username = m.username) WHERE (m.username IN (EXEC GetAllOnlineUsers)) Is it possible to exec a SP into a select statement?
It seems my sysindexes table is inaccurate on a nonclustered index. In my case the rowcount (rows and rowcnt) do not match the actual rowcount of the table. The command UPDATE STATISTICS doesn't change the rows or rowcount, adding 'FULSLCAN' won't budge rowcount either.
After I did a dbcc reindex, the number of rows matched, however, upon adding rows in the table both rows and rowcount are out of sync again.
It's a fairly straightforward table, no triggers, no computed fields, only integer, datetime, varchar and bigint columns. There's a clustered index on a bigint column and a nonclustered index on a integer column.
dbcc show_statistics show that the nonclustered index is updated and it's rows and rows sampled match the number of rows in the table (not in the sysindexes-table).
I'd like to know if I'm chasing ghosts here or if there's something very wrong here. What could be causing the counts being inaccurate? Anyone who could shed some light?
SELECT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName, PersonSkills.Skills FROM PersonSkills INNER JOIN....
How can I choose a certain value from PersonSkills.Skills?
for example, I would like to choose a value from PersonSkills.Skills that matches a certain product. I can do the Max/Min value but the selected value needs to match the product.
The tables that I have are: PersonSkills: id, ProductID, Skills
Product: id, ProductName
Ive been reading and playing around with this without success.
I want to select Property names or owner id from table A where there is no match between owner ID in table A and B , for example Property 3 in red. In table A only one person can be listed as owner of property. In table B many people can be listed as owner of the same property.
The way i script will return property 1 twice (as no match) because the owner ID from table A '123456' mismatch twice with owner ID from table B
'111111' and '222222'. I dont want Property 1 to be selected at all because the owner '123456' is listed as one of the owners in Table B
I have two tables in the same DB, one named âClientâ one named âCaseâ.
-One column inside Client is âClientIDâ, another is âClientCodeâ.
-One column inside Case is âCaseIDâ and another is âClientCodeâ, which corresponds to the ClientCode column in the previous table.
I need a file with output that looks like this CaseID+ClientID.
So, an example row from the âClientâ tableâ could be ClientID = 123, ClientCode=999. An example row from the âCase tableâ could be CaseID=555, ClientCode=999. So, I need output of 555123 .
Scenario: 2 sets of data Set 1 (200 records): firstname, lastname, address, city, phone Set 2 (100 records): firstname, lastname, address, city, phone
I run a query using phone as criteria/condition (where xx.phone=yy.phone). I got 75 matches based on the phone numbers
However, if I deleted the phone numbers from set 2 data, used below query, the return result is only 45 matches. My question is what technique that I can use to optimize the result just based on criteria like first, last, and address.
select xx.firstname, xx.lastname, yy.Firstname, yy.Lastname, xx.address, yy.address, xx.city, yy.city, yy.phone from set2 xx, set1 yy where substring(soundex(xx.Firstname),0,3)=substring(soundex(yy.Firstname),0,3) and substring(soundex(xx.lastname),0,4)=substring(soundex(yy.Lastname),0,4) and substring(xx.address,1,7)= substring(yy.address,1,7) and xx.city=yy.city;
I have a table of employees and two sites. Most employees only do shifts at the one site but may occasionally work at the other site. What i want is the details of those who have worked at both sites.
for e.g.
Employees 1 Dave 2 Peter 3 John
Site 1 Mon John Tue Peter Wed John Thu John Fri Peter
Site 2 Mon Dave Tue Dave Wed Dave Thu Dave Fri Peter
So the answer should be 2 Peter. Hope this explains the problem. Probably very obvious but it has me stumped.
In the trading (stock market) industry there is a practice of rolling up (merging) multiple trades into a single trade in an effort to save on ticket charges. The way this is done is performing a SUM() on the quantities and calculating an average price. (Average price is the SUM(Qty * Price) / SUM(Qty).
Here is my dilema: If given a set of trades, I need to loop through them and check every combination to determine which one matches the expected rolled-up final trade. In other words,
If I know that the final trade is:
15 $10
And I have the following trades in my set:
TradeId Qty Price 1 10 $10 2 7 $20 3 5 $10
I need to check the roll-up of trades (1, 2), (1, 3), and (2, 3) and determine that it final trade was made by rolling up trades 1 and 3.
In the real situation, the number of trades that I need to check is not set to a specific number.
Any help would be appreciated. Cursors, temp tables, functions, recursive calls, .NET (I am running SQL 2005 so have access to CLR) are ALL acceptable solutions...
Here is a sample SQL code (table and data) to work with.
Many a times see the below error in SQL Error log.
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ]
Is this something to do here?
Note: If I run the below statement I know that the SQL Error log entry will go off, but wanted to know the real significance of this error?
CREATE LOGIN [NT AUTHORITYANONYMOUS LOGON] FROM WINDOWS
I am administering several SQL Servers running SQL Server 2005 SP2 Build 3042. I have a common maintenance plan that runs on each of the servers. The maintenance plan runs fine on all the servers except for one. On the one server the Database Integrity check fails with the following error:
Check Database integrity on Local server connection Databases: <list of databases> Include indexes Task start: 2008-02-21T00:05:42. Task end: 2008-02-21T00:05:46. Failed0) Alter failed for Server XYZ
I created a test maintenance plan to just do the integrity check and selected one database only and this also failed with the same error message. I ran this test maintenance plan and configured it for each of the databases in question and it failed each time. If I run the DBCC manually against the databases they all report fine.
I read some of the post that talked about the Allow Updates? being set incorrectly but that does not apply to my problem since my configured and run values are set to 0.
I have a re-indexing stored procedure,yester day night it got failed "Executed as user: CONNECTSRVmyadmin. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed." what may be the reason..
