convert access IIF to sql in order by statement

ive been having a lot of trouble converting this last line from my access database to sql server. i was hoping someone could help
order by Name, IIF(Risk.Calc like H*, 1, IIF (Risk.Calc like M*,2,3)), Risk.Chance*risk.cons DESC, risk.title
any suggestions?I;m not sure I got the IIF true/False right...I went with IIF(expr, true, false)...if it's backwards just flip it in the code...

ORDER BY Name
-- , IIF(Risk.Calc like H*, 1, IIF (Risk.Calc like M*,2,3)), Risk.Chance*risk.cons DESC, risk.title
, CASE WHEN Risk.Calc LIKE 'H%' THEN 1
WHEN Riks.Calc LIKE 'M%' THEN 2
ELSE 3
END
, Risk.Tile|||i didnt think you could use IIF in sql|||I think brett was just reiterating your iif and submitting a case example as the replacement.|||yea thats what i thought after i looked at it, thanks for the help brett|||The "--" indicate that it's a comment line...good luck..

did it work?

convert Access DB to MS SQL with dtswiz(command line)

Hi!

I' m sitting here now for days and I still get no conclusion on my
problem. I have an MS ACCESS .mdb File and I would like to export it
with dtswiz.exe (from MS SQL Utility Tools) via command line. (If you
know other tools which are able to do that via command line I would
appreciate any suggestion)

I tried to find another Provider Parameter (like MSDASQL) for Access
but somehow it seems to me that Microsoft only got one. I' m also able
to address a Server which appears in the GUI but I don t need one for
my ACCess DB (I only tried, if the command parameters have any effect
on the converter).

I would be glad if someone could help me on this.

Thank you
Klaus Baumgarten"Klaus Baumgarten" <klaus.baumgarten@.gmx.net> wrote in message
news:c562e80d.0405110819.6eb845bc@.posting.google.c om...
> Hi!
> I' m sitting here now for days and I still get no conclusion on my
> problem. I have an MS ACCESS .mdb File and I would like to export it
> with dtswiz.exe (from MS SQL Utility Tools) via command line. (If you
> know other tools which are able to do that via command line I would
> appreciate any suggestion)
> I tried to find another Provider Parameter (like MSDASQL) for Access
> but somehow it seems to me that Microsoft only got one. I' m also able
> to address a Server which appears in the GUI but I don t need one for
> my ACCess DB (I only tried, if the command parameters have any effect
> on the converter).
> I would be glad if someone could help me on this.
> Thank you
> Klaus Baumgarten

I'm not totally sure what your issue is - are you asking how to execute a
DTS package from the command line? If so, you can save your package to a
..dts file and execute it with dtsrun.exe; you can use dtsrunui.exe to build
a working command line first.

If your problem is moving data from an Access database to an MSSQL one, then
this may help:

http://support.microsoft.com/default.aspx?kbid=285829

Simon

Conversion of DTS to SSIS command Line

I am trying to convert a command line using the dtexecui utility. I need to pass three parameters ; account number ,begin and end date to project.

What am i doing wrong ?

DTEXEC /DTS "\File System\Archive Data" /SERVER SRV2 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW \package /SET "Account_Number";"'00001'" /SET "File_Name";"'C:\Inetpub\wwwroot\output\Archive\'" /SET "Begin_Date";"'04/03/2006'" /SET "End_Date";"'04/04/2006'"

Error I get

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 9:52:49 AM
Warning: 2006-04-05 09:52:51.58
Code: 0x80012018
Source: Archive Data
Description: The configuration entry, "Account_Number", has an incorrect form
at because it does not begin with the package delimiter. Prepend "\package" to t
he package path.
End Warning
Warning: 2006-04-05 09:52:51.58
Code: 0x80012017
Source: Archive Data
Description: The package path referenced an object that cannot be found: "Acc
ount_Number". This occurs when an attempt is made to resolve a package path to a
n object that cannot be found.
End Warning
DTExec: Could not set Account_Number value to '00001'.
Started: 9:52:49 AM
Finished: 9:52:51 AM
Elapsed: 2.172 seconds

