SQL Server 2005 Image Data Type

Question

SQL Server 2005 Image Data Type

Robin 66

Hi,

We have an older version of Microsoft AX running on SQL Server 2005. I am trying to get the email address from the PRINTJOBSETTINGS column in SALESPRINTSETUP table. The PRINTJOBSETTINGS column is of Image data type. It does not contain images but information stored in such a way I don't know how to extract..

I ran the following query:

SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), PRINTJOBSETTINGS)) FROM SALESPRINTSETUP

And this is the results:

Can someone please tell me how to extract this information correctly? As you can see there is a space after every character which is making it more difficult to extract.

Thanks.

Ian Bates 91 Reputation points

2020-10-26T20:00:40.51+00:00

Hi @Robin , Could you give some example data from the SALESPRINTSETUP table?
scott_morris-ga 16 Reputation points

2020-10-26T20:43:21.357+00:00

Only a person familiar with the code that saves or uses that column will be able to help you. It might be this object (or more likely, an older version) stored as a blob.
Dan Guzman 9,516 Reputation points

2020-10-26T20:52:51.957+00:00

Try nvarchar(MAX) instead of varchar(MAX).
Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2020-10-26T22:39:55.437+00:00

Dan, you should have posted that as an Answer, and not a Comment. (As that is the answer to the question.)
Dan Guzman 9,516 Reputation points

2020-10-27T04:07:26.047+00:00

Erland, I suspect nvarchar(MAX) will be the answer but I am not certain without seeing the actual binary value. I will post it as an answer once Robin-8787 responds.
Robin 66 Reputation points

2020-10-27T08:21:50.837+00:00

HI @Dan Guzman ,

I was praying that will work but that didn't.

Thanks for your help.
Robin 66 Reputation points

2020-10-27T08:24:31.09+00:00

Hi @Ian Bates ,

I can't do that as it has customer email addresses. If I am able to produce some test record, I will post it.

Kind regards,
Robin 66 Reputation points

2020-10-27T08:29:12.967+00:00

Hi @scott_morris-ga ,

Thanks for that. Yes, I think only someone from Microsoft can help. That link is a good start. Need to find the correct object for the older version which Microsoft may have archived or removed from the public domains.
Dan Guzman 9,516 Reputation points

2020-10-27T10:47:35.757+00:00
It seems some of the contents are Unicode but the column contains other binary data as well. Reverse-engineering a binary object knowing nothing about the creating application is a challenge. Just a guess but a clue in the text is "Microsoft Print to PDF". You might try saving the value to a PDF file to see if you can view as a PDF. PowerShell example:

$connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=YourServer;Integrated Security=SSPI;Initial Catalog=YourDatabase") $connection.Open() $command = New-Object System.Data.SqlClient.SqlCommand("SELECT YourBinaryColumn FROM dbo.YourTable WHERE YourTableID = 1;", $connection) [System.IO.File]::WriteAllBytes("C:\Temp\PrintToPDF.pdf", ($command.ExecuteScalar()))
Robin 66 Reputation points

2020-10-27T12:11:23.25+00:00

Hi @Dan Guzman ,

I tried that and it created pdfs but can't open. The 'Microsoft Print to PDF' could be some instructions as the PRINTJOBSETTINGS column is used to store information on creating documents, saving them as pdfs and emailing them to customers using the email address stored within this column.

I think @scott_morris-ga is right. Since it is Microsoft AX, it is using the PrintJobSettings Class where it has one field and you have to use the correct Method to extract the information. But the PrintJobSettings.__k_PrintJobSettingsId Field is of integer type whereas the table column is image. So, I need to find the correct information for Microsoft AX 4.0 to figure out how to retrieve details out of this column.

@Ian Bates and @EchoLiu-MSFT have asked for sample data but I can't share that information unfortunately.
EchoLiu-MSFT 14,626 Reputation points

2020-11-02T09:39:19.28+00:00

Do you have any updates？
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

2 answers

Your answer

Ian Bates 91 Reputation points

2020-10-26T20:00:40.51+00:00

Hi @Robin , Could you give some example data from the SALESPRINTSETUP table?
scott_morris-ga 16 Reputation points

2020-10-26T20:43:21.357+00:00

