How To Return Nvarchar(max) From CLR?

Jan 17, 2008

How to return nvarchar(max) from CLR?
----------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlString Select_Description(int Obj_ID)
{

using (SqlConnection conn = new SqlConnection("context connection=true"))
{
try
{
string sql = @"Select description from tbl_detail";

SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
return (SqlString)cmd.ExecuteScalar();

}
catch (Exception e)
{
throw e;
}

finally
{
conn.Close();
}
}
}

};
----------------------

Code above work fine with any [description]< 4000 characters. Get an error if description field have values> 4000 characters.

Try to solve ==> Looking for the net they said change SqlString ==> SqlChars

Here my modify code
------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlString Select_Description(int Obj_ID)
{

using (SqlConnection conn = new SqlConnection("context connection=true"))
{
try
{
string sql = @"Select description from tbl_detail";

SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
return (SqlChars)cmd.ExecuteScalar(); //<== is this correct syntax?

}
catch (Exception e)
{
throw e;
}

finally
{
conn.Close();
}
}
}

};
------------------------

the error come when compile

Error 1 Cannot implicitly convert type 'System.Data.SqlTypes.SqlChars' to 'System.Data.SqlTypes.SqlString'. An explicit conversion exists (are you missing a cast?)

Please teach me how to solve this problem. Thank you.
I'm newbie on CLR and .NET

View 6 Replies


ADVERTISEMENT

How To Return UTF 8 String From Nvarchar Field

Mar 29, 2004

hi

i have connected my ms sql 2000 with C using ODBC

can u help me to return the utf 8 string from nvarchar field ??
how should i do it
please help!!!!!!!

View 6 Replies View Related

Why Do Varchar And Nvarchar Return Different Resultset In A Search?

Mar 7, 2008



CREATE TABLE #TEST (Keyfield varchar(30) NULL)

INSERT INTO #Test (keyfield) VALUES ('M-S Logistics');

INSERT INTO #Test (keyfield) VALUES ('Monster Racing');

INSERT INTO #Test (keyfield) VALUES ('Mueller Farms');

DECLARE @Search AS nvarchar(30), @Search2 AS varchar(30)

--Query 1

SET @Search = 'Monster Racing'

SELECT TOP(1) keyfield FROM #Test WHERE keyfield >= @Search;

--Query 2

SET @Search2 = 'Monster Racing'

SELECT TOP(1) keyfield FROM #Test WHERE keyfield >= @Search2;

-- Why does query 2 return different result than query 1



View 3 Replies View Related

Storing A Carriage Return Or VbCrLf In An NVARCHAR Column

Sep 7, 2007

Is it possible to store a carriage return value or VbCrLf in an NVARCHAR column ?

I need to display a textbox in Reporting Services using one row, but showing multiple lines as a result of a few carriage returns.

View 3 Replies View Related

Saving Carriage Return In A Nvarchar Field (SQL Server 2005)

Oct 26, 2006

Hi,

As you can see, I'm totally new at Sql Server.

I have a problem, I store text into a nvarchar field (could be a 200 or 20,000 characters long string), inside the text there are several carriage returns which I would like to preserve to later presentation, but when retreiving the data from sql server I got the "cr" as "?", also I opened the database from Sql Managment and all cr's were saved as "?".

What can I do to preserve the cr inside each field ?

Thanks in advance.

View 6 Replies View Related

Error Conversion Failed When Converting The Nvarchar Value 'xxxxxx' To Data Type Int, For Return Value

Aug 24, 2007

ALTER procedure [dbo].[findConsultantMail]

(



@PerID numeric(18,0),

@perMail nvarchar(100) OUTPUT



)

as

SELECT @perMail=PerMail FROM Personel

WHERE (PerID =@PerID)

return @perMail


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.

View 4 Replies View Related

The Parameterized Query '(@contactdate Nvarchar(4000),@dnbnumber Nvarchar(4000),@prospect' Expects The Parameter '@futureopportunity', Which Was Not Supplied.