Your command line is not correct as each set command needs a package path starting with \package just as the error message indicates. As I don't know tasks these properties belong to I can't give you the exact path but in general the set option should look something like "\Package.rest_of_path_to_property". You can use the configurations on the package to identify what the package path should look like. You should also remove the \package from the command line outside of the set because that is invalid.

HTH,

Matt

Conversion ERROR

This is the error message I get: :(
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

This is the query:
Select Qual_ins.CompanyCode, Qual_ins.ParticipantCode, Qual_ins.Ins_Code, Qual_ins.Plan_Code,
dbo.PremiumRate(Qual_Ins.Crit,Qual_Ins.PQB_Spec,Pl an_Mas.Extend_Fee,
Qual_Ins.Adjpremium,Qual_Ins.Adjpremiumper,Qual_In s.Adjpremend,
GetDate(),Qual_Ins.Cover_Amt, Plan_Mas.CR_A, Plan_Mas.CR_B,
Plan_Mas.CR_C, Plan_Mas.CR_D, Plan_Mas.CR_E, Plan_Mas.CR_F,
Plan_Mas.CR_G, Plan_Mas.CR_H, Plan_Mas.CR_I, Plan_Mas.CR_J,
Plan_Mas.CR_K, Plan_Mas.CR_L, Plan_Mas.CR_M, Plan_Mas.CR_N,
Plan_Mas.CR_O) AS PremiumRate
FROM Qual_ins, Plan_Mas
WHERE Qual_Ins.CompanyCode = 'ACME'
AND Qual_ins.ParticipantCode = 4
AND Plan_Mas.CompanyCode = Qual_ins.CompanyCode
AND Plan_Mas.Ins_Code = Qual_ins.Ins_Code
AND Plan_Mas.Plan_Code = Qual_ins.Plan_Code
Order BY Qual_ins.Ins_Code

Please let me know if you need to see my PremiumRate (User Defined Function) in order to help me elimate this error message.
Any help is appreciate!
I'm new to this... "Hello" to all!

ShuviBased on the error message, I'd guess that you are trying to convert a string (CHAR or VARCHAR) to a DATETIME or a SMALLDATETIME. If that is the case, then one or more rows in your data isn't a valid date string.

-PatP

Controlling Crystal Reports 9.0 Grids with VB

Hello,
I'm new to Crystal reports and am finishing up my first vb app. I need to be able to control the "Draw Custom Line" property of the Crystal Reports grid within VB 6.0. Does anyone know the Grid property I use in VB to control this?
Thanks so much!
Tricia =)aha! After a full day of searching for the answer, I think I found a comparable solution to this problem. =)

Solution: Split Dual Axis Grid
- Bottom axis range from x to alert level
- Upper axis range from alert level to x

Pros:
- The alert level line can be placed on the grid and it can be a variable amount calculated in VB so that we can normalize the data appropriately.

Cons:
- the grid must have 2 data points in order to have the split axis option available. The problem with this is that we need to show the marker for each point in the event of a first time sampling and only one level to show on the grid. I am using the alert level for the second data point and so the markers are showing only the alert level line, but it still looks a bit funky, but at least I have the line!!!

control yank in 2005 Query Designer

How can I assign ctrl+y to remove the line the cursor is on when designing a query in management studio like it was defaulted on in 2000? Muchos gracias.I hate sql 2005 it can suck a duck for all i give a damn please let there be a way to restore my databases back to 2000 how do ctrl+f4 for search this is worse than switching from mssql to oracle on unix why is no backward compatability maintained did apple by these perverts out or something? peace out my ninjas you have been a great lot of help may you eternally be blessed with your just rewards

CONTAINS not working