Only a person familiar with the code that saves or uses that column will be able to help you. It might be this object (or more likely, an older version) stored as a blob.
Dan Guzman 9,516 Reputation points

2020-10-26T20:52:51.957+00:00

Try nvarchar(MAX) instead of varchar(MAX).
Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2020-10-26T22:39:55.437+00:00

Dan, you should have posted that as an Answer, and not a Comment. (As that is the answer to the question.)
Dan Guzman 9,516 Reputation points

2020-10-27T04:07:26.047+00:00

Erland, I suspect nvarchar(MAX) will be the answer but I am not certain without seeing the actual binary value. I will post it as an answer once Robin-8787 responds.
Robin 66 Reputation points

2020-10-27T08:21:50.837+00:00

HI @Dan Guzman ,

I was praying that will work but that didn't.

Thanks for your help.
Robin 66 Reputation points

2020-10-27T08:24:31.09+00:00

Hi @Ian Bates ,

I can't do that as it has customer email addresses. If I am able to produce some test record, I will post it.

Kind regards,
Robin 66 Reputation points

2020-10-27T08:29:12.967+00:00

Hi @scott_morris-ga ,

Thanks for that. Yes, I think only someone from Microsoft can help. That link is a good start. Need to find the correct object for the older version which Microsoft may have archived or removed from the public domains.
Dan Guzman 9,516 Reputation points

2020-10-27T10:47:35.757+00:00

It seems some of the contents are Unicode but the column contains other binary data as well. Reverse-engineering a binary object knowing nothing about the creating application is a challenge. Just a guess but a clue in the text is "Microsoft Print to PDF". You might try saving the value to a PDF file to see if you can view as a PDF. PowerShell example:

$connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=YourServer;Integrated Security=SSPI;Initial Catalog=YourDatabase") $connection.Open() $command = New-Object System.Data.SqlClient.SqlCommand("SELECT YourBinaryColumn FROM dbo.YourTable WHERE YourTableID = 1;", $connection) [System.IO.File]::WriteAllBytes("C:\Temp\PrintToPDF.pdf", ($command.ExecuteScalar()))
Robin 66 Reputation points

2020-10-27T12:11:23.25+00:00

Hi @Dan Guzman ,

I tried that and it created pdfs but can't open. The 'Microsoft Print to PDF' could be some instructions as the PRINTJOBSETTINGS column is used to store information on creating documents, saving them as pdfs and emailing them to customers using the email address stored within this column.

I think @scott_morris-ga is right. Since it is Microsoft AX, it is using the PrintJobSettings Class where it has one field and you have to use the correct Method to extract the information. But the PrintJobSettings.__k_PrintJobSettingsId Field is of integer type whereas the table column is image. So, I need to find the correct information for Microsoft AX 4.0 to figure out how to retrieve details out of this column.

@Ian Bates and @EchoLiu-MSFT have asked for sample data but I can't share that information unfortunately.
EchoLiu-MSFT 14,626 Reputation points

2020-11-02T09:39:19.28+00:00

Do you have any updates？
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

Answer 1

Tom Phillips 17,786

Just to be clear, the results you posted appears to be a "file" stored in an "image" data type, which is very common.

That is not something you scan for email addresses. You would need to know the format of every file type stored in that field.

0 comments

Answer 2

EchoLiu-MSFT 14,626

Hi @Robin ，

Please refer to:

    SELECT CONVERT(varbinary(max), CONVERT(varbinary(max), PRINTJOBSETTINGS)) FROM SALESPRINTSETUP

varchar(max)-------text
nvarchar(max)-----ntext
varbinary(max)----image

For more details, please refer to:
ntext, text, and image (Transact-SQL)

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Robin 66 Reputation points

2020-10-27T08:33:46.837+00:00

Hi @EchoLiu-MSFT ,

Thanks for that but that didn't work. Even though it is of Image data type, it is not an image and using varchar and nvarchar didn't resolve the problem.
EchoLiu-MSFT 14,626 Reputation points

2020-10-27T09:06:04.217+00:00

Could you share with us a minimal example (including create and insert statements) and expected output?

Echo

Share via

SQL Server 2005 Image Data Type

2 answers

Your answer