Convert AlphaNumeric to Numeric

Hello,

I have to convert a alpha numeric value to a numeric value using query.
Is there a way to do it.

The column data type is Varchar and I am storing alpha numeric values to it.
I have to sort the column now. when I say order by [column name] it is not comming properly.

Need some solution to do it.

Regards,
GowriShankar.

Quote:

Originally Posted by gowrishankar

Hello,

I have to convert a alpha numeric value to a numeric value using query.
Is there a way to do it.

The column data type is Varchar and I am storing alpha numeric values to it.
I have to sort the column now. when I say order by [column name] it is not comming properly.

Need some solution to do it.

Regards,
GowriShankar.

What do you mean by "alphanumeric value"? Do you have non-numeric chars in the column? If yes, how do you expect it to be converted to numeric? If no, then use "convert(int, ColumnName)" or "cast(ColumnName as int)" statements.
Please post examples|||

Quote:

Originally Posted by almaz

What do you mean by "alphanumeric value"? Do you have non-numeric chars in the column? If yes, how do you expect it to be converted to numeric? If no, then use "convert(int, ColumnName)" or "cast(ColumnName as int)" statements.
Please post examples

try like this
SELECT Description
FROM ModuleSetup
ORDER BY CAST(Description AS varchar)

convert

hi,
How do I convert this into smalldatetime please?
I am doing this because there is a field of type varchar which has to go into a separate table with field of type smalldatetime.

select convert(smalldatetime, '14/10/04', 101)

This is what I have but the error is:
Conversion failed when converting character string to smalldatetime data type.

This looks like a locale issue; try executing a SET DATEFORMAT DMY before executing your convert. You might also want to give a look to Umachandar's comments in this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=597495&SiteID=1

Note the suggestion to try to use one of the ISO formats when possible.

Code Snippet

set dateformat dmy

select convert(varchar, cast('14/10/7' as datetime), 101) [a date/time]

/*
a date/time
10/14/2007
*/

select cast('14/10/7' as smalldatetime) as [converted]

/*
converted
2007-10-14 00:00:00
*/

|||

Try this:

select convert(smalldatetime, '10/14/2004', 101)

You already had smalldatetime and the system was probably expecting the MM/DD/YYYY format rather than DD/MM/YYYY

sqlsql

Conversion of int data type error?!

Hi,

I keep getting the error:

System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value '@.qty' to data type int.

When I initiate the insert and update.

I tried adding a: Convert.ToInt32(TextBox1.Text), but it didn't work..

Could someone help?

My code:

private bool ExecuteUpdate(int quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";

con.Open();

SqlCommand command = new SqlCommand();
command.Connection = con;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
Label labname = (Label)FormView1.FindControl("Label3");
Label labid = (Label)FormView1.FindControl("Label13");

command.CommandText = "UPDATE Items SET Quantityavailable = Quantityavailable - '@.qty' WHERE productID=@.productID";
command.Parameters.Add("@.qty", TextBox1.Text);
command.Parameters.Add("@.productID", labid.Text);
command.ExecuteNonQuery();

con.Close();
return true;
}

private bool ExecuteInsert(String quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";

con.Open();

SqlCommand command = new SqlCommand();
command.Connection = con;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
Label labname = (Label)FormView1.FindControl("Label3");
Label labid = (Label)FormView1.FindControl("Label13");

command.CommandText = "INSERT INTO Transactions (Usersname,Itemid,itemname,Date,Qty) VALUES (@.User,@.productID,@.Itemsname,@.date,@.qty)";
command.Parameters.Add("@.User", System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.Add("@.Itemsname", labname.Text);
command.Parameters.Add("@.productID", labid.Text);
command.Parameters.Add("@.qty", Convert.ToInt32(TextBox1.Text));
command.Parameters.Add("@.date", DateTime.Now.ToString());
command.ExecuteNonQuery();

con.Close();
return true;
}

protected void Button2_Click(object sender, EventArgs e)
{
TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;
ExecuteUpdate(Int32.Parse(TextBox1.Text) );
}

protected void Button2_Command(object sender, CommandEventArgs e)
{
if (e.CommandName == "Update")
{
TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;
ExecuteInsert(TextBox1.Text);
}
}

Thanks so much if someone can!

Jon

Hi,

I think the problem lies in your Command Text. Try this:

command.CommandText = "UPDATE Items SET Quantityavailable = Quantityavailable - " + @.qty +" WHERE productID=@.productID";

Hope this helps.

|||

Hi,

I tried it but it gave me a different error message saying qty doesnt exist.

But actually the update seems to work - I think the problem lies with the insert commands..

Thanks,

Jon

|||

In your original post, you need to remove the single quotes around @.qty.

|||

sswanner1:

In your original post, you need to remove the single quotes around @.qty.

Hi,

I put them in when I got a syntax error 'near WHERE'..

If I take them away the error comes back..

|||

command.Parameters.Add("@.qty", TextBox1.Text); //need to convert into integer like Convert.ToInt32(TextBox1.Text)

TextBox.Text is string rather than integer. You need to valify and convert it to integer.

|||

Hi,

You mean just change the update parameter to the same as the insert parameter (@.qty)?

If so, I have done that but it still gives the same error...

Thanks,

Jon

|||

int qty = 0;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");

if(TextBox1 != null)

{

qty = int.parse(TextBox1.Text);

}

catch{}

command.CommandText = "UPDATE Items SET Quantityavailable = Quantityavailable -@.qty WHERE productID=@.productID";
command.Parameters.Add("@.qty",qty);
command.Parameters.Add("@.productID", labid.Text);
command.ExecuteNonQuery();

Following my code, and do it for both methods. And, you need to do same for @.productId.|||

Hi,

che3358:

if(TextBox1 != null)

{

qty = int.parse(TextBox1.Text);

}

catch{}

Gives me a squiggly before catch{}, saying 'try' is expected? then when I type try it gives more syntax errors?

Thanks,

Jon

|||

You will have to convert TextBox1.text to int . Try using ,

int qty = int.parse(TextBox1.text);

command.Parameters.Add("@.qty",SqlDbType.Int);

command..Parameters["@.qty"].Value = qty ;

|||

My fault. It should be

if(TextBox1 != null)

{

try

{

qty = int.parse(TextBox1.Text);

}

catch{}

}

|||

Hi again,

Now it gives the error:

CS0117: 'int' does not contain a definition for 'parse'

Line 40: qty = int.parse(TextBox1.Text);

??

Thanks again!

Jon 

|||

int.Parse. Sorry.

|||

Hi,

New error:

CS0103: The name 'int32' does not exist in the current context

Line 40: qty = int32.parse(TextBox1.Text);

Cheers

Jon 

|||

it needs to be exactly as :

qty = int.Parse(TextBox1.Text);

Tim

Conversion issues on Output Columns with Script Task

I am not sure which type to use for my Script Transformation Editor output fields. I'm getting errors based on the Data Type I'm specifying for my fields.

Print Screens:

http://www.webfound.net/script_task.jpg

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Import Maintenance (mnt) File [Split HeaderRows into Columns [5176]]: Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'UInteger'.
Line 21 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 22 Column 35 through 69
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 23 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 25 Column 27 through 61

Error at Import Maintenance (mnt) File [Split HeaderRows into Columns [5176]]: Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'UInteger'.
Line 21 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 22 Column 35 through 69
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 23 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 25 Column 27 through 61

Error at Import Maintenance (mnt) File [DTS.Pipeline]: "component "Split HeaderRows into Columns" (5176)" failed validation and returned validation status "VS_ISBROKEN".

Error at Import Maintenance (mnt) File [DTS.Pipeline]: One or more component failed validation.

Error at Import Maintenance (mnt) File: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

I'm not sure if this is needed but here's the script I coded in my script task also:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim strWholeRow As String = Row.OutputHeaderRows

Row.BatchDate = CStr(strWholeRow.Substring(0, 8))

Row.NotUsed = CStr(strWholeRow.Substring(9, 32))

Row.TransactionCode = CStr(strWholeRow.Substring(33, 34))

Row.GrossBatchTotalAmount = CDbl(strWholeRow.Substring(35, 44))

Row.NetBatchTotalAmount = CDbl(strWholeRow.Substring(45, 54))

Row.BatchTransactionCount = CDbl(strWholeRow.Substring(55, 59))

Row.PNETID = CStr(strWholeRow.Substring(60, 63))

Row.PartnerCode = CDbl(strWholeRow.Substring(64, 67))

Row.Filler = strWholeRow.Substring(68, 100)

End Sub

End Class

Looking at the screenshot and the code it looks like you're trying to put a decimal number into an integer column and you simply can't do that. You'll have to change either the type of the output column (try using DT_DECIMAL) or change CDbl to CInt.

-Jamie

Conversion from type SqlInt32 to type Integer is not valid]