When I run the query,
SELECT nn
FROM tt
WHERE CONTAINS(nn, 'test')
I get the error
Server: Msg 7601, Level 16, State 2, Line 1
Cannot use a CONTAINS or FREETEXT predicate on table 'tt' because it is
not full-text indexed.
How can I set full-text index?
MadhivananHi
Just rey this way
EXEC sp_fulltext_column 'tt', 'nn', 'add'
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Madhivanan" wrote:

> When I run the query,
> SELECT nn
> FROM tt
> WHERE CONTAINS(nn, 'test')
> I get the error
> Server: Msg 7601, Level 16, State 2, Line 1
> Cannot use a CONTAINS or FREETEXT predicate on table 'tt' because it is
> not full-text indexed.
> How can I set full-text index?
> Madhivanan
>|||When I execute that I get the error
Server: Msg 15601, Level 16, State 1, Procedure sp_fulltext_column,
Line 13
Full-Text Search is not enabled for the current database. Use
sp_fulltext_database to enable Full-Text Search.
Madhivanan|||check this URL
http://msdn.microsoft.com/library/d...r />
_0kjd.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Madhivanan" wrote:

> When I execute that I get the error
> Server: Msg 15601, Level 16, State 1, Procedure sp_fulltext_column,
> Line 13
> Full-Text Search is not enabled for the current database. Use
> sp_fulltext_database to enable Full-Text Search.
>
> Madhivanan
>|||can I enable fulltext indexing with os win xp pro?
"Chandra" wrote:
> Hi
> Just rey this way
> EXEC sp_fulltext_column 'tt', 'nn', 'add'
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Madhivanan" wrote:
>

Constructing Email message

Hi,

I am constructing a Message (Body) for sending our Emails. It is around
3000 characters long. But for whatever reason, the last line seems to
be broken with a "!" exclamatory mark in it, which results in
displaying the constructed image path as a broken one.

How to resolve this ?. Thanks.

Regards,
KarthickNo idea - what version of SQL Server, how are you building the message
body, which data type are you using for the message body, how are you
going to send the mail, what does "image path" refer to, can you post a
simplified SQL script to show the problem etc.

As a complete guess, you've declared the message body as varchar but
the path has Unicode characters in it, so you need to use nvarchar. But
without more information, that's probably wrong.

Simon|||Simon, thanks for your reply.

I am using SQL Server 2000. The Message body is NTEXT datatype in the
database. And I am constructing the message body like the following:

SELECT @.MessageBody = '<!DOCTYPE HTML PUBLIC "-//IETF//DTD
HTML//EN">'
SELECT @.MessageBody = @.MessageBody + '<html><head><meta
http-equiv="Content-Type" '
SELECT @.MessageBody = @.MessageBody + 'content="text/html;charset=' +
@.CharSet + '">'
SELECT @.MessageBody = @.MessageBody + '<title>Title goes here:
Ticket</title>'
SELECT @.MessageBody = @.MessageBody + '<link rel="stylesheet"
type="text/css" '

-- I have 67 lines of the @.MessageBody construction and at the end of
the stored procedure I am doing a EXEC to insert this @.MessageBody onto
another table and our third party vendor picks up the Emails from the
table and send them out. So I don't send out the emails manually, all I
do is insert the email contents onto a table and the rest is taken care
of.

Please help. Thanks.

Karthick|||It's still a little unclear (at least to me) - @.MessageBody can't be
ntext, because you can't declare an ntext variable. And you don't say
where the invalid data appears - if you SELECT @.MessageBody before
INSERTing it, is the data correct? Or is it only wrong after INSERTing?
And what does "doing an EXEC" mean? Are you using dynamic SQL, or a
stored procedure to do the INSERT?

Rather than describing your problem, I suggest that you try to produce
a (simplified) script to illustrate your problem - code is always
clearer than a description, and if other people can quickly copy and
paste into Query Analyzer, you're more likely to get a useful answer.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon