Script to convert char to int please help....

I ran the following simple select statement :
select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted =
'20041123'
Then I got the following error message after several thousand records were
fetched.
Syntax error converting the varchar value 'ZAER "' to a column of data
type int.
What I like to do is convert that column which is char to int or select
everything with the value 'ZAER' and copy to another table and then convert.
I did the first and updated, copied the data back and ran the select and
still got the same error. Can someone help please? Thank you.
James
How do you plan to convert the value 'ZAER' to an INT? And why do you have
non-integer strings mixed in with integer amounts in your 'amount' column?
I would recommend that you clean up your data and re-define the column as
INT to avoid these types of issues in the future.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> I ran the following simple select statement :
> select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted =
> '20041123'
> Then I got the following error message after several thousand records were
> fetched.
> Syntax error converting the varchar value 'ZAER "' to a column of data
> type int.
> What I like to do is convert that column which is char to int or select
> everything with the value 'ZAER' and copy to another table and then
convert.
> I did the first and updated, copied the data back and ran the select and
> still got the same error. Can someone help please? Thank you.
> James
|||What I wanted to do was remove all the non-integer strings first with an
update to '0' before converting. Do you know what I can do?
James.
"Adam Machanic" wrote:

> How do you plan to convert the value 'ZAER' to an INT? And why do you have
> non-integer strings mixed in with integer amounts in your 'amount' column?
> I would recommend that you clean up your data and re-define the column as
> INT to avoid these types of issues in the future.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> convert.
>
>
|||Here's what I would do:
UPDATE tcase
SET amount= '0'
WHERE PATINDEX('%[^0-9]%', RTRIM(LTRIM(amount))) > 0
OR amount IS NULL
ALTER TABLE tcase
ALTER COLUMN amount INT NOT NULL
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:506B7C12-D36E-4F82-A2A1-5C545CD643CC@.microsoft.com...[vbcol=seagreen]
> What I wanted to do was remove all the non-integer strings first with an
> update to '0' before converting. Do you know what I can do?
> James.
>
> "Adam Machanic" wrote:
have[vbcol=seagreen]
column?[vbcol=seagreen]
as[vbcol=seagreen]
were[vbcol=seagreen]
data[vbcol=seagreen]
select[vbcol=seagreen]
and[vbcol=seagreen]

Script to convert char to int please help....

