I just converted a SQL Server 2000 database to 2005. I have a job whose only
step does the following:
Declare @.Command char(240)
Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
Replace([name],' ','~')
From msdb..sysjobs where job_id=[JOBID])
exec master..xp_cmdshell @.Command, NO_OUTPUT
Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job was
passed to the job step. Under 2005, I get an error when I run the job saying
'JOBID' is undefined. Does anyone know what change I need to make?
Amos.Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>I just converted a SQL Server 2000 database to 2005. I have a job whose
>only step does the following:
> Declare @.Command char(240)
> Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
> Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
> Replace([name],' ','~')
> From msdb..sysjobs where job_id=[JOBID])
> exec master..xp_cmdshell @.Command, NO_OUTPUT
> Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job
> was passed to the job step. Under 2005, I get an error when I run the job
> saying 'JOBID' is undefined. Does anyone know what change I need to make?
> Amos.
>|||Thank you.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8AeOpt5FHA.3296@.TK2MSFTNGP09.phx.gbl...
> Please don't post independently in separate newsgroups. You can add
> multiple newsgroups to the header and then all the answers appear as one.
> See my reply in the other newsgroup.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>>I just converted a SQL Server 2000 database to 2005. I have a job whose
>>only step does the following:
>> Declare @.Command char(240)
>> Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
>> Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
>> Replace([name],' ','~')
>> From msdb..sysjobs where job_id=[JOBID])
>> exec master..xp_cmdshell @.Command, NO_OUTPUT
>> Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job
>> was passed to the job step. Under 2005, I get an error when I run the job
>> saying 'JOBID' is undefined. Does anyone know what change I need to make?
>> Amos.
>sql
Showing posts with label declare. Show all posts
Showing posts with label declare. Show all posts
Tuesday, March 27, 2012
Sunday, February 12, 2012
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 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 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 in raise error
Is there anyway to CAST or CONVERT an integer within RAISERROR?
I have tried all sorts of permeations to no avail, I have to declare another
variable and CAST into that to use it in the RAISERROR.
SET RAISERROR(101002,16,1,@.errorstring, (SELECT @.pid = CAST(@.pid as
varchar(15)),@.uid)
SET RAISERROR(101002,16,1,@.errorstring,CONVE
RT(varchar(15),@.pid),@.uid)Hi
You may want to change the definition of your message to use %d instead, or
try:
DECLARE @.charpid varchar(15)
SET @.charpid = CONVERT(varchar(15),@.pid)
SET RAISERROR(101002,16,1,@.errorstring,@.char
pid,@.uid)
John
"DazedAndConfused" wrote:
> Is there anyway to CAST or CONVERT an integer within RAISERROR?
> I have tried all sorts of permeations to no avail, I have to declare anoth
er
> variable and CAST into that to use it in the RAISERROR.
> SET RAISERROR(101002,16,1,@.errorstring, (SELECT @.pid = CAST(@.pid as
> varchar(15)),@.uid)
> SET RAISERROR(101002,16,1,@.errorstring,CONVE
RT(varchar(15),@.pid),@.uid)
>
>|||Thank you, I had seen the %d in an example last w
but drew a blank last
night when I needed it.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:B30FBE89-42BA-4541-8627-86ED08BC6DE9@.microsoft.com...
> Hi
> You may want to change the definition of your message to use %d instead,
> or
> try:
> DECLARE @.charpid varchar(15)
> SET @.charpid = CONVERT(varchar(15),@.pid)
> SET RAISERROR(101002,16,1,@.errorstring,@.char
pid,@.uid)
> John
> "DazedAndConfused" wrote:
>
I have tried all sorts of permeations to no avail, I have to declare another
variable and CAST into that to use it in the RAISERROR.
SET RAISERROR(101002,16,1,@.errorstring, (SELECT @.pid = CAST(@.pid as
varchar(15)),@.uid)
SET RAISERROR(101002,16,1,@.errorstring,CONVE
RT(varchar(15),@.pid),@.uid)Hi
You may want to change the definition of your message to use %d instead, or
try:
DECLARE @.charpid varchar(15)
SET @.charpid = CONVERT(varchar(15),@.pid)
SET RAISERROR(101002,16,1,@.errorstring,@.char
pid,@.uid)
John
"DazedAndConfused" wrote:
> Is there anyway to CAST or CONVERT an integer within RAISERROR?
> I have tried all sorts of permeations to no avail, I have to declare anoth
er
> variable and CAST into that to use it in the RAISERROR.
> SET RAISERROR(101002,16,1,@.errorstring, (SELECT @.pid = CAST(@.pid as
> varchar(15)),@.uid)
> SET RAISERROR(101002,16,1,@.errorstring,CONVE
RT(varchar(15),@.pid),@.uid)
>
>|||Thank you, I had seen the %d in an example last w
but drew a blank lastnight when I needed it.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:B30FBE89-42BA-4541-8627-86ED08BC6DE9@.microsoft.com...
> Hi
> You may want to change the definition of your message to use %d instead,
> or
> try:
> DECLARE @.charpid varchar(15)
> SET @.charpid = CONVERT(varchar(15),@.pid)
> SET RAISERROR(101002,16,1,@.errorstring,@.char
pid,@.uid)
> John
> "DazedAndConfused" wrote:
>
Subscribe to:
Posts (Atom)