Showing posts with label returns. Show all posts
Showing posts with label returns. Show all posts

Sunday, March 11, 2012

CLR test script SELECT returns no row data

Hi,

The test.sql scripts I write to test CLR stored procedures run successfully, but when I want to display the resulting data in the database with a simple "SELECT * from Employee"

I get the result as:
Name Address
- -
No rows affected.
(1 row(s) returned)

But not the actual row is displayed whereas I would expect to see something like:

Name Address
- -
John Doe
No rows affected.
(1 row(s) returned)

I have another database project where doing the same thing displays the row information but there doesn't seem to be a lot different between the two.

Why no results in first case?

Thanks,
Bahadir
You maybe still have the transaction open and uncommitted, thats why you don′t see the actual row.

HTH; Jens SUessmeyer.

http://www.sqlserver2005.de

CLR Table Value Function Insert Into Table Variable

I have a simple clr tvf that splits a string and returns a two column table. When I try and insert the results of this tvf into another table variable I get the error below. Can anyone help me on this one? What a huge letdown if clr tvf cannot be insert into table variables. Inserting into a temp table works fine.

-- BTW I am on the September CTP with VS 2005 RC

Thanks,
Adam

Error Message

Msg 8624, Level 16, State 1, Line 2

Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

Below is the SQL I am using to test

declare @.t table(a int, b nvarchar(128))
insert into @.t
select * from dbo.Split('Hello,GoodBye', ',', 1)

Function Definition

CREATE FUNCTION [dbo].[Split](@.value [nvarchar](4000), @.seperator [nvarchar](32) = N',', @.removeEmptyEntries [bit] = 1)
RETURNS TABLE ([Position] [int] NULL,[Value] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Sit.Sql.Cdw].[Sit.Sql.Cdw.Split].[InitMethod]
GO

Source Code

using System;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;

namespace Sit.Sql.Cdw
{
public class Split
{
public struct Splitter
{
public int pos;
public String value;
}

[SqlFunction(FillRowMethodName= "FillRow", Name="Split", TableDefinition="Position int, Value nvarchar(1000)")]
public static IEnumerable InitMethod(String value, String seperator, bool removeEmptyEntries)
{
Splitter[] sVals;
String[] vals = value.Split( new string[1] { seperator }, (removeEmptyEntries) ?
StringSplitOptions
.RemoveEmptyEntries : StringSplitOptions.None);
sVals = new Splitter[vals.Length];
for(int i = 0; i < vals.Length; i++)
{
sValsIdea.pos = i + 1;
sValsIdea.value = valsIdea;
}
return sVals;
}

public static void FillRow(Object obj, out SqlInt32 Position, out SqlChars Value)
{
Splitter s = (Splitter)obj;
Position = new SqlInt32(s.pos);
Value = new SqlChars(s.value);
}
}
}

I just realized my mistake after posting...
I originally called the function with default parameters dbo.Split('Hello,Goodbye', default, default) which produces the error. Substituting the default parameter with an actual value runs fine.

CLR Stored procedure consume webservice

Can you have a CRL stored procedure call a webservice that returns a dataset?

If you download and install the SQL Server samples from http://msdn2.microsoft.com/en-us/sql/aa336343.aspx you'll find there is a sample called CurrencyWebService (typically it installs to systemdrive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\CurrencyWebService). This sample demonstrates exposing the results of invoking a web service in server-side code as a table valued function. The simple web service provided does in fact return a data set. You can read more about this sample at http://msdn2.microsoft.com/en-us/library/ms365190.aspx.

--Bonnie [MSFT]

|||To do this:
Create a class from an existing webservice using wsdl