Showing posts with label uniqueidentifier. Show all posts
Showing posts with label uniqueidentifier. Show all posts

Tuesday, March 20, 2012

Convert varchar to uniqueidentifier

When I need to have more info about a process, I run this kind of query:
SELECT PROGRAM_NAME FROM master..sysprocesses WHERE program_name LIKE
'SQLAgent - TSQL JobStep (Job %'
The result might be something like this:
SQLAgent - TSQL JobStep (Job 0x9BD0927CE9086241B582AAAAD7D3B86D : Step
1)
When I want to have more info about that particular job, I run this
query:
SELECT * FROM msdb..sysjobs WHERE job_id =
0x9BD0927CE9086241B582AAAAD7D3B86D
Now how do I combine these two queries? The first version you might
suggest is:
SELECT *
FROM msdb..sysjobs
WHERE job_id IN
(SELECT SUBSTRING(program_name,30,34)
FROM master..sysprocesses
WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job %')
This gives the result
Syntax error converting from a character string to uniqueidentifier.
Can you help me with this? How to convert the result of the subquery to
uniqueidentifier? I didn't succeed to use the usual convert function
CONVERT(UNIQUEIDENTIFIER,SUBSTRING(progr
am_name,30,34)).
MarkTry this (untested):
SELECT *
FROM msdb..sysjobs
WHERE cast(job_id as nvarchar(128)) IN
(SELECT cast(SUBSTRING(program_name,30,34) as nvarchar(128))
FROM master..sysprocesses
WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job %')
ML|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:9FB96207-78A4-4566-9AEC-8294830AABAB@.microsoft.com...
> Try this (untested):
> SELECT *
> FROM msdb..sysjobs
> WHERE cast(job_id as nvarchar(128)) IN
> (SELECT cast(SUBSTRING(program_name,30,34) as nvarchar(128))
> FROM master..sysprocesses
> WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job %')
>
> ML
Thanks, but that doesn't work either - I tested. But at least that doesn't
give any errors, just an empty result.
However, I found out, that this works. Quite ugly one, but it works.
SELECT *
FROM msdb..sysjobs
WHERE job_id IN
(SELECT SUBSTRING(program_name,38,2) +
SUBSTRING(program_name,36,2) +
SUBSTRING(program_name,34,2) +
SUBSTRING(program_name,32,2) + '-' +
SUBSTRING(program_name,42,2) +
SUBSTRING(program_name,40,2) + '-' +
SUBSTRING(program_name,46,2) +
SUBSTRING(program_name,44,2) + '-' +
SUBSTRING(program_name,48,4) + '-' +
SUBSTRING(program_name,52,12)
FROM master..sysprocesses
WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job %')
Mark

Tuesday, February 14, 2012

Convert from unicodestring to uniqueidentifier

Hello,
I have a XML data source containing strings representing GUIDs (ie <modelAsset id="e04ba23c-0401-43a9-b40c-b4c3d580caa9" />) and I need to fill a SQL table with uniqueidentifier column.

I added Data Conversion component setting the data type as DT_GUID.

Running the package I get the following error message:

Error: 0xC02020C5 at Import MyALM settings, Data Conversion [1301]: Data conversion failed while converting column "id" (4645) to column "GUID" (5912). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Error: 0xC0209029 at Import MyALM settings, Data Conversion [1301]: The "output column "GUID" (5912)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "GUID" (5912)" specifies failure on error. An error occurred on the specified object of the specified component.

How can I solve the problem ?

Thanks,
PierreWhile the error message could use some improvement the problem is that your strings that represent guids are not exactly correct. A string representation of a GUID has opening and closing curly braces ({}) and yours do not. If you wrap the column with curly braces (you could use a derived column for this) then your conversion should succeed.

HTH,
Matt|||

Thanks - I had the same problem.

It would be useful if the documentation for uniqueidentifer was updated to reflect the need for { and }.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/b026035b-f3d2-4d70-989d-3884b4ca0233.htm

Currently it states:

"converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value."

|||

So did you use the Send Feedback link then?

-Jamie

Convert from unicodestring to uniqueidentifier

Hello,
I have a XML data source containing strings representing GUIDs (ie <modelAsset id="e04ba23c-0401-43a9-b40c-b4c3d580caa9" />) and I need to fill a SQL table with uniqueidentifier column.

I added Data Conversion component setting the data type as DT_GUID.

Running the package I get the following error message:

Error: 0xC02020C5 at Import MyALM settings, Data Conversion [1301]: Data conversion failed while converting column "id" (4645) to column "GUID" (5912). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Error: 0xC0209029 at Import MyALM settings, Data Conversion [1301]: The "output column "GUID" (5912)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "GUID" (5912)" specifies failure on error. An error occurred on the specified object of the specified component.

How can I solve the problem ?

Thanks,
PierreWhile the error message could use some improvement the problem is that your strings that represent guids are not exactly correct. A string representation of a GUID has opening and closing curly braces ({}) and yours do not. If you wrap the column with curly braces (you could use a derived column for this) then your conversion should succeed.

HTH,
Matt|||

Thanks - I had the same problem.

It would be useful if the documentation for uniqueidentifer was updated to reflect the need for { and }.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/b026035b-f3d2-4d70-989d-3884b4ca0233.htm

Currently it states:

"converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value."

|||

So did you use the Send Feedback link then?

-Jamie