Change local variable inside query

/*Given*/

CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)

/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.

This doesn't compile of course, but is there
a way to accomplish the same thing?
*/

DECLARE @.ndx int

SET @.ndx = 1

SELECT

(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT

FROM _T1sub a

/*Output would look like this:*/

FKplusWT
----
11
22
33

/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/

thanks, Otto PorterOn Sat, 02 Oct 2004 12:20:48 -0600, Otto Porter wrote:

>I know, I can get this output just by adding
>FK+WT. This is not about that.
>This is about setting vars inside a query

Hi Otto,

It's not possible to change the value of a variable during the execution
of a SELECT statement. At least not the way you are trying to do it.

You can of course do
SELECT @.var = ..., @.var = ...
FROM table
WHERE ...
but I assume that this is not what you want. You can't mix this format of
the SELECT statement with a SELECT that outputs a result set.

The way I read your example, it would be very easy to have queries where
the result would be dependent on the order in which rows are processed by
SQL Server. Since SQL Server is entirely free in it's choice of processing
order, the results would be unexpected and might even vary from execution
to execution.

Check out the following link to find some good examples of the possible
effects of unexpected processing order on assignments with the SELECT
statement:
http://groups.google.com/groups?hl=...FTNGP12.phx.gbl

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Catching return values of a SP

I have calling a stored procedure that returns two values, and I want to catch these values and to store them into a variable.

Here is a piece of my SP inside SQL Server that shows the returned values:

…
SELECT @.Id = SCOPE_IDENTITY()
SELECT @.Id AS user_id
SELECT 1 AS Value
END
GO

In my aspx page I am trying to call the first value like this:

Dim nID
CmdInsert.Parameters.Add(New SqlParameter("@.RETURN_VALUE", SqlDbType.bigint, 8, "user_id"))
 CmdInsert.Parameters("@.RETURN_VALUE").Direction = ParameterDirection.ReturnValue
CmdInsert.Parameters("@.RETURN_VALUE").Value = nID

And to check if the right value is returned I use:

strConnection.open()
 cmdInsert.ExecuteNonQuery
 'Set the value of a textbox
 ident.text = nID
 strConnection.close()

But now no value appears in the textbox, How can I achieve it? What is wrong?You are sort of combining a few different approaches to solving this problem. Since only one ReturnValue can be returned from a stored procedure and you need 2 values, that approach won't work. And since you only have 2 values, I think that you should use OUTPUT parameters.

The stored procedure would look like this:

CREATE PROCEDURE
 myProcedure
AS
 @.myInput1 varchar(50),
 @.myInput2 varchar(50),
 @.myOutput1 bigint OUTPUT,
 @.myOutput2 bigint OUTPUT
INSERT <etc etc
SET @.myOutput1 = SCOPE_IDENTITY
SET @.myOutput2 = 2

Your aspx page code would look like this:

CmdInsert.Parameters.Add("@.myOutput1", SqlDbType.bigint)
CmdInsert.Parameters("@.myOutput1").Direction = ParameterDirection.Output
CmdInsert.Parameters.Add("@.myOutput2", SqlDbType.bigint)
CmdInsert.Parameters("@.myOutput2").Direction = ParameterDirection.Output
strConnection.open()
cmdInsert.ExecuteNonQuery
'Set the value of a textbox
ident.text = CmdInsert("@.myOutput1")
strConnection.close()

Terri|||I have followed all your steps, and now this error message appears:

BC30367: Class 'System.Data.SqlClient.SqlCommand' cannot be indexed because it has no default property.

What does it mean?|||Sorry, the line afected is this:

Line 105: ident.text = CmdInsert("@.Id")|||I'm the one who's sorry. The correct syntax for that line is:

ident.text = CmdInsert.Parameters("@.Id").Value

Terri|||i think its something like :

ident.text=convert.toint32(CmdInsert.Parameters("@.Id").Value)

hth|||Good! now runs fine

Thank you very much,
Cesar

Catch Error message in Variable?

Greetings all,

When an error occurs it is written to a log file (Assuming you have loggin on).
Anyone know of a way to catch the error in a variable?

When an error occurs I send an email explaining where there error happened and to view the logfile. I would like to include the last error in the email. Saves having to go view the log...

Thanks

Yes there is a way. In fact, its done for you.

The OnError eventhandler has a system variable scoped to it called @.[System::ErrorDescription]. That variable contains the error message of the error that caused the eventhandler to fire.

Please reply if any of that needs clarifying.

-Jamie

|||

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

|||Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))|||

