Database

Euro symbol & CDO via T-SQL

8-Dec-08 5:21

Civic06 wrote:
its not fixed length right

No it's not. Single CLOB can store gigabytes of data, so if it would be fixed length, it would eat out the disk space quite fast.

The minimum storage size is size of one block but depending on the CHUNK parameter, it can be multiple blocks.

The need to optimize rises from a bad design.My articles[^]

RichardGrimmer8-Dec-08 3:57

8-Dec-08 3:57

Apologies if this isn't the correct forum.

OK, the situation is that our web app has a feature to send autometed emails. The emails may potentially contain the Euro symbol ('€'). The DB server holds the currency symbol, and it can be SELECTed correctly. However, when the symbol is used in an auto-email, it's replaced with a '?'

The sending is performed using CDO, encaspulated into an SP. The sp itself sets up the various requisites through sp_OASetProperty, and references the CDO schema...eg:

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

I'm able to correctly send a mail with a Euro symbol in it though Outlook, using the same exchange server.

Anyone have any idea what may be causing this / seen it before? Most of the links I've found seem to hint at setting Message.BodyPart.Codepage / CharSet, but I'm unable to find out how to do the same thing in T-SQL....

C# has already designed away most of the tedium of C++.

Ben Fair8-Dec-08 4:08

Ben Fair

8-Dec-08 4:08

You could try setting the body to HTML if it's plain text and that may make a difference.

Keep It Simple Stupid! (KISS)

RichardGrimmer8-Dec-08 4:32

8-Dec-08 4:32

Hi and thanks for the quick reply.

Unfortunately, it's already an HTML email. I've just tried swapping to a text body just in case, and same result.

Cheers for the suggestion though!

C# has already designed away most of the tedium of C++.

Wendelius8-Dec-08 5:42

8-Dec-08 5:42

Not sure about this, but try setting CDO.Message's BodyPart.Charset to utf-8.

The need to optimize rises from a bad design.My articles[^]

RichardGrimmer8-Dec-08 5:44

Re: Euro symbol & CDO via T-SQL [modified]

8-Dec-08 5:44

Thanks for the reply....

Yes, that's what I suspect is needed, however, I'm unable to find how to do it in T-SQL....

I've tried varous combinations of OASets, but keep getting errors, and can't seem to find the "correct" way to do it...

Thanks again for your time...

C# has already designed away most of the tedium of C++.

Wendelius8-Dec-08 5:55

8-Dec-08 5:55

I haven't really used that (always used either sql mail or database mail). I'd guess it could be something like :

sp_OASetProperty @iMsg, 'BodyPart.Charset.Value','"utf-8"'

Or some of it's variations. I guess you'll have to google around or test several variations.

If it would be possible for you I'd suggest that you would use database mail (if having SQL Server 2005 or above). It's a whole lot simpler and purely smtp.

The need to optimize rises from a bad design.My articles[^]

modified on Monday, December 8, 2008 5:14 PM

RichardGrimmer8-Dec-08 23:28

8-Dec-08 23:28

Got it in the end.....

EXEC @hr = sp_OASetProperty @iMsg, 'BodyPart.Charset', 'windows-1252'

Seems to do the trick....Many thanks for the help there chap.

C# has already designed away most of the tedium of C++.

Wendelius9-Dec-08 5:24

Select column name where...

9-Dec-08 5:24

RichardGrimmer wrote:
Many thanks for the help there chap

You're welcome.

The need to optimize rises from a bad design.My articles[^]

Muammar©6-Dec-08 20:53

6-Dec-08 20:53

How to select the column names that contains a value of say 0 in the first row
Please help Sigh | :sigh:

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Wendelius6-Dec-08 22:38

6-Dec-08 22:38

I think you could write a little procedure (or program). If you need the columns only from one table, just select the first row and loop through columns. If you need info from several tables, you could use system tables and based on the info from there, loop tables, fetch first row and then loop through columns. You'll need to use dynamic statements based on system table information.

The need to optimize rises from a bad design.My articles[^]

Mohammad Dayyan8-Dec-08 13:52

8-Dec-08 13:52

If you want it in SQL , so :

SELECT names FROM tableName WHERE id IN ( SELECT MIN(id) FROM tableName )

You should identify first row by a field , in this case I used id

Muammar©12-Dec-08 19:24