I ran the following simple select statement :
select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted = '20041123'
Then I got the following error message after several thousand records were
fetched.
Syntax error converting the varchar value 'ZAER "' to a column of data
type int.
What I like to do is convert that column which is char to int or select
everything with the value 'ZAER' and copy to another table and then convert.
I did the first and updated, copied the data back and ran the select and
still got the same error. Can someone help please? Thank you.
JamesHow do you plan to convert the value 'ZAER' to an INT? And why do you have
non-integer strings mixed in with integer amounts in your 'amount' column?
I would recommend that you clean up your data and re-define the column as
INT to avoid these types of issues in the future.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> I ran the following simple select statement :
> select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted => '20041123'
> Then I got the following error message after several thousand records were
> fetched.
> Syntax error converting the varchar value 'ZAER "' to a column of data
> type int.
> What I like to do is convert that column which is char to int or select
> everything with the value 'ZAER' and copy to another table and then
convert.
> I did the first and updated, copied the data back and ran the select and
> still got the same error. Can someone help please? Thank you.
> James|||What I wanted to do was remove all the non-integer strings first with an
update to '0' before converting. Do you know what I can do?
James.
"Adam Machanic" wrote:
> How do you plan to convert the value 'ZAER' to an INT? And why do you have
> non-integer strings mixed in with integer amounts in your 'amount' column?
> I would recommend that you clean up your data and re-define the column as
> INT to avoid these types of issues in the future.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> > I ran the following simple select statement :
> >
> > select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted => > '20041123'
> > Then I got the following error message after several thousand records were
> > fetched.
> >
> > Syntax error converting the varchar value 'ZAER "' to a column of data
> > type int.
> >
> > What I like to do is convert that column which is char to int or select
> > everything with the value 'ZAER' and copy to another table and then
> convert.
> > I did the first and updated, copied the data back and ran the select and
> > still got the same error. Can someone help please? Thank you.
> >
> > James
>
>|||Here's what I would do:
UPDATE tcase
SET amount= '0'
WHERE PATINDEX('%[^0-9]%', RTRIM(LTRIM(amount))) > 0
OR amount IS NULL
ALTER TABLE tcase
ALTER COLUMN amount INT NOT NULL
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:506B7C12-D36E-4F82-A2A1-5C545CD643CC@.microsoft.com...
> What I wanted to do was remove all the non-integer strings first with an
> update to '0' before converting. Do you know what I can do?
> James.
>
> "Adam Machanic" wrote:
> > How do you plan to convert the value 'ZAER' to an INT? And why do you
have
> > non-integer strings mixed in with integer amounts in your 'amount'
column?
> > I would recommend that you clean up your data and re-define the column
as
> > INT to avoid these types of issues in the future.
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> > news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> > > I ran the following simple select statement :
> > >
> > > select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted => > > '20041123'
> > > Then I got the following error message after several thousand records
were
> > > fetched.
> > >
> > > Syntax error converting the varchar value 'ZAER "' to a column of
data
> > > type int.
> > >
> > > What I like to do is convert that column which is char to int or
select
> > > everything with the value 'ZAER' and copy to another table and then
> > convert.
> > > I did the first and updated, copied the data back and ran the select
and
> > > still got the same error. Can someone help please? Thank you.
> > >
> > > James
> >
> >
> >

Script task User defined variable and Data Reader Source SQL Statement

Hello,

Please can anyone tell me if or how I have to cross reference to a user defined variable from a script task in the SQL of a Data Reader Source?

The script task creates a variable for the last Sales Ledger Session. The SQL in the data reader source that updates the DW sales invoice lines file based upon those invoice lines where the session number is greater than the value from the script task.

The SQL command in the custom properties doesn't cross reference back to the variable.

If anyone can help or needs further detail to help please post a resonse.

Thanks,

Marcus Simpson

The SqlCommand property can be overridden with a property expression. For data flow components expression are exposed on the parent Data Flow task rather than at the individual component level.

If you are not already familiar with expressions, look them up in Books Online.

To quickly set an expression, select the Data Flow task and hit F4. Then find the Expressions property in the property grid (as shown by the F4 key) and then select the property for the your DR source, SqlCommand. The name will be something like [DataReader Source].[SqlCommand]. Now add an expression that uses the variable as part of the command.

|||

Darren,

Thankyou very much for your response. It was correct.

Marcus.

|||

Marcus Simpson wrote:

Darren,

Thankyou very much for your response. It was correct.

Marcus.

Please mark Darren's post as answered.

Scope_Identity and SqlDataSource

have a detailsView control with an SqlDataSource whose insert statement looks like this:

InsertCommand

="INSERT INTO [tblCompaniesNewSetRaw] ([NAME], [CITY], [ST], [ZIPCODE], [NAICS], [NAICSDESCRIPTION]) VALUES (@.NAME, @.CITY, @.ST, @.ZIPCODE, @.NAICS, @.NAICSDESCRIPTION); SELECT RETURN_VALUE = SCOPE_IDENTITY()"

also played with the same insert but used ...;Select SCOPE_IDENTITY()

my question is how do i get the last record inserted into tblCompaniesNewSetRaw after the insert is run. ie I read that the Select Scope_identity() would return the value but how do i access the return value from within the code behind page, iusing VB.