Hi all,

I am developing ASP.NET 1.1 application using VB.NET & SQL Server, on my machine I am using SQL Server 2000, and everything is working just fine.

The problem appears when I uploaded the site to the Host, they are using SQL Server 2005, is there any reason for this, I am using casting in the code, and I am sure there is something wrong with the hosting settings.

Any suggestions.

Best Regards

Wafi Mohtaseb

You can perform an explicit conversion between those two types

Dim xAs System.Data.SqlTypes.SqlInt32 = 5Dim yAs Integer y =CType(x,Integer)

|||

This is what I am doing in the code, and its working on my local server, the problem appears when I uploaded the site to the hosting company server.

Best regards,

|||

please post the code you are having trouble with.

|||

1Public Overrides Function Insert()As Boolean2 Dim cmdToExecuteAs SqlCommand =New SqlCommand3 cmdToExecute.CommandText ="dbo.[UserFiles_Insert]"4 cmdToExecute.CommandType = CommandType.StoredProcedure56' // Use base class' connection object7 cmdToExecute.Connection = _mainConnection89Try10 cmdToExecute.Parameters.Add(New SqlParameter("@.UserID", SqlDbType.Int, 4, ParameterDirection.Input,False, 10, 0,"", DataRowVersion.Proposed, _userID))11 cmdToExecute.Parameters.Add(New SqlParameter("@.FileName", SqlDbType.VarChar, 50, ParameterDirection.Input,False, 0, 0,"", DataRowVersion.Proposed, _fileName))12 cmdToExecute.Parameters.Add(New SqlParameter("@.FileType", SqlDbType.VarChar, 50, ParameterDirection.Input,False, 0, 0,"", DataRowVersion.Proposed, _fileType))13 cmdToExecute.Parameters.Add(New SqlParameter("@.FileSize", SqlDbType.Decimal, 9, ParameterDirection.Input,False, 18, 0,"", DataRowVersion.Proposed, _fileSize))14Dim lengthAs Integer = 015If Not _fileContent.IsNullThen16 length = _fileContent.Length17End If18 cmdToExecute.Parameters.Add(New SqlParameter("@.FileContent", SqlDbType.Image, length, ParameterDirection.Input,False, 0, 0,"", DataRowVersion.Proposed, _fileContent))19 cmdToExecute.Parameters.Add(New SqlParameter("@.FileID", SqlDbType.Int, 4, ParameterDirection.Output,False, 10, 0,"", DataRowVersion.Proposed, _fileID))2021' // Open connection.22 _mainConnection.Open()2324' // Execute query.25 cmdToExecute.ExecuteNonQuery()26Dim _fileIDAs SqlInt32

//Error ocures here27_fileID =New SqlInt32(CType(cmdToExecute.Parameters.Item("@.FileID").Value,Integer))28Return True29 Catch exAs Exception30' // some error occured. Bubble it to caller and encapsulate Exception object31Throw New Exception("UserFiles::Insert::Error occured.", ex)32Finally33' // Close connection.34 _mainConnection.Close()35 cmdToExecute.Dispose()36End Try37 End Function

conversion from 'text' to 'int' is not supported

Changing the data type of a column (from text to int) in a saved table the
following error occurs:
conversion from 'text' to 'int' is not supportedHi,
yes this is correct, SQL Server does not allow text-> int on int->text
see the explicit and implicit data type conversions chart in bol (see the
CONVERT function)
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"jrb" <jrb@.discussions.microsoft.com> wrote in message
news:5CB28555-F612-4318-AA60-7290E0DAF26D@.microsoft.com...
> Changing the data type of a column (from text to int) in a saved table the
> following error occurs:
> conversion from 'text' to 'int' is not supported
>

conversion from 'text' to 'int' is not supported

Changing the data type of a column (from text to int) in a saved table the
following error occurs:
conversion from 'text' to 'int' is not supportedHi,
yes this is correct, SQL Server does not allow text-> int on int->text
see the explicit and implicit data type conversions chart in bol (see the
CONVERT function)
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"jrb" <jrb@.discussions.microsoft.com> wrote in message
news:5CB28555-F612-4318-AA60-7290E0DAF26D@.microsoft.com...
> Changing the data type of a column (from text to int) in a saved table the
> following error occurs:
> conversion from 'text' to 'int' is not supported
>

Conversion failed when converting from a character string to uniqueidentifier. - PLEASE HE

I am trying to store a unique identifier that is text into a field in a SQL DB that is type uniqueidentifier and I get the follow error message.

Conversion failed when converting from a character string to uniqueidentifier.

My Code is shown below:

comSQL.Parameters.AddWithValue("@.PROPERTYID", Format(Request.QueryString("ID").ToString,"{0:########-####-####-####-############}"))

This has worked before but isnt' anymore. Any ideas?

jsmith3465:

comSQL.Parameters.AddWithValue("@.PROPERTYID", Format(Request.QueryString("ID").ToString,"{0:########-####-####-####-############}"))

have you tried as...

comSQL.Parameters.AddWithValue("@.PROPERTYID",New Guid(Format("werwerwerwerwerwerwerwerwerwerwe","{0:########-####-####-####-############}")))

|||

I tried adding the New Guid() and that did not solve the problem either. Anymore ideas? I am trying to convert Text to Unique Identifier for storage in SQL Server 2005.

Thanks for all of your help!

Ryan

Conversion failed when converting character string to smalldatetime data type.