Thiru_ wrote:

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

Very simply...no. The OnPostExecute eventhandler gets raised when a container finishes execution. OnError gets raised when a container throws an error. hence, ErrorDescription is not relevant in OnPostExecute.

-Jamie

|||

Crispin wrote:

Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))

I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.

-Jamie

|||

Jamie Thomson wrote:

Crispin wrote:

Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))

I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.

-Jamie

Nope, you misunderstood me.

Have a data flow which inserts 1000 rows into a table. One of the rows cannot

go because of a constraint on the column. The following is thrown by SQL /

SSIS:

>>>>>>>>>>>>>>>>>>>>>>>>>>

[OLE DB Destination [19]] Error: An OLE DB error has occurred. Error code:

0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL

Native Client" Hresult: 0x80040E2F Description: "The

statement has been terminated.". An OLE DB record is available.

Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F

Description: "The INSERT statement conflicted with the CHECK constraint

"CK_cpxx". The conflict occurred in database "POS_ETL",

table "dbo.cpxx", column 'Col1'.".

>>>>>>>>>>>>>>>>>>>>>>>>>>

[OLE DB Destination [19]] Error: The "input "OLE

DB Destination Input" (32)" failed because error code 0xC020907B

occurred, and the error row disposition on "input "OLE DB Destination

Input" (32)" specifies failure on error. An error occurred on the

specified object of the specified component.

>>>>>>>>>>>>>>>>>>>>>>>>>>

[DTS.Pipeline] Error: The ProcessInput method on component

"OLE DB Destination" (19) failed with error code 0xC0209029. The

identified component returned an error from the ProcessInput method. The error

is specific to the component, but the error is fatal and will cause the Data

Flow task to stop running.

>>>>>>>>>>>>>>>>>>>>>>>>>>

[DTS.Pipeline] Error: Thread "WorkThread0"

has exited with error code 0xC0209029.

>>>>>>>>>>>>>>>>>>>>>>>>>>

The above is one error with 4 lines explaining what happened. (or not?)

This causes the onerror event to fire 4 times.

This type of behavior is useless for any error handling of this type as

anything you do in the handler will fire again and again and again.

I can think of many dirty ways to get around this but they create more work

than it's worth.

OnFail constraint is the simplest and totally ignoring the onerror event

handlers.|||

Hi Jamie/Crispin,

Thanks for your comments. I missed the point that u said.

As Crispin told, it fires the event 4 times for a single error and so I am getting a weird error msg instead of some useful information which is the fist line of the eror msg. So I am checking the variable first and if it is empty, then I am taking value from 'ErrorDescription'. It worked. (But as Crispin told, it is a dirty method, isn't it?)

Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?

Thanks.

|||

Thiru_ wrote:

Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?

Thanks.

I was thinking along the lines of using conditional precedence constraints to ensure that you only log the message if certain conditions are met (e.g. System::ErrorDescription has something in it).

-Jamie

|||

Crispin wrote:

This type of behavior is useless for any error handling of this type as anything you do in the handler will fire again and again and again.

I can think of many dirty ways to get around this but they create more work than it's worth.
OnFail constraint is the simplest and totally ignoring the onerror event handlers.

Hmmm...useless you say? Why is it useless that the eventhandler fires again and again? You still got all the information that you need for debugging. And more. Admittedly you may get information that isn't pertinent to you but look at it from this perspective - SSIS is giving you all the information that it can possibly give you in order to debug.

Not convinced? Fair enough...I can understand the frustration (although don't agree with it :) )

-Jamie

|||

JAmin,

How Can I

put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event

|||

Thiru_ wrote:

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

No. it is only scoped to the OnError eventhandler. Open up the Variables pane and you will see this for yourself.

-Jamie

|||

leo1 wrote:

JAmin,

How Can I

put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event

I don't know. You have to tell me what your logic is.

Go here for info about conditional precedence constraints: http://www.sqlis.com/default.aspx?306

-jamie

|||Error description is also available on the error output of data flow tasks, no?! Why not catch it there and use it later?

Catch Error message in Variable?

Greetings all,

When an error occurs it is written to a log file (Assuming you have loggin on).
Anyone know of a way to catch the error in a variable?

When an error occurs I send an email explaining where there error happened and to view the logfile. I would like to include the last error in the email. Saves having to go view the log...

Thanks

Yes there is a way. In fact, its done for you.

The OnError eventhandler has a system variable scoped to it called @.[System::ErrorDescription]. That variable contains the error message of the error that caused the eventhandler to fire.

Please reply if any of that needs clarifying.

-Jamie

|||

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

|||Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))|||