some things i tried in the detailsView_ItemInserted(...

Dim

rowAs DetailsViewRowForEach rowIn DetailsView3.Rows

x = row.Cells.Item(0).Text

Next

in the VS debugger x is just "" and not the last record inserted in that table.

probably way off base on this, clues appreciated, tc

I have never done it but i think what you will want to do is add a

InsertCommand="INSERT INTO [tblCompaniesNewSetRaw] ([NAME], [CITY], [ST], [ZIPCODE], [NAICS], [NAICSDESCRIPTION]) VALUES (@.NAME, @.CITY, @.ST, @.ZIPCODE, @.NAICS, @.NAICSDESCRIPTION); SELECT @.RETURN_VALUE = SCOPE_IDENTITY()"

<

asp:ParameterDirection="ReturnValue"Name="RETURN_VALUE"Type="Int16"/>

And then in the

ProtectedSub SqlDataSource1_Inserted(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SqlDataSource1.InsertedDim newIdAsObject = e.Command.Parameters("@.ReturnValue").ValueEndSub|||

sorry this

Dim newIdAsObject = e.Command.Parameters("@.ReturnValue").Value

should be this

Dim newIdAsObject = e.Command.Parameters("@.Return_Value").Value

|||

thanks rojay12

u gave me the clue i needed,

ended up using

Dim newIdAsObject = e.Command.Parameters("@.RETURN_VALUE").Value

<asp:ParameterName="RETURN_VALUE"Direction="Output"Type="Int32"/>

InsertCommand

="INSERT INTO [tblCompaniesNewSetRaw] ([NAME], [CITY], [ST], [ZIPCODE], [NAICS], [NAICSDESCRIPTION]) VALUES (@.NAME, @.CITY, @.ST, @.ZIPCODE, @.NAICS, @.NAICSDESCRIPTION); SELECT @.RETURN_VALUE = SCOPE_IDENTITY()

seems to be working, now got to go back and sync up some gridviews and the detailview afte the insert, much obliged, tc

SCOPE_IDENITY() - Incorrect Value (Returned)

There are two tables

Table_1:

ProductID (Identity Increment)

ProductDescription (nvarchar)

Table_2:

ProductID (int)

I have a sql statement and it's like this. This SQL is probably incorrect - but hopefully gives you an idea of what I am trying to do:

DECLARE@.getTheNewProductIDint
INSERT INTOTable_1 (ProductDescription)VALUES('SomeValue') ;SET@.getTheNewProductID =SELECT SCOPE_IDENTITY()
INSERT INTOTable_2 (ProductID)VALUES(@.getTheNewProductID)
  

What I need is: when inserting into Table_1 to get the Exact New Product Id from it that occurs from the identity increment - then insert that exact same product ID into table_2

The problem is it is returning incorrect values on the scope identity. Such as values from two transaction ago.

How do you do this?

I did try using @.@.identity which may have stuffed things up??

Thanks for your help

Hi

Try with this code.

DECLARE@.getTheNewProductIDint

INSERT INTOTable_1 (ProductDescription)VALUES('SomeValue') ;

SET@.getTheNewProductID = (SELECTProductID from Table_1 where ProductID= SCOPE_IDENTITY())

INSERT INTOTable_2 (ProductID)VALUES(@.getTheNewProductID)

scope statement in the session scope?

How to write [scope] statements in the session cope,not in the cube-mdx-script?

In the book called "Microsoft Analysis Services 2005" written by developers from microsft's ssas2005 team, there is a senctence:

"because scope statements and assignment operators work ony in the context of a cube or session..."

How to do that ?

Thanx.

You can't do a scope statement from Management Studio because Management Studio doesn't set the current cube on the connection string.

If you look at the MDX Script Performance tool (http://www.codeplex.com/mdxscriptperf) that Chris Webb wrote you will see an example of running a scope statement within a session. It specifies the cube in the connection string. And it replays the calc script one statement at a time which is an example of using "scope" in the context of a session.

Hope that helps.

|||

Thanx for your answer,which is right absolutely.

But I doubt about the Performance Analyzer according the inner working rule , which can be found in the book called "Microsoft Analysis Services 2005" that is written by developers from SSAS2005.