12-Dec-08 19:24

Mohammad Dayyan wrote:
SELECT names FROM tableName WHERE id IN ( SELECT MIN(id) FROM tableName )

Hey Mohammad,
More help please Blush | :O

By selecting column names, I'll get all the columns even if they contain a value other than the minimum in the sub select statement, are you sure it can be done from sql??

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Mohammad Dayyan12-Dec-08 20:39

12-Dec-08 20:39

It should work, but I think sou can use this query too :

SELECT name, MIN(id)
       FROM tableName ;

Muammar©12-Dec-08 21:39

12-Dec-08 21:39

Hey Mohammad,
Thank you for your kind help, but do you understand my question??
I want to get the column names of a table that has for example the value '1' in the first and only row of that table

Example:

A       B       C
--     --      --
0       0       1

The output should be:

Please tell me if you can make this happen.

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Mohammad Dayyan13-Dec-08 4:41

13-Dec-08 4:41

Thanks Muammar. I've got it right now $Shucks | :-\$
I think you should use several queries

select A from tablename where A=1
select B from tablename where B=1
select C from tablename where C=1

Muammar©13-Dec-08 18:27

13-Dec-08 18:27

No, thanks to you Mohammad,
That should do it, but do you have any idea how can we combine the 3 select statements so they would return a single row??

Thank you for being there until now Smile | :)

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Muammar©13-Dec-08 19:26

13-Dec-08 19:26

Thanks Mohammad, UNION ALL was what I needed to combine multiple select statements and get them in one column

Column Name
------------
A
B
C
D

Column Value
------------
0
0
1
0

Though, I still need to combine those tow columns in one output, can you help in that too my friend Blush | :O

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Mohammad Dayyan13-Dec-08 20:10

13-Dec-08 20:10

Muammar© wrote:
Thank you for being there until now

Oh no problem Muammar, I'm happy to help someone .

Muammar© wrote:
That should do it, but do you have any idea how can we combine the 3 select statements so they would return a single row??

I'm so sorry , I don't know

how to transfer all data to another database

13-Dec-08 20:17

Don't worry Mohammad, thanks anyways for your efforts, I think it's some sort of a join query and I'm googling for join queries.

Thanks again Mohammad.

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

harcaype6-Dec-08 4:12

harcaype

6-Dec-08 4:12

hi. How do you transfer data of a database from another database. I am using MS SQL. I have hundreds of data there and I need to transfer it to another database since I'm having problems with the previous one with regards to attaching it to the dataset with crystal reports. It only recognizes the newly created databases in SQL Express and I don't know why. So I just planned to recreate my recent database and transfer all of the existing data to a new one. How do I do that? Thanks!

Wendelius6-Dec-08 4:36

6-Dec-08 4:36

harcaype wrote:
How do you transfer data of a database from another database

SSIS is one way if you have to do this continuously but you can also use backup/restore or detach/attach. They would be easier for a single transfer.

harcaype wrote:
It only recognizes the newly created databases in SQL Express

If this is the root problem, I think you should concentrate in this. There's no reason why Crystal would connect only to a new database if the database you're trying to connect to is in good condition.

The need to optimize rises from a bad design.My articles[^]

harcaype6-Dec-08 4:54

harcaype

6-Dec-08 4:54

Root problem? I'm sorry, but what did you mean by root problem? I'm not really sure myself though. A few days ago I was able to attach the tables on the dataset with the same database, and get recognized in the Crystal Report Wizard. Then when I duplicated my project file, and redo what I did in my "backed-up" project file, that started the strange problem. And if you mean that getting and successfully querying the database is a good condition, Yes. My database is in good condition for that part.

Wendelius6-Dec-08 5:13

6-Dec-08 5:13

harcaype wrote:
A few days ago I was able to attach the tables on the dataset with the same database, and get recognized in the Crystal Report Wizard. Then when I duplicated my project file, and redo what I did in my "backed-up" project file, that started the strange problem

Yes that's what I mean by root problem. If your new project acts differently than the previous project I would guess that the problem is actually in the project, not in the database. But then again I don't know enough about the solution so this is merely guessing.

Try for example restoring a backup to a new database. If that solves the problem then everything should be ok. If it doesn't it would still give you more information about the problem.

The need to optimize rises from a bad design.My articles[^]