Thiru_ wrote:

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

Very simply...no. The OnPostExecute eventhandler gets raised when a container finishes execution. OnError gets raised when a container throws an error. hence, ErrorDescription is not relevant in OnPostExecute.

-Jamie

|||

Crispin wrote:

Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))

I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.

-Jamie

|||

Jamie Thomson wrote:

Crispin wrote:

Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))

I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.

-Jamie

Nope, you misunderstood me.
Have a data flow which inserts 1000 rows into a table. One of the rows cannot go because of a constraint on the column. The following is thrown by SQL / SSIS:

>>>>>>>>>>>>>>>>>>>>>>>>>>
[OLE DB Destination [19]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The INSERT statement conflicted with the CHECK constraint "CK_cpxx". The conflict occurred in database "POS_ETL", table "dbo.cpxx", column 'Col1'.".
>>>>>>>>>>>>>>>>>>>>>>>>>>

[OLE DB Destination [19]] Error: The "input "OLE DB Destination Input" (32)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (32)" specifies failure on error. An error occurred on the specified object of the specified component.
>>>>>>>>>>>>>>>>>>>>>>>>>>

[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (19) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
>>>>>>>>>>>>>>>>>>>>>>>>>>

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.
>>>>>>>>>>>>>>>>>>>>>>>>>>

The above is one error with 4 lines explaining what happened. (or not?)
This causes the onerror event to fire 4 times.
This type of behavior is useless for any error handling of this type as anything you do in the handler will fire again and again and again.
I can think of many dirty ways to get around this but they create more work than it's worth.
OnFail constraint is the simplest and totally ignoring the onerror event handlers.|||

Hi Jamie/Crispin,

Thanks for your comments. I missed the point that u said.

As Crispin told, it fires the event 4 times for a single error and so I am getting a weird error msg instead of some useful information which is the fist line of the eror msg. So I am checking the variable first and if it is empty, then I am taking value from 'ErrorDescription'. It worked. (But as Crispin told, it is a dirty method, isn't it?)

Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?

Thanks.

|||

Thiru_ wrote:

Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?

Thanks.

I was thinking along the lines of using conditional precedence constraints to ensure that you only log the message if certain conditions are met (e.g. System::ErrorDescription has something in it).

-Jamie

|||

Crispin wrote:

This type of behavior is useless for any error handling of this type as anything you do in the handler will fire again and again and again.

I can think of many dirty ways to get around this but they create more work than it's worth.
OnFail constraint is the simplest and totally ignoring the onerror event handlers.

Hmmm...useless you say? Why is it useless that the eventhandler fires again and again? You still got all the information that you need for debugging. And more. Admittedly you may get information that isn't pertinent to you but look at it from this perspective - SSIS is giving you all the information that it can possibly give you in order to debug.

Not convinced? Fair enough...I can understand the frustration (although don't agree with it :) )

-Jamie

|||

JAmin,

How Can I

put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event

|||

Thiru_ wrote:

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

No. it is only scoped to the OnError eventhandler. Open up the Variables pane and you will see this for yourself.

-Jamie

|||

leo1 wrote:

JAmin,

How Can I

put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event

I don't know. You have to tell me what your logic is.

Go here for info about conditional precedence constraints: http://www.sqlis.com/default.aspx?306

-jamie

|||Error description is also available on the error output of data flow tasks, no?! Why not catch it there and use it later?

Casting question

HI all,
Quick question about a trigger i am developing.
I need to take a varchar string variable and convert it and store it in an
integer variable.
How do i write that statement.
Pls keep in mind that this is inside a trigger not inside a SQL statement.
Thanks in advance,
Colin
csmart@.nf.sympatico.caassuming the value is a number value an inplicit conversion will occur
take a look at this
declare @.v varchar(50),@.i int
select @.v ='1212121'
select @.i =@.v -- implicit conversion
select convert(int,@.v),@.i
the only problem you will have is if the value is bigger than an int
can hold or not a number
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||try isnumeric function to check if its a valid numeric

Casting decimal number

Hello,

When I declare a VB variable Dim s as Decimal,

I want to cast d like this :
1452,41
41,00
45,47
756544,04

Only with to digits after the ","

How can I perform this

Hi,

If my variable d=125,45111

How can I view d like 125,45 ?

|||you mean 2 digits after a "decimal" not a "comma" right?|||

You have two choices use place holder in strings and formatting or set precision and scale. Try the links below for details. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstringsoutputexample.asp

http://support.microsoft.com/?kbid=892406

|||

Hi,

If you are asking how to mak this conversion using t-sql, you can use CAST function

You can run and test the result for CAST

DECLARE @.d FLOAT
SET @.d = 125.45111

SELECT @.d, CAST(@.d AS DECIMAL(10,2))

|||

Hi,

Thank I mean two digits after a decimal number ( 44,45 or 4,00 or 7888,01 )

I'm using VB.NET and my variable is declared like Dim x as decimal.

Thanks

Cast/Convert value to VB variable

Hi all,
I have a VB 6.0 application that interacts with an MS Access backend. I am in the process of converting it so that it interacts with SQL Server 2000. The current applications uses Cint, CStr, etc. functions which are MS-Access specific, so I am now switching over to Convert (or Cast) function. The problem is that there are certain places wherein I first need to store the value of the resultant CAST/Convert function in a variable and then use that value in a SQL statement. However, I cant seem to figure out a way of storing the results from CAST/CONVERT functions in the VB 6.0 variable. All the examples on Internet show use of these functions directly in an SQL statement e.g. "Select CAST(title as Int) from xyz", etc.
Can anybody tell me how can i get the values to be stored in the variable? I am really stuck here and cant seem to progress.

Thanks in advance for all your help.

Regards:
Prathmeshe.g.

set rs=cmd.execute("select cast(title as int) as title from xyz")

title=rs.fields("title")|||Thanks for the reply oj. However, my requirement is somewhat different. I'll explain the scenario in short. The user chooses a filename to delete, which is stored in the database as a record. It is stored in 2 places in the database. One as a whole filename and in the second place as a breakdown record. The program should delete the filename from the database and also the file from the disk location. The filename in the database is stored as say "XY006CV003A.xls" or "XY005CJ003B.doc" however on the disk they are stored with the above number and the title for the document, concatenated e.g "XY006CV003A test.xls". So I need to extract the file name only which is "XY006CV003A.xls" to match the database record. The last part "003" is sort of a sequence number and is stored in the database. I need to extract the that sequence to match it and delete it from the second place as I have mentioned. '003' when extracted from the filename will be a string and I need to cast it to Integer type to match the record.

e.g.
fname = Split("XY006CV003A.xls",".") gives "XY006CV003A"
seq = Mid(fname, 8, 3) gives '003' which is string format

I now need to use this seq variable in the query

"Select * from XYZ where fileseq=" & seq

fileseq is of integer datatype so I need to cast/convert seq variable to Integer from String.
Can anybody suggest any ideas?

Regards:
Prathmesh

cast question

if i declare a variable within a stored procedure as a varchar, then later
cast it to an integer- will it act as an integer for all subsequent
references after the cast?
example:
declare @.myVar varchar(2)
set @.myVar = '2'
set @.myVar = cast(@.myVar as integer)
select @.myVar -- what data type is my variable now? is it an integer
because of the previous cast?
or would i need to specify: "select
cast(@.myVar as integer)" in order for it to be considered an integer?
thanks much,
jTJT,
DECLARE @.MYVAR VARCHAR(2)
SET @.MYVAR = '2'
SELECT ISNUMERIC(@.MYVAR)
SELECT 'THIS IS A TEST ' + @.MYVAR
SET @.MYVAR = CAST(@.MYVAR AS INTEGER)
SELECT ISNUMERIC(@.MYVAR)
SELECT 'THIS IS A TEST ' + @.MYVAR
RESULTS:
1
THIS IS A TEST 2
1
THIS IS A TEST 2
HTH
Jerry
"JT" <jt@.nospam.com> wrote in message
news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...
> if i declare a variable within a stored procedure as a varchar, then later
> cast it to an integer- will it act as an integer for all subsequent
> references after the cast?
> example:
> declare @.myVar varchar(2)
> set @.myVar = '2'
> set @.myVar = cast(@.myVar as integer)
> select @.myVar -- what data type is my variable now? is it an integer
> because of the previous cast?
> or would i need to specify: "select
> cast(@.myVar as integer)" in order for it to be considered an integer?
>
> thanks much,
> jT
>|||Since you declared it to be varchar(2), that's how it will remain for the
batch.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"JT" <jt@.nospam.com> wrote in message
news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...
if i declare a variable within a stored procedure as a varchar, then later
cast it to an integer- will it act as an integer for all subsequent
references after the cast?
example:
declare @.myVar varchar(2)
set @.myVar = '2'
set @.myVar = cast(@.myVar as integer)
select @.myVar -- what data type is my variable now? is it an integer
because of the previous cast?
or would i need to specify: "select
cast(@.myVar as integer)" in order for it to be considered an integer?
thanks much,
jT|||Note however that this will cause the string concatenation to fail where the
others worked:
SELECT 'THIS IS A TEST ' + CAST(@.MYVAR AS INTEGER)
Results:
Server: Msg 245, Level 16, State 1, Line 7
Syntax error converting the varchar value 'THIS IS A TEST ' to a column of
data type int.
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eYsPhT1zFHA.3896@.TK2MSFTNGP10.phx.gbl...
> JT,
> DECLARE @.MYVAR VARCHAR(2)
> SET @.MYVAR = '2'
> SELECT ISNUMERIC(@.MYVAR)
> SELECT 'THIS IS A TEST ' + @.MYVAR
> SET @.MYVAR = CAST(@.MYVAR AS INTEGER)
> SELECT ISNUMERIC(@.MYVAR)
> SELECT 'THIS IS A TEST ' + @.MYVAR
> RESULTS:
> --
> 1
>
> --
> THIS IS A TEST 2
> --
> 1
> --
> THIS IS A TEST 2
> HTH
> Jerry
> "JT" <jt@.nospam.com> wrote in message
> news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...
>|||thanks much!
jT
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e1hiGX1zFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Note however that this will cause the string concatenation to fail where
the
> others worked:
> SELECT 'THIS IS A TEST ' + CAST(@.MYVAR AS INTEGER)
> Results:
> Server: Msg 245, Level 16, State 1, Line 7
> Syntax error converting the varchar value 'THIS IS A TEST ' to a column of
> data type int.
> HTH
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eYsPhT1zFHA.3896@.TK2MSFTNGP10.phx.gbl...
integer
>|||Just to add, you are casting the value in @.myVar to integer, not the @.myVar
variable. Note also that you don't have to cast it to an integer just get
use it as an integer. If you said
select 1 + '1'
It will return:
2
But if you enter
select 1 + 'bob'
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'bob' to a column of data type
int.
It shouts "WRONG" at you, just as if you did:
select cast('bob' as int)
So be careful with this sort of operaton, because when you cast a varchar to
an int it will not just return NULL, it gives an error.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"JT" <jt@.nospam.com> wrote in message
news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...
> if i declare a variable within a stored procedure as a varchar, then later
> cast it to an integer- will it act as an integer for all subsequent
> references after the cast?
> example:
> declare @.myVar varchar(2)
> set @.myVar = '2'
> set @.myVar = cast(@.myVar as integer)
> select @.myVar -- what data type is my variable now? is it an integer
> because of the previous cast?
> or would i need to specify: "select
> cast(@.myVar as integer)" in order for it to be considered an integer?
>
> thanks much,
> jT
>|||In addition to the other posts, check out "data type precedence" in Books On
line. Will probably
explain a lot for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JT" <jt@.nospam.com> wrote in message news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...arkred">
> if i declare a variable within a stored procedure as a varchar, then later
> cast it to an integer- will it act as an integer for all subsequent
> references after the cast?
> example:
> declare @.myVar varchar(2)
> set @.myVar = '2'
> set @.myVar = cast(@.myVar as integer)
> select @.myVar -- what data type is my variable now? is it an integer
> because of the previous cast?
> or would i need to specify: "select
> cast(@.myVar as integer)" in order for it to be considered an integer?
>
> thanks much,
> jT
>

CAST function problem

Hi,

I am facing problem while using CAST function. Here i am using SUM function inside CAST function and assigning the returned value to a variable which is of varchar datatype of length 100. The variable which i am using inside SUM function is big int.

I am doing like this:

@.hour = (select CAST(SUM(totaltime) as varchar(100) from 'Table name'

It was getting error like:

Unable to convert varchar datatype to bigint.

How can i resolve this problem..
Regards,
SaratStart by posting code that will actually compile... Also, give us the data types of each column you are looking at.|||Actually the following code compiles and works as I would expect:

Create Table #X(x bigint)

Insert Into #X Values(1)

Insert Into #X Values(2)

Declare @.hour varchar(100)

Set @.hour = (select CAST(SUM(x) as varchar(100)) from #X)

Select @.hour

Drop Table #X

So I don't really see the problem... I pretty-much copied and pasted your line

|||Just wondering - why not use:

select @.hour = CAST(SUM(x) as varchar(100)) from #X

rather than: SET @.hour = (SELECT ....

CAST function problem

Hi,

I am facing problem while using CAST function. Here i am using SUM function inside CAST function and assigning the returned value to a variable which is of varchar datatype of length 100. The variable which i am using inside SUM function is big int.

I am doing like this:

@.hour = (select CAST(SUM(totaltime) as varchar(100) from 'Table name'

It was getting error like:

Unable to convert varchar datatype to bigint.

How can i resolve this problem..
Regards,
SaratStart by posting code that will actually compile... Also, give us the data types of each column you are looking at.|||Actually the following code compiles and works as I would expect:

Create Table #X(x bigint)

Insert Into #X Values(1)

Insert Into #X Values(2)

Declare @.hour varchar(100)

Set @.hour = (select CAST(SUM(x) as varchar(100)) from #X)

Select @.hour

Drop Table #X

So I don't really see the problem... I pretty-much copied and pasted your line

|||Just wondering - why not use:

select @.hour = CAST(SUM(x) as varchar(100)) from #X

rather than: SET @.hour = (SELECT ....

CAST a variable into a datetime object

CAST a variable into a datetime object
I need to do a CAST(@.variable_name as datetime)
this won't work because @.variable_name has the following format
'dd/mm/yy hh:mi:ss:mmmAM'
like how do i specify a style for it.
please help..
James : (You can use default values (style 0 or 100) to represent, refer to books online for CAST & CONVERT topic.

HTH.|||My suggestion is to write some code (which could be a transact-sql expression) to convert your chacter date to a standard form. I would strongly suggest using the ISO standard 'yyyy-mm-dd hh:mm:ss.ttt" format. I've seen a euro-to-ISO time function, if you can't find it, I can either find or write one for you.

-PatP|||hi

thanx for the reply.

books online wasn't of much help because it didn't provide any examples of converting a string to datetime. but, I have found the following to work.

convert (datetime, '30/12/04 1:10:30:000PM', 3);

i remember i used to use a combination of convert and cast to get datetime conversion working. I am just surprised that format such as
'dd/mm/yyyy' is not automatically supported in ms sql server. I had to parse the string so that i get dd/mm/yyyy in order for the conversion to work.

thanx for helping.

james : )|||Originally posted by Pat Phelan
My suggestion is to write some code (which could be a transact-sql expression) to convert your chacter date to a standard form. I would strongly suggest using the ISO standard 'yyyy-mm-dd hh:mm:ss.ttt" format. I've seen a euro-to-ISO time function, if you can't find it, I can either find or write one for you.

-PatP

hi, it would be good if you can show me how it's done, i don't know how to do any string manipulation in mssql.

thank you.

james :)