Hello, I have problem with this code.(This programpresents - there is GridView tied to a SQL database that will sort the data selected by a dropdownList at time categories. There are 2 time categories in DropDownList - this day, this week.

Problem: when I choose one categorie in dropDownlist for examle this week and submit data on the server I got this error.

Conversion failed when converting character string to smalldatetime data type.

Here is code:

<%

@.PageLanguage="C#" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

scriptrunat="server">

protectedvoid DropDownList1_SelectedIndexChanged(object sender,EventArgs e)

{

string datePatt =@."yyyymmdd";

// Get start and end of day

DateTime StartDate =DateTime.Today;

string @.StartDate1 = StartDate.ToString(datePatt);

string @.EndDate = StartDate.AddDays(1).ToString(datePatt);// Get start and end of weekstring @.startOfWeek = StartDate.AddDays(0 - (int)StartDate.DayOfWeek).ToString(datePatt);string @.startOfNextWeek = StartDate.AddDays(7 - (int)StartDate.DayOfWeek).ToString(datePatt);

switch (DropDownList1.SelectedValue)

{

case"1":

// day

SqlDataSource1.SelectCommand =

"SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" +"WHERE" +"[DATE] >=" +"'@.StartDate1'" +" AND [DATE] < " +"'@.EndDate'";break;case"2"://week

SqlDataSource1.SelectCommand =

"SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" +"WHERE" +"[DATE] >=" +"'@.startOfWeek'" +"AND [DATE] <" +"'@.startOfNextWeek'";break;

}

}

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headid="Head1"runat="server"><title>Untitled Page</title><styletype="text/css">body {font:1emVerdana;

}

</style>

</

head>

<

body><formid="form1"runat="server"><div>

<asp:DropDownListID="DropDownList1"runat="server"AutoPostBack="True"OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"Style="z-index: 100; left: 414px; position: absolute; top: 22px"><asp:ListItemSelected="True"Value="1">jeden den</asp:ListItem><asp:ListItemValue="2">jeden tyden</asp:ListItem></asp:DropDownList>

<asp:GridViewID="GridView1"runat="server"Style="z-index: 102; left: 228px; position: absolute;

top: 107px"

DataSourceID="SqlDataSource1"AutoGenerateColumns="True">

</asp:GridView> <br/><br/><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="Data Source=CR\SQLEXPRESS;

Initial Catalog=MyConn;Integrated Security=True"

ProviderName="System.Data.SqlClient"></asp:SqlDataSource></div></form>

</

body>

</

html>

string datePatt =@."yyyymmdd";

In your 'date' pattern, mm is minutes.

You want MM for month: yyyyMMdd

|||Thank you, for your reply, Icorrected this error. But the problem is still here.|||

I notice you are trying to use parameters but I don't see the code that adds parameters: Parameters.Add(...).

If you pass in the date as a DateTime, you will not have to format it to a string.

|||Thank you, I will try.

Conversion failed when converting character string to smalldatetime data type

I am newbie in asp and sql, and I am using VS & SQL express

when I try to submit I get following error

"Conversion failed when converting character string to smalldatetime data type"

Following is my insert statement

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"

SelectCommand="SELECT data.* FROM data" InsertCommand="INSERT INTO data(Apps, Location, Impact, System, Date, Start_Time, End_Time, Duration, Problem, Cause, Solution, Case, Comments) VALUES ('@.DropDownList1','@.DropDownList2','@.DropDownList3','@.TextBox6','@.DropDownCalendar1','@.DropDownCalendar2','@.DropDownCalendar3','@.TextBox1','@.TextBox2','@.TextBox3','@.TextBox4','@.TextBox5','@.TextBox7')">

</asp:SqlDataSource>

These are @.DropDownCalendar1','@.DropDownCalendar2','@.DropDownCalendar3' defined as datetime in database.

I would appriciate if somebody could help.

Thanks

anybody here?|||

The issue here seems to be specific to the authoring in the ASP.Net pages...someone in the ASP.Net forum may be able to help you.

Here's a potentially useful links to start with: http://forums.asp.net/thread/881828.aspx

Conversion failed when converting character string to smalldatetime data type

I am newbie in asp and sql, and I am using VS & SQL express

when I try to submit I get following error

"Conversion failed when converting character string to smalldatetime data type"

Following is my insert statement

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"

SelectCommand="SELECT data.* FROM data" InsertCommand="INSERT INTO data(Apps, Location, Impact, System, Date, Start_Time, End_Time, Duration, Problem, Cause, Solution, Case, Comments) VALUES ('@.DropDownList1','@.DropDownList2','@.DropDownList3','@.TextBox6','@.DropDownCalendar1','@.DropDownCalendar2','@.DropDownCalendar3','@.TextBox1','@.TextBox2','@.TextBox3','@.TextBox4','@.TextBox5','@.TextBox7')">

</asp:SqlDataSource>

These are @.DropDownCalendar1','@.DropDownCalendar2','@.DropDownCalendar3' defined as datetime in database.

I would appriciate if somebody could help.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"

SelectCommand="SELECT data.* FROM data" InsertCommand="INSERT INTO data(Apps, Location, Impact, System, Date, Start_Time, End_Time, Duration, Problem, Cause, Solution, Case, Comments) VALUES (@.DropDownList1,@.DropDownList2,@.DropDownList3,@.TextBox6,@.DropDownCalendar1,@.DropDownCalendar2,@.DropDownCalendar3,@.TextBox1,@.TextBox2,@.TextBox3,@.TextBox4,@.TextBox5,@.TextBox7)">

</asp:SqlDataSource>

|||

Now I am getting following error

Must declare the scalar variable "@.DropDownList1".

How do I do it in asp, I found the syntax for declare statement but don't know how put it in asp?

Thanks

|||Go to the design view, right click the sqldatasource, choose properties, then choose the selectcommand. A dialog should open, in there you can add all your parameters for the select.|||

I did that and still getting same error

after changes it looks like this

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"

SelectCommand="SELECT data.* FROM data"InsertCommand="INSERT INTO data(Apps, Location, Impact, System, tDate, Start_Time, End_Time, Duration, Problem, Cause, Solution, DW_Case, Comments) VALUES (@.DropDownList1,@.DropDownList2,@.DropDownList3,@.TextBox6,@.DropDownCalendar1,@.DropDownCalendar2,@.DropDownCalendar3,@.TextBox1,@.TextBox2,@.TextBox3,@.TextBox4,@.TextBox5,@.TextBox7)">

<SelectParameters>

<asp:FormParameterFormField="DropDownList1"Name="@.Apps"/>

<asp:FormParameterFormField="DropDownList2"Name="@.Location"/>

<asp:FormParameterFormField="DropDownList3"Name="@.Impact"/>

<asp:FormParameterFormField="TextBox6"Name="@.System"/>

<asp:FormParameterFormField="DropDownCalendar1"Name="@.tdate"/>

<asp:FormParameterFormField="DropDownCalendar2"Name="@.Start_time"/>

<asp:FormParameterFormField="DropDownCalendar3"Name="@.End_time"/>

<asp:FormParameterFormField="TextBox1"Name="@.Duration"/>

<asp:FormParameterFormField="TextBox2"Name="@.Problem"/>

<asp:FormParameterFormField="TextBox3"Name="@.Cause"/>

<asp:FormParameterFormField="TextBox4"Name="@.Solution"/>

<asp:FormParameterFormField="TextBox5"Name="@.DW_case"/>

<asp:FormParameterFormField="TextBox7"Name="@.Comments"/>

</SelectParameters>

</asp:SqlDataSource>

I really appreciate your.

Thanks

|||I also tried doing samething with insert parameter and still no luck|||You have to call them the same thing. @.DropDownList1=@.Apps. Change one to the other and repeat for all parameters. Either change the insert statement, replacing @.DropDownList1 with @.Apps, *OR* change the parameter "@.Apps" to use the name "@.DropDownList1".|||

Motley

I really appreciate your help,

And a BIG Thanks to you.

sqlsql

Conversion ERROR

This is the error message I get: :(
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

This is the query:
Select Qual_ins.CompanyCode, Qual_ins.ParticipantCode, Qual_ins.Ins_Code, Qual_ins.Plan_Code,
dbo.PremiumRate(Qual_Ins.Crit,Qual_Ins.PQB_Spec,Pl an_Mas.Extend_Fee,
Qual_Ins.Adjpremium,Qual_Ins.Adjpremiumper,Qual_In s.Adjpremend,
GetDate(),Qual_Ins.Cover_Amt, Plan_Mas.CR_A, Plan_Mas.CR_B,
Plan_Mas.CR_C, Plan_Mas.CR_D, Plan_Mas.CR_E, Plan_Mas.CR_F,
Plan_Mas.CR_G, Plan_Mas.CR_H, Plan_Mas.CR_I, Plan_Mas.CR_J,
Plan_Mas.CR_K, Plan_Mas.CR_L, Plan_Mas.CR_M, Plan_Mas.CR_N,
Plan_Mas.CR_O) AS PremiumRate
FROM Qual_ins, Plan_Mas
WHERE Qual_Ins.CompanyCode = 'ACME'
AND Qual_ins.ParticipantCode = 4
AND Plan_Mas.CompanyCode = Qual_ins.CompanyCode
AND Plan_Mas.Ins_Code = Qual_ins.Ins_Code
AND Plan_Mas.Plan_Code = Qual_ins.Plan_Code
Order BY Qual_ins.Ins_Code

Please let me know if you need to see my PremiumRate (User Defined Function) in order to help me elimate this error message.
Any help is appreciate!
I'm new to this... "Hello" to all!

ShuviBased on the error message, I'd guess that you are trying to convert a string (CHAR or VARCHAR) to a DATETIME or a SMALLDATETIME. If that is the case, then one or more rows in your data isn't a valid date string.

-PatP

Conversion error

I am getting the error message Error converting data type varchar to float when running the following query:

Code Snippet

select top 175816
AV.intItemID,
AV.intAttrID,
-- AV.vchValue,
CAST(AV.vchValue AS float) AS Test,
0
from tblAttrVals AV
join tblAttributes AA
on AA.intAttributeID = AV.intAttrID
and AA.intDataTypeID in (2, 3)
and (1 = isnumeric (AV.vchValue))
order by AV.intItemID, AV.intAttrID

Here is what is strange. If I bump the top count down by one it succeeds. And even stranger, if I leave the top count the same and uncomment out the line in the select statement that shows the value being converted it succeeds.

Any ideas? This seems like a bug.

Chris:

Can you show us the specific data that is giving you trouble?

|||

I found the issue. It actually had nothing to do with the data that is being returned. It had to do with the data not being returned.

Here is the info from a post that helped me:

The problem is that SQL Server 2005 is more aggressive in terms of evaluating expressions in your query and moving them to different stages of the query plan. This might result in conversion error like in your case if the CAST gets computed before the WHERE clause checks. So there is no guarantee that the expressions in the WHERE clause will be computed first. This was true even in SQL Server 2000 except that you probably never hit it for your schema/data set. You can get the same error there also if the query plan changes.

To resolve the problem, you need to either correct your data model to represent the values correctly. Use float if your data is float - don't mix values from different domains. Or you will have to use CASE in the SELECT list to avoid the conversion problem. Note that using CASE expression is the only way to control order of execution of various expressions. See link below for more details (search for unsafe expressions):

http://msdn2.microsoft.com/en-us/library/ms143359.aspx

To summarize you have two solutions:

1. Fix your data model / schema so you represent the values in their proper domain (not float values in varchar and mixing various values in string)
2. Or modify your SELECT in the 2nd view to:

SELECT cast(CASE WHEN dwpId LIKE '[0-9]%' THEN dwpId END as int) as dwpId, startDate, endDate
FROM View1

Note that even above check is not entirely correct because not all values that have just numeric digits can be successfully converted to int. You might get overflow errors for example. You could use ISNUMERIC but that checks for integer, numeric, and money conversions so it will let more data through. So it is best you correct your schema to avoid all these issues.

sqlsql

Conversion

Hi,
I encounter a strange problem.Here down,I explain it.Pls anyone give me
solution.
typeID is of INT type
In a storedprocedure,when used as
typeID='+cast(23 as varchar)+'
it is getting executed but when i break the stored procedure,it is
showing error
as below:
Syntax error converting the varchar value '+CAST(23 AS VARCHAR)+' to a
column of data type int.
Thanks
SunilDutt N.Dutt
INT is highest in terms of Data Type Precedence, thuis you 've got a error.
SQL Server tries to convert a varachar (lower) to INT (higher)
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1165930651.040999.302700@.16g2000cwy.googlegroups.com...
> Hi,
> I encounter a strange problem.Here down,I explain it.Pls anyone give me
> solution.
> typeID is of INT type
> In a storedprocedure,when used as
>
> typeID='+cast(23 as varchar)+'
>
> it is getting executed but when i break the stored procedure,it is
> showing error
> as below:
> Syntax error converting the varchar value '+CAST(23 AS VARCHAR)+' to a
> column of data type int.
> Thanks
> SunilDutt N.
>|||Uri,
But,its working in the sp.
Thanks
Dutt
Uri Dimant wrote:
> Dutt
> INT is highest in terms of Data Type Precedence, thuis you 've got a error.
> SQL Server tries to convert a varachar (lower) to INT (higher)
>
>
> "Dutt" <Mr.Dutt@.gmail.com> wrote in message
> news:1165930651.040999.302700@.16g2000cwy.googlegroups.com...
> > Hi,
> > I encounter a strange problem.Here down,I explain it.Pls anyone give me
> > solution.
> >
> > typeID is of INT type
> >
> > In a storedprocedure,when used as
> >
> >
> > typeID='+cast(23 as varchar)+'
> >
> >
> > it is getting executed but when i break the stored procedure,it is
> > showing error
> > as below:
> >
> > Syntax error converting the varchar value '+CAST(23 AS VARCHAR)+' to a
> > column of data type int.
> >
> > Thanks
> >
> > SunilDutt N.
> >|||Dutt
Can you show us entire source?
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1165931759.413557.54020@.73g2000cwn.googlegroups.com...
> Uri,
> But,its working in the sp.
> Thanks
> Dutt
> Uri Dimant wrote:
>> Dutt
>> INT is highest in terms of Data Type Precedence, thuis you 've got a
>> error.
>> SQL Server tries to convert a varachar (lower) to INT (higher)
>>
>>
>> "Dutt" <Mr.Dutt@.gmail.com> wrote in message
>> news:1165930651.040999.302700@.16g2000cwy.googlegroups.com...
>> > Hi,
>> > I encounter a strange problem.Here down,I explain it.Pls anyone give me
>> > solution.
>> >
>> > typeID is of INT type
>> >
>> > In a storedprocedure,when used as
>> >
>> >
>> > typeID='+cast(23 as varchar)+'
>> >
>> >
>> > it is getting executed but when i break the stored procedure,it is
>> > showing error
>> > as below:
>> >
>> > Syntax error converting the varchar value '+CAST(23 AS VARCHAR)+' to a
>> > column of data type int.
>> >
>> > Thanks
>> >
>> > SunilDutt N.
>> >
>|||Hi Uri,
I got it.
It was a dynamic SQL statement with a lot of quotes which bring
confusion.
Thanq.
Dutt.
Uri Dimant wrote:
> Dutt
> Can you show us entire source?
>
> "Dutt" <Mr.Dutt@.gmail.com> wrote in message
> news:1165931759.413557.54020@.73g2000cwn.googlegroups.com...
> > Uri,
> > But,its working in the sp.
> > Thanks
> > Dutt
> > Uri Dimant wrote:
> >> Dutt
> >> INT is highest in terms of Data Type Precedence, thuis you 've got a
> >> error.
> >> SQL Server tries to convert a varachar (lower) to INT (higher)
> >>
> >>
> >>
> >>
> >> "Dutt" <Mr.Dutt@.gmail.com> wrote in message
> >> news:1165930651.040999.302700@.16g2000cwy.googlegroups.com...
> >> > Hi,
> >> > I encounter a strange problem.Here down,I explain it.Pls anyone give me
> >> > solution.
> >> >
> >> > typeID is of INT type
> >> >
> >> > In a storedprocedure,when used as
> >> >
> >> >
> >> > typeID='+cast(23 as varchar)+'
> >> >
> >> >
> >> > it is getting executed but when i break the stored procedure,it is
> >> > showing error
> >> > as below:
> >> >
> >> > Syntax error converting the varchar value '+CAST(23 AS VARCHAR)+' to a
> >> > column of data type int.
> >> >
> >> > Thanks
> >> >
> >> > SunilDutt N.
> >> >
> >

conversation handle

hi all, i am having a hard time getting the conversation handle id to match
..
scripts that create the relevant objects ...
create message type QueryMessage validation = none
create contract QueryContract (QueryMessage sent by initiator)
create queue QueueSender
create queue QueueReceiver
create service Sender on queue QueueSender
create service Receiver on queue QueueReceiver (QueryContract)
send with conversation handle id ...
begin transaction
declare @.conversationhandle uniqueidentifier;
select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
begin dialog @.conversationhandle
from service [Sender]
to service 'Receiver'
on contract [QueryContract]
with encryption = off;
send on conversation @.conversationhandle
message type [QueryMessage] ('blah blah blah;');
commit transaction
after i ran the above send query a few times, and do the below select ...
select conversation_group_id, conversation_handle, cast(message_body as
varchar(1000)) from QueueReceiver
i got all kinds of different conversation_group_id and conversation_handle?
should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i specified
in the send query'
and when i do the below receive query, it says:
Msg 8426, Level 16, State 20, Line 1
The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not found.
receive top(1) convert(varchar(1000),message_body) as message
from QueueReceiver
where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
please help! thanks in advance!
- tinThe conversation handle is an output parameter for the BEGIN DIALOG
statement. Each time you run the statement, a new conversation will be
created and the @.conversationhandle will get a new value assigned to it. If
the variable had a previous value, it will be overwriten.
So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
was never created, hence the error on the RECEIVE statement.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"tin" <tin@.discussions.microsoft.com> wrote in message
news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> hi all, i am having a hard time getting the conversation handle id to
> match ...
> scripts that create the relevant objects ...
> create message type QueryMessage validation = none
> create contract QueryContract (QueryMessage sent by initiator)
> create queue QueueSender
> create queue QueueReceiver
> create service Sender on queue QueueSender
> create service Receiver on queue QueueReceiver (QueryContract)
> send with conversation handle id ...
> begin transaction
> declare @.conversationhandle uniqueidentifier;
> select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> begin dialog @.conversationhandle
> from service [Sender]
> to service 'Receiver'
> on contract [QueryContract]
> with encryption = off;
> send on conversation @.conversationhandle
> message type [QueryMessage] ('blah blah blah;');
> commit transaction
> after i ran the above send query a few times, and do the below select ...
> select conversation_group_id, conversation_handle, cast(message_body as
> varchar(1000)) from QueueReceiver
> i got all kinds of different conversation_group_id and
> conversation_handle?
> should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> specified
> in the send query'
> and when i do the below receive query, it says:
> Msg 8426, Level 16, State 20, Line 1
> The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> found.
> receive top(1) convert(varchar(1000),message_body) as message
> from QueueReceiver
> where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> please help! thanks in advance!
> - tin
>|||ah, no wonder. another question:
if i want an application to send messages to 2 applications, then i guess i
will have to create two queues? i was trying to use one queue with 2
conversation handles.
many thanks.
"Remus Rusanu [MSFT]" wrote:

> The conversation handle is an output parameter for the BEGIN DIALOG
> statement. Each time you run the statement, a new conversation will be
> created and the @.conversationhandle will get a new value assigned to it. I
f
> the variable had a previous value, it will be overwriten.
> So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> was never created, hence the error on the RECEIVE statement.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
>
>|||In general yes, each application should listen on it's own queue. When
sending a message to more than one application it usually conforms to a
publish-subscribe pattern. See if this article helps you at
http://blogs.msdn.com/remusrusanu/a.../12/502942.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"tin" <tin@.discussions.microsoft.com> wrote in message
news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...[vbcol=seagreen]
> ah, no wonder. another question:
> if i want an application to send messages to 2 applications, then i guess
> i
> will have to create two queues? i was trying to use one queue with 2
> conversation handles.
> many thanks.
> "Remus Rusanu [MSFT]" wrote:
>|||thanks, that helped a lot!
"Remus Rusanu [MSFT]" wrote:

> In general yes, each application should listen on it's own queue. When
> sending a message to more than one application it usually conforms to a
> publish-subscribe pattern. See if this article helps you at
> http://blogs.msdn.com/remusrusanu/a.../12/502942.aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...
>
>sqlsql

conversation handle

hi all, i am having a hard time getting the conversation handle id to match ...
scripts that create the relevant objects ...
create message type QueryMessage validation = none
create contract QueryContract (QueryMessage sent by initiator)
create queue QueueSender
create queue QueueReceiver
create service Sender on queue QueueSender
create service Receiver on queue QueueReceiver (QueryContract)
send with conversation handle id ...
begin transaction
declare @.conversationhandle uniqueidentifier;
select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
begin dialog @.conversationhandle
from service [Sender]
to service 'Receiver'
on contract [QueryContract]
with encryption = off;
send on conversation @.conversationhandle
message type [QueryMessage] ('blah blah blah;');
commit transaction
after i ran the above send query a few times, and do the below select ...
select conversation_group_id, conversation_handle, cast(message_body as
varchar(1000)) from QueueReceiver
i got all kinds of different conversation_group_id and conversation_handle?
should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i specified
in the send query?
and when i do the below receive query, it says:
Msg 8426, Level 16, State 20, Line 1
The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not found.
receive top(1) convert(varchar(1000),message_body) as message
from QueueReceiver
where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
please help! thanks in advance!
- tin
The conversation handle is an output parameter for the BEGIN DIALOG
statement. Each time you run the statement, a new conversation will be
created and the @.conversationhandle will get a new value assigned to it. If
the variable had a previous value, it will be overwriten.
So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
was never created, hence the error on the RECEIVE statement.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"tin" <tin@.discussions.microsoft.com> wrote in message
news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> hi all, i am having a hard time getting the conversation handle id to
> match ...
> scripts that create the relevant objects ...
> create message type QueryMessage validation = none
> create contract QueryContract (QueryMessage sent by initiator)
> create queue QueueSender
> create queue QueueReceiver
> create service Sender on queue QueueSender
> create service Receiver on queue QueueReceiver (QueryContract)
> send with conversation handle id ...
> begin transaction
> declare @.conversationhandle uniqueidentifier;
> select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> begin dialog @.conversationhandle
> from service [Sender]
> to service 'Receiver'
> on contract [QueryContract]
> with encryption = off;
> send on conversation @.conversationhandle
> message type [QueryMessage] ('blah blah blah;');
> commit transaction
> after i ran the above send query a few times, and do the below select ...
> select conversation_group_id, conversation_handle, cast(message_body as
> varchar(1000)) from QueueReceiver
> i got all kinds of different conversation_group_id and
> conversation_handle?
> should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> specified
> in the send query?
> and when i do the below receive query, it says:
> Msg 8426, Level 16, State 20, Line 1
> The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> found.
> receive top(1) convert(varchar(1000),message_body) as message
> from QueueReceiver
> where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> please help! thanks in advance!
> - tin
>
|||ah, no wonder. another question:
if i want an application to send messages to 2 applications, then i guess i
will have to create two queues? i was trying to use one queue with 2
conversation handles.
many thanks.
"Remus Rusanu [MSFT]" wrote:

> The conversation handle is an output parameter for the BEGIN DIALOG
> statement. Each time you run the statement, a new conversation will be
> created and the @.conversationhandle will get a new value assigned to it. If
> the variable had a previous value, it will be overwriten.
> So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> was never created, hence the error on the RECEIVE statement.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
>
>
|||In general yes, each application should listen on it's own queue. When
sending a message to more than one application it usually conforms to a
publish-subscribe pattern. See if this article helps you at
http://blogs.msdn.com/remusrusanu/ar...12/502942.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"tin" <tin@.discussions.microsoft.com> wrote in message
news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...[vbcol=seagreen]
> ah, no wonder. another question:
> if i want an application to send messages to 2 applications, then i guess
> i
> will have to create two queues? i was trying to use one queue with 2
> conversation handles.
> many thanks.
> "Remus Rusanu [MSFT]" wrote:
|||thanks, that helped a lot!
"Remus Rusanu [MSFT]" wrote:

> In general yes, each application should listen on it's own queue. When
> sending a message to more than one application it usually conforms to a
> publish-subscribe pattern. See if this article helps you at
> http://blogs.msdn.com/remusrusanu/ar...12/502942.aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...
>
>

conversation handle

hi all, i am having a hard time getting the conversation handle id to match ...
scripts that create the relevant objects ...
create message type QueryMessage validation = none
create contract QueryContract (QueryMessage sent by initiator)
create queue QueueSender
create queue QueueReceiver
create service Sender on queue QueueSender
create service Receiver on queue QueueReceiver (QueryContract)
send with conversation handle id ...
begin transaction
declare @.conversationhandle uniqueidentifier;
select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
begin dialog @.conversationhandle
from service [Sender]
to service 'Receiver'
on contract [QueryContract]
with encryption = off;
send on conversation @.conversationhandle
message type [QueryMessage] ('blah blah blah;');
commit transaction
after i ran the above send query a few times, and do the below select ...
select conversation_group_id, conversation_handle, cast(message_body as
varchar(1000)) from QueueReceiver
i got all kinds of different conversation_group_id and conversation_handle?
should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i specified
in the send query'
and when i do the below receive query, it says:
Msg 8426, Level 16, State 20, Line 1
The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not found.
receive top(1) convert(varchar(1000),message_body) as message
from QueueReceiver
where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
please help! thanks in advance!
- tinThe conversation handle is an output parameter for the BEGIN DIALOG
statement. Each time you run the statement, a new conversation will be
created and the @.conversationhandle will get a new value assigned to it. If
the variable had a previous value, it will be overwriten.
So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
was never created, hence the error on the RECEIVE statement.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"tin" <tin@.discussions.microsoft.com> wrote in message
news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> hi all, i am having a hard time getting the conversation handle id to
> match ...
> scripts that create the relevant objects ...
> create message type QueryMessage validation = none
> create contract QueryContract (QueryMessage sent by initiator)
> create queue QueueSender
> create queue QueueReceiver
> create service Sender on queue QueueSender
> create service Receiver on queue QueueReceiver (QueryContract)
> send with conversation handle id ...
> begin transaction
> declare @.conversationhandle uniqueidentifier;
> select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> begin dialog @.conversationhandle
> from service [Sender]
> to service 'Receiver'
> on contract [QueryContract]
> with encryption = off;
> send on conversation @.conversationhandle
> message type [QueryMessage] ('blah blah blah;');
> commit transaction
> after i ran the above send query a few times, and do the below select ...
> select conversation_group_id, conversation_handle, cast(message_body as
> varchar(1000)) from QueueReceiver
> i got all kinds of different conversation_group_id and
> conversation_handle?
> should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> specified
> in the send query'
> and when i do the below receive query, it says:
> Msg 8426, Level 16, State 20, Line 1
> The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> found.
> receive top(1) convert(varchar(1000),message_body) as message
> from QueueReceiver
> where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> please help! thanks in advance!
> - tin
>|||ah, no wonder. another question:
if i want an application to send messages to 2 applications, then i guess i
will have to create two queues? i was trying to use one queue with 2
conversation handles.
many thanks.
"Remus Rusanu [MSFT]" wrote:
> The conversation handle is an output parameter for the BEGIN DIALOG
> statement. Each time you run the statement, a new conversation will be
> created and the @.conversationhandle will get a new value assigned to it. If
> the variable had a previous value, it will be overwriten.
> So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> was never created, hence the error on the RECEIVE statement.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> > hi all, i am having a hard time getting the conversation handle id to
> > match ...
> >
> > scripts that create the relevant objects ...
> >
> > create message type QueryMessage validation = none
> > create contract QueryContract (QueryMessage sent by initiator)
> > create queue QueueSender
> > create queue QueueReceiver
> > create service Sender on queue QueueSender
> > create service Receiver on queue QueueReceiver (QueryContract)
> >
> > send with conversation handle id ...
> >
> > begin transaction
> > declare @.conversationhandle uniqueidentifier;
> > select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> > begin dialog @.conversationhandle
> > from service [Sender]
> > to service 'Receiver'
> > on contract [QueryContract]
> > with encryption = off;
> > send on conversation @.conversationhandle
> > message type [QueryMessage] ('blah blah blah;');
> > commit transaction
> >
> > after i ran the above send query a few times, and do the below select ...
> >
> > select conversation_group_id, conversation_handle, cast(message_body as
> > varchar(1000)) from QueueReceiver
> >
> > i got all kinds of different conversation_group_id and
> > conversation_handle?
> > should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> > specified
> > in the send query'
> >
> > and when i do the below receive query, it says:
> > Msg 8426, Level 16, State 20, Line 1
> > The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> > found.
> >
> > receive top(1) convert(varchar(1000),message_body) as message
> > from QueueReceiver
> > where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> >
> > please help! thanks in advance!
> >
> > - tin
> >
>
>|||In general yes, each application should listen on it's own queue. When
sending a message to more than one application it usually conforms to a
publish-subscribe pattern. See if this article helps you at
http://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"tin" <tin@.discussions.microsoft.com> wrote in message
news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...
> ah, no wonder. another question:
> if i want an application to send messages to 2 applications, then i guess
> i
> will have to create two queues? i was trying to use one queue with 2
> conversation handles.
> many thanks.
> "Remus Rusanu [MSFT]" wrote:
>> The conversation handle is an output parameter for the BEGIN DIALOG
>> statement. Each time you run the statement, a new conversation will be
>> created and the @.conversationhandle will get a new value assigned to it.
>> If
>> the variable had a previous value, it will be overwriten.
>> So the conversation with the handle
>> 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
>> was never created, hence the error on the RECEIVE statement.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> HTH,
>> ~ Remus Rusanu
>> SQL Service Broker
>> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>>
>> "tin" <tin@.discussions.microsoft.com> wrote in message
>> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
>> > hi all, i am having a hard time getting the conversation handle id to
>> > match ...
>> >
>> > scripts that create the relevant objects ...
>> >
>> > create message type QueryMessage validation = none
>> > create contract QueryContract (QueryMessage sent by initiator)
>> > create queue QueueSender
>> > create queue QueueReceiver
>> > create service Sender on queue QueueSender
>> > create service Receiver on queue QueueReceiver (QueryContract)
>> >
>> > send with conversation handle id ...
>> >
>> > begin transaction
>> > declare @.conversationhandle uniqueidentifier;
>> > select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
>> > begin dialog @.conversationhandle
>> > from service [Sender]
>> > to service 'Receiver'
>> > on contract [QueryContract]
>> > with encryption = off;
>> > send on conversation @.conversationhandle
>> > message type [QueryMessage] ('blah blah blah;');
>> > commit transaction
>> >
>> > after i ran the above send query a few times, and do the below select
>> > ...
>> >
>> > select conversation_group_id, conversation_handle, cast(message_body as
>> > varchar(1000)) from QueueReceiver
>> >
>> > i got all kinds of different conversation_group_id and
>> > conversation_handle?
>> > should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
>> > specified
>> > in the send query'
>> >
>> > and when i do the below receive query, it says:
>> > Msg 8426, Level 16, State 20, Line 1
>> > The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
>> > found.
>> >
>> > receive top(1) convert(varchar(1000),message_body) as message
>> > from QueueReceiver
>> > where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
>> >
>> > please help! thanks in advance!
>> >
>> > - tin
>> >
>>|||thanks, that helped a lot!
"Remus Rusanu [MSFT]" wrote:
> In general yes, each application should listen on it's own queue. When
> sending a message to more than one application it usually conforms to a
> publish-subscribe pattern. See if this article helps you at
> http://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...
> > ah, no wonder. another question:
> >
> > if i want an application to send messages to 2 applications, then i guess
> > i
> > will have to create two queues? i was trying to use one queue with 2
> > conversation handles.
> >
> > many thanks.
> >
> > "Remus Rusanu [MSFT]" wrote:
> >
> >> The conversation handle is an output parameter for the BEGIN DIALOG
> >> statement. Each time you run the statement, a new conversation will be
> >> created and the @.conversationhandle will get a new value assigned to it.
> >> If
> >> the variable had a previous value, it will be overwriten.
> >>
> >> So the conversation with the handle
> >> 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> >> was never created, hence the error on the RECEIVE statement.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> HTH,
> >> ~ Remus Rusanu
> >>
> >> SQL Service Broker
> >> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
> >>
> >>
> >> "tin" <tin@.discussions.microsoft.com> wrote in message
> >> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> >> > hi all, i am having a hard time getting the conversation handle id to
> >> > match ...
> >> >
> >> > scripts that create the relevant objects ...
> >> >
> >> > create message type QueryMessage validation = none
> >> > create contract QueryContract (QueryMessage sent by initiator)
> >> > create queue QueueSender
> >> > create queue QueueReceiver
> >> > create service Sender on queue QueueSender
> >> > create service Receiver on queue QueueReceiver (QueryContract)
> >> >
> >> > send with conversation handle id ...
> >> >
> >> > begin transaction
> >> > declare @.conversationhandle uniqueidentifier;
> >> > select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> >> > begin dialog @.conversationhandle
> >> > from service [Sender]
> >> > to service 'Receiver'
> >> > on contract [QueryContract]
> >> > with encryption = off;
> >> > send on conversation @.conversationhandle
> >> > message type [QueryMessage] ('blah blah blah;');
> >> > commit transaction
> >> >
> >> > after i ran the above send query a few times, and do the below select
> >> > ...
> >> >
> >> > select conversation_group_id, conversation_handle, cast(message_body as
> >> > varchar(1000)) from QueueReceiver
> >> >
> >> > i got all kinds of different conversation_group_id and
> >> > conversation_handle?
> >> > should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> >> > specified
> >> > in the send query'
> >> >
> >> > and when i do the below receive query, it says:
> >> > Msg 8426, Level 16, State 20, Line 1
> >> > The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> >> > found.
> >> >
> >> > receive top(1) convert(varchar(1000),message_body) as message
> >> > from QueueReceiver
> >> > where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> >> >
> >> > please help! thanks in advance!
> >> >
> >> > - tin
> >> >
> >>
> >>
> >>
>
>

CONTAINSTABLE and local TABLE variables

Hi I have a stored procedure that uses local variables of type TABLE, is it possible to use CONTAINSTABLE on these as it appears to not be working correctly. The CONTAINSTABLE code is at the very bottom of this long piece of code.

CREATE PROCEDURE dbo.SearchJobs
@.STRING varchar(6000),
@.Longitude float,
@.Latitude float,
@.Distance int,
@.Category int
AS

DECLARE @.SEARCHTABLE TABLE(JobId bigint,
Type varchar(20) PRIMARY KEY,
PromotionCode varchar(50),
Title varchar(50),
JobCategoryId int,
Description text,
ConditionOfEmployment text,
DollarAmount decimal,
DollarType varchar(20),
DateFrom datetime,
DateTo datetime,
ResumeEmail varchar(200),
Phone varchar(20),
AreaCode varchar(10),
AddressId bigint,
Street varchar(50),
Suburb varchar(50),
PostCode varchar(10),
State varchar(20),
Longitude float,
Latitude float,
Positions int,
EmployerId varchar(200),
Filled bit,
FilledBy varchar(200),
EmployerVisible bit,
EmployeeVisible bit,
AdditionalSearchString text,
Street2 varchar(50),
Suburb2 varchar(50),
PostCode2 varchar(10),
State2 varchar(20),
Longitude2 float,
Latitude2 float,
AddressId2 bigint,
Reference varchar(50),
SearchColumn text)

DECLARE @.OUTPUTTABLE TABLE(JobId bigint,
Type varchar(20) PRIMARY KEY,
PromotionCode varchar(50),
Title varchar(50),
JobCategoryId int,
Description text,
ConditionOfEmployment text,
DollarAmount decimal,
DollarType varchar(20),
DateFrom datetime,
DateTo datetime,
ResumeEmail varchar(200),
Phone varchar(20),
AreaCode varchar(10),
AddressId bigint,
Street varchar(50),
Suburb varchar(50),
PostCode varchar(10),
State varchar(20),
Longitude float,
Latitude float,
Positions int,
EmployerId varchar(200),
Filled bit,
FilledBy varchar(200),
EmployerVisible bit,
EmployeeVisible bit,
AdditionalSearchString text,
Street2 varchar(50),
Suburb2 varchar(50),
PostCode2 varchar(10),
State2 varchar(20),
Longitude2 float,
Latitude2 float,
AddressId2 bigint,
Reference varchar(50))

DECLARE @.Cat as varchar(200)
IF (NOT @.Category is NULL)
BEGIN
SET @.Cat = 'AND JobCategoryId = ' + @.Category
END
ELSE
BEGIN
SET @.Cat = ''
END

-- This does not calculate exact coordinate distances. It is designed to find all jobs
-- within a certain distance of a reference point. It uses a sqare box for speed as
-- opposed to calculationg a cirecular reference.
IF (NOT @.Longitude is NULL)
BEGIN
EXEC (' INSERT INTO @.SEARCHTABLE SELECT JobId, Type, PromotionCode, Title, JobCategoryId, [Description], ConditionOfEmployment, DollarAmount, DollarType, DateFrom, DateTo, ResumeEmail,
Phone, AreaCode, AddressId, Street, Suburb, PostCode, State, Longitude, Latitude, Positions, EmployerId, Filled, FilledBy, EmployerVisible,
EmployeeVisible, AdditionalSearchString, Street2, Suburb2, PostCode2, State2, Longitude2, Latitude2, AddressId2, Reference, (str(Title) + '' '' + str(Suburb) + '' '' + str(Street) + '' '' + str(PostCode) + CAST([Description] AS varchar))
FROM Job
WHERE (Longitude < (@.Longitude + @.Distance)) AND (Longitude > (@.Longitude - @.Distance)) AND (Latitude < (@.Latitude + @.Distance)) AND (Latitude > (@.Latitude - @.Distance))' + @.Cat)

END
ELSE
BEGIN
EXEC('INSERT INTO @.SEARCHTABLE SELECT * FROM Job WHERE 1 ' + @.Cat)
END

IF(LEN(@.STRING) > 0)

BEGIN
DECLARE @.SearchString varchar(8000)
SET @.SearchString = ''
DECLARE @.INDEX INT
DECLARE @.SLICE nvarchar(4000)

SELECT @.INDEX = 1
DECLARE @.IDCounter int
SET @.IDCounter = 0
IF @.String IS NULL RETURN
WHILE @.INDEX !=0
BEGIN
SELECT @.INDEX = CHARINDEX(' ' ,@.STRING)
IF @.INDEX !=0
BEGIN
SELECT @.SLICE = LEFT(@.STRING,@.INDEX - 1)
END
ELSE
BEGIN
SELECT @.SLICE = @.STRING
--INSERT INTO @.SEARCHTERMS (searchterm) VALUES(@.SLICE)
IF @.SearchString = ''
BEGIN
SET @.SearchString = @.SLICE
END
ELSE
BEGIN
SET @.SearchString = @.SearchString + ' OR ' + @.SLICE
END


SET @.IDCounter = @.IDCounter + 1
SELECT @.STRING = RIGHT(@.STRING,LEN(@.STRING) - @.INDEX)
IF LEN(@.STRING) = 0 BREAK
END

END
END

EXEC sp_fulltext_table @.SEARCHTABLE

SELECT FT_TBL.JobId, FT_TBL.Type, FT_TBL.PromotionCode, FT_TBL.Title, FT_TBL.JobCategoryId, FT_TBL.[Description], FT_TBL.ConditionOfEmployment, FT_TBL.DollarAmount, FT_TBL.DollarType, FT_TBL.DateFrom, FT_TBL.DateTo, FT_TBL.ResumeEmail,
FT_TBL.Phone, FT_TBL.AreaCode, FT_TBL.AddressId, FT_TBL.Street, FT_TBL.Suburb, FT_TBL.PostCode, FT_TBL.State, FT_TBL.Longitude, FT_TBL.Latitude, FT_TBL.Positions, FT_TBL.EmployerId, FT_TBL.Filled, FT_TBL.FilledBy, FT_TBL.EmployerVisible,
FT_TBL.EmployeeVisible, FT_TBL.AdditionalSearchString, FT_TBL.Street2, FT_TBL.Suburb2, FT_TBL.PostCode2, FT_TBL.State2, FT_TBL.Longitude2, FT_TBL.Latitude2, FT_TBL.AddressId2, FT_TBL.Reference,
KEY_TBL.RANK
FROM @.SEARCHTABLE AS FT_TBL INNER JOIN
CONTAINSTABLE (@.SEARCHTABLE,SearchColumn,
@.SearchString

) AS KEY_TBL
ON FT_TBL.JobId = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO

The deprecated sp_fulltext_table operates on a table that exists in the database, see sp_fulltext_table (Transact-SQL) in Books Online.

Indexes cannot be created explicitly on table variables. See table (Transact-SQL) in Books Online.

contains/fuzzy/substring - type of join between two tables - MS SQL 2000

ok, I have a table with names of countries.
I have another table with a description field.
I want to join the country_name with the description field, BUT
instead of the join being based on equality, I want it be based on
country_name appearing in the description field.
Is this possible?You can try to use LIKE or PATINDEX in WHERE if these can find your
country_name in your fields
Will be not very efficient, though
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegroups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>|||On Jun 29, 5:07 pm, "AlexS" <salexru200...@.SPAMrogers.comPLEASE>
wrote:
> You can try to use LIKE or PATINDEX in WHERE if these can find your
I dont think... AHA! a correlated subquery should do it! thanks.
> country_name in your fields
> Will be not very efficient, though
Me no care :)|||You should also be able to use fulltext for this.
Here is an example
select * from TableWithName join (select [key] from
containstable(TableWithDescriptionField,descriptionColumn, @.SearchPhrase))
as t
on t.[key]=TableWithName.pk
Assuming that the primary key column of TableWithDescriptionField is the
same value as the PK of the TableWithName (ie pk fk relationship).
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegroups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>

contains/fuzzy/substring - type of join between two tables - MS SQL 2000

ok, I have a table with names of countries.
I have another table with a description field.
I want to join the country_name with the description field, BUT
instead of the join being based on equality, I want it be based on
country_name appearing in the description field.
Is this possible?You can try to use LIKE or PATINDEX in WHERE if these can find your
country_name in your fields
Will be not very efficient, though
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegroups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>|||On Jun 29, 5:07 pm, "AlexS" <salexru200...@.SPAMrogers.comPLEASE>
wrote:
> You can try to use LIKE or PATINDEX in WHERE if these can find your
I dont think... AHA! a correlated subquery should do it! thanks.

> country_name in your fields
> Will be not very efficient, though
Me no care |||You should also be able to use fulltext for this.
Here is an example
select * from TableWithName join (select [key] from
containstable(TableWithDescriptionField,
descriptionColumn, @.SearchPhrase))
as t
on t.[key]=TableWithName.pk
Assuming that the primary key column of TableWithDescriptionField is the
same value as the PK of the TableWithName (ie pk fk relationship).
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegroups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>