Jan 10, 2008

HI, I am running the below method which returns this error: The parameterized query '(@contactdate nvarchar(4000),@dnbnumber nvarchar(4000),@prospect' expects the parameter '@futureopportunity', which was not supplied" Please help.Private Shared Sub InsertData(ByVal sourceTable As System.Data.DataTable, ByVal destConnection As SqlConnection)
' old method: Lots of INSERT statements Dim rowscopied As Integer = 0
' first, create the insert command that we will call over and over:
destConnection.Open()Using ins As New SqlCommand("INSERT INTO [tblAppointmentDisposition] ([contactdate], [dnbnumber], [prospectname], [businessofficer], [phonemeeting], [followupcalldate2], [phonemeetingappt], [followupcalldate3], [appointmentdate], [appointmentlocation], [appointmentkept], [applicationgenerated], [applicationgenerated2], [applicationgenerated3], [comments], [newaccount], [futureopportunity]) VALUES (@contactdate, @dnbnumber, @prospectname, @businessofficer, @phonemeeting, @followupcalldate2, @phonemeetingappt, @followupcalldate3, @appointmentdate, @appointmentlocation, @appointmentkept, @applicationgenerated, @applicationgenerated2, @applicationgenerated3, @comments, @newaccount, @futureopportunity)", destConnection)
ins.CommandType = CommandType.Textins.Parameters.Add("@contactdate", SqlDbType.NVarChar)
ins.Parameters.Add("@dnbnumber", SqlDbType.NVarChar)ins.Parameters.Add("@prospectname", SqlDbType.Text)
ins.Parameters.Add("@businessofficer", SqlDbType.NChar)ins.Parameters.Add("@phonemeeting", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate2", SqlDbType.NVarChar)ins.Parameters.Add("@phonemeetingappt", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate3", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentdate", SqlDbType.NVarChar)
ins.Parameters.Add("@appointmentlocation", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentkept", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated", SqlDbType.NVarChar)ins.Parameters.Add("@applicationgenerated2", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated3", SqlDbType.NVarChar)ins.Parameters.Add("@comments", SqlDbType.Text)
ins.Parameters.Add("@newaccount", SqlDbType.NVarChar)ins.Parameters.Add("@futureopportunity", SqlDbType.NVarChar)
' and now, do the work: For Each r As DataRow In sourceTable.RowsFor i As Integer = 0 To 15
ins.Parameters(i).Value = r(i)
Next
ins.ExecuteNonQuery()
'If System.Threading.Interlocked.Increment(rowscopied) Mod 10000 = 0 Then
'Console.WriteLine("-- copied {0} rows.", rowscopied)
'End If
Next
End Using
destConnection.Close()
End Sub

View 6 Replies View Related

Compare Nvarchar(10) With Nvarchar(1000)

Sep 4, 2007

I had this question for quite a long time.

It seems the latter one don't take any extra storage space than the previous one.

As long as the real string length is less than 10.

Is that mean the latter one not cost anything?

I once heard the different is when they are in memory. But not sure of it.

Can anyone explain it and provide some official reference on it?

Thank.

View 6 Replies View Related

Return Error Code (return Value) From A Stored Procedure Using A Sql Task

Feb 12, 2008


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.


CREATE procedure [dbo].[Marketing_extract_history_load_TEST]

@table_name varchar(200),

@i int output

as

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.

View 2 Replies View Related

How To Return SqlDataReader And Return Value (page Count) From SPROC

Jan 2, 2006

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

View 3 Replies View Related

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

Aug 28, 2007


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 5 Replies View Related

Using Output Clause And Return Timestamp And Return Value

Oct 28, 2006

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.

Thanks for help on this

SM haig

View 5 Replies View Related

Why SP Return SQLException When No Data Return ?

Jul 24, 2006

hi, good day,

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 ?

thanks in advance

View 1 Replies View Related

Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure

Aug 19, 2015

I have a stored procedure that selects the unique Name of an item from one table. 

SELECT DISTINCT ChainName from Chains

For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.

SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName

Each row of the result set returned by the stored procedure would contain:

ChainName, Array of StoreNames (or comma separated strings or whatever)

How can I code a stored procedure to do this?

View 17 Replies View Related

Nvarchar(m)

Sep 2, 2007

 
Hi All,
   The storage reserverd for nvarchar(m) is 2*m+ 2 extra bytes.
   I understood each character requires because it supports unicode format.
   Why extra two bytes are required?
 
  Regards
   Abdul
 

View 1 Replies View Related

Nvarchar(100)

Jan 31, 2007

Hello,

What is the maximum lenght when using nvarchar(MAX)?

If I want to save a really long text, should be this the data type to use?

I am using SQL 2005.

Thanks,
Miguel

View 2 Replies View Related

Nvarchar(max)

Dec 11, 2007

Does anyone know the formula to calcuate the space occupied by an nvarchar(max) column for the purposes of sizing a table?

View 1 Replies View Related

Using Nvarchar(MAX) Instead Of Nvarchar(#)

Feb 19, 2008

Is it bad form to use nvarchar(MAX) in place of column types with specific lengths like nvarchar(50)? Are there performance issues? Because to me (a novice), it appears that I would save space by using nvarchar(MAX) everywhere.

Same question applies to varbinary(MAX) as well.

Thanks

Jim

_______________
Jim Geurts
Personal: http://www.biasecurities.com
Work: http://propertycenteronline.com

View 3 Replies View Related

NVARCHAR(MAX)

May 22, 2008



I have an SSIS pkg, that gets data from Lotusnotes db and loads into SQL Server, using ODBC Driver for Notessql driver . I have a comments, field, in Lotusnotes which has comments>8000 chars in lotusnotes. Hence I created my destn SQL Table with datatype NVARCHAR(MAX) so that it can uplaod the comments that are >8000 chars.
However, every time I run the ssis pkg , the destn table is bringing only 250 chars ONLY in the comments field , and truncates the rest of the chars.
And I see the reason is because, on my ODBC Serttings for Lotusnotes, There is a section for NOTESSQL options
where the "Max length of text fields" set to 254. hence it brings only 254 chars into SQL.
However, If I increase that option "Max length of text fields" to 8000 or some higher number, the sssis package errors out on the datareader source itself, err is"

"The output column 'comments' has a length that is not valid.

Pl advise how can I load all the comments which are more than 8000 chars from lotus notes into SQL, AND KEEPING THE ODBC settings, the "Max length of text fields" TO 254 Only.

View 5 Replies View Related

Order By Nvarchar

Feb 2, 2007

I have a scenario to sort on an nvarchar (50) field and I need to keep any changes to the sproc in the ORDER BY clause if possible.  This field will contain strings such as...
abc-217c, abc-15a, abc-9a, abc-7b, abc-17ar, etc...
The issue I'm having is when the records are sorted, they are returned as...
abc-15a, abc-17ar, abc-217c, abc-7b, abc-9a,etc...ordering numerically on the first numeric character in the string ie, 1,1,2,7,9)
However, I need the numeric component to be treated as a whole number and order in this fashion...
abc-7b, abc-9a, abc-15a, abc-17ar, abc-217c (7,9,15,17,217, etc)
I feel pretty sure that this issue has come up before...can anybody provide a working example that would provide a simple(or not so simple) solution?

View 2 Replies View Related

Nvarchar To Text

Aug 26, 2007

Hello, I have a column in my table that is a nvarchar.The information that we need to store in this column has exceeded the limit.Can we simply change the datatype to 'text' ?  Will there be any issues that we might experience? Thank you in advance.  

View 1 Replies View Related

Nvarchar To Varchar

Aug 28, 2007

i have used nvarchar as my datatype in sql server 2000 now
i have decided to change to varchar as i can increase the character length from 4000 to 8000
Do I Lose data if i change the datatype.

View 7 Replies View Related

Nvarchar To Varchar

Jul 10, 2003

I have a table using nvarchar(for what ever reason which beyond me why its a nvarchar...) that I would like to change to a varchar. There is no unicode in the fields so I don't have to worry about but I don't want to lose any text data. Will coverting the data type lose data?

Thanks

View 9 Replies View Related

Converting Nvarchar To Int

Oct 28, 2004

Hello all,


I'm trying to convert a nvarchar datatype to int
(ie:1234-56-78 to 12345678) . These values are primary keys in two tables. Both these tables have 3500 rows of this key type. I want to convert this to a int so I can make it a AutoNumber primary key so I can increment it. Is this possible? If so, how do I do it. Do I need to delete the dashes first some how? I fairly new to database adminstration, so any guidance will be greatly appreciated.

Thank You

View 2 Replies View Related

VARCHAR Vs NVARCHAR

May 16, 2006

So I have an existing table that looks like:


ID BIGINT
VAL VARCHAR(128)


I am converting this table to something that will be multi language compliant. My question is, I know that NVARCHAR's take double the space of a VARCHAR. Do I actually need to double the length of the VAL field to store the same amount of data or does the DB handle that?

Basically I want to store a 128 character NVARCHAR.. do I need to set my table up like this:


ID BIGINT
VAL NVARCHAR(256)

or


ID BIGINT
VAL NVARCHAR(128)

View 3 Replies View Related

NText Vs NVarChar

Sep 27, 2005

I am new to SQL Server and would like to hear opinions on pros and cons of using nText vs. nVarChar data type for following uses:

URLs (up to 260 bytes)
Addresses (50-300 bytes)
Descriptions and comments (50-2,000 bytes)
Memos (up to 8000 bytes)

TIA.

View 8 Replies View Related

NText And NVarChar.

Feb 6, 2007

Hello,

I need to save some news text in an SQL table. The text can be long.
1. Should I use nvarchar(MAX) or nText?
2. And what is the difference between nText and Text?

I am using SQL 2005.

Thanks,
Miguel

View 4 Replies View Related

Ntext To Nvarchar

Feb 15, 2008

Dear All,
i'm trying to convert the datatype from ntext to nvarchar.i'm getting error. is it not possible?

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 3 Replies View Related

Nvarchar(50) To Datetime

Feb 29, 2008

Hi all, Please help.
I have created a new SQL dB and imported a table from Access to SQL.
my date columns in Access have been imported as nvarchar(50).
all dates are in the format dd/mm/yyyy.And there is no bad data

My problem is that in the table design of my new SQL Table, if I change the datatype from nvarchar(50) to datetime I get the error "Arithmetic overflow error converting expression to data type datetime"

I know this is telling me that there is a problem with the format of some of the dates but all my dates are defo dd/mm/yyyy ,none of the data has format mm/dd/yyyy ie a 13th month.I know this because I cut the data down to just 2 pieces of data.
can someone help me get the format into datetime please
Ray..

View 8 Replies View Related

Nvarchar && Varchar

Feb 26, 2007

Hi,I am new to MS SQL. When I create a column in a table, when shall Iuse nvarchar or varchar? Please help.Thanks,Mike

View 5 Replies View Related

Parse Nvarchar To Int?

Apr 4, 2008

Is there a way to parse a string to int? this is my example code. quote_id is a string i want to be treated as an int here.




Code Snippet
ALTER PROCEDURE [dbo].[GetNextQuoteID]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT MAX(quote_id)+1 as id From Quote
END






Thanks

K

View 3 Replies View Related

Convert Nvarchar To Int

Jul 24, 2007

i have got a table



id name pagenumber(nvarchar)

1 gas pg:231-123

2 dff pg:323-123





i need to copy data from this table to another with page number as

id name pagenumber(int)

1 gas 231

2 dff 323

help me

View 18 Replies View Related

Varchar &&amp; Nvarchar

Dec 18, 2006

from the definition, i know that "n" means uni-code. but what is the exact advantage of having nchar or nvarchar over char or varchar?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved