forcing dbsqlexec to exit cleanly

I am developing a simple DB-Library program in C calling SQL Server 2000 on
windows 2003 and NT 4. I have some T-SQL code that checks for the existence
of a table and want to abort the program if the table doesn't exist. I issue
a raiserror if the table doesn't exist and then call RETURN.

I construct the string using sprintf and pass it dbfcnd and dbsqlexec. Since
the commands work, there is no error to halt the execution of the program.
Is there an easy, clean way to force dbsqlexec to fail? Do I need a stored
procedure to return an error code and then deal with that?

Thanks for any advice,

-GaryHi

I have not used DB-Library but using a store procedure is usually the best
way to provide this logic. You can use an output parameter although if you
didn't want to use the return parameter.

John

"Gary" <levenson_g@.comcast.net> wrote in message
news:5dedncjrkf-uJb3fRVn-ug@.comcast.com...
>I am developing a simple DB-Library program in C calling SQL Server 2000 on
> windows 2003 and NT 4. I have some T-SQL code that checks for the
> existence
> of a table and want to abort the program if the table doesn't exist. I
> issue
> a raiserror if the table doesn't exist and then call RETURN.
> I construct the string using sprintf and pass it dbfcnd and dbsqlexec.
> Since
> the commands work, there is no error to halt the execution of the program.
> Is there an easy, clean way to force dbsqlexec to fail? Do I need a stored
> procedure to return an error code and then deal with that?
> Thanks for any advice,
> -Gary
>|||[posted and mailed, please reply in news]

Gary (levenson_g@.comcast.net) writes:
> I am developing a simple DB-Library program in C calling SQL Server 2000
> on windows 2003 and NT 4.

How come you are using DB-Library? It's a very good API, but alas Microsoft
thinks differently and has not developed DB-Library since the release of
SQL 6.5. Because of this, there are many ffeatures in SQL Server that
you don't have support for with DB-Library.

Some future version of SQL Server will not support connecting from
DB-Library at all. And already the next version of SQL Server will come
without the files to compile, link and run DB-Library.

If you have legacy software you have. But don't use DB-Library for new
development. Use ODBC or OLE DB instead.

> I have some T-SQL code that checks for the existence of a table and want
> to abort the program if the table doesn't exist. I issue a raiserror if
> the table doesn't exist and then call RETURN.
> I construct the string using sprintf and pass it dbfcnd and dbsqlexec.
> Since the commands work, there is no error to halt the execution of the
> program. Is there an easy, clean way to force dbsqlexec to fail? Do I
> need a stored procedure to return an error code and then deal with
> that?

If you insist on using DB-Library, you should set up an message
handler with dbmsghandle. This is how DB-Libarary notifies you of
SQL errors: through a callback. I believe by setting the return
status in the message handler, you can control whether the command
fails or not.

Also, after each submitted command, you should always loop with
dbresults and dbnextrow to get all result sets and rows. The message
handler may in fact no be invoked until you call dbresults, since
the message also is a result in some meaning.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the replies. I have a legacy program that I am modifying for a
quick one-off project.

I do have a message handler installed. I solved the problem by putting the
code that checks for the table into a stored procedure and then calling it.
Since it can return a value, returning a non-zero value from the sproc does
the trick and dbsqlexec throws an error.

Thanks again,

-Gary

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns960A47B4CCDYazorman@.127.0.0.1...
> [posted and mailed, please reply in news]
> Gary (levenson_g@.comcast.net) writes:
> > I am developing a simple DB-Library program in C calling SQL Server 2000
> > on windows 2003 and NT 4.
> How come you are using DB-Library? It's a very good API, but alas
Microsoft
> thinks differently and has not developed DB-Library since the release of
> SQL 6.5. Because of this, there are many ffeatures in SQL Server that
> you don't have support for with DB-Library.
> Some future version of SQL Server will not support connecting from
> DB-Library at all. And already the next version of SQL Server will come
> without the files to compile, link and run DB-Library.
> If you have legacy software you have. But don't use DB-Library for new
> development. Use ODBC or OLE DB instead.
> > I have some T-SQL code that checks for the existence of a table and want
> > to abort the program if the table doesn't exist. I issue a raiserror if
> > the table doesn't exist and then call RETURN.
> > I construct the string using sprintf and pass it dbfcnd and dbsqlexec.
> > Since the commands work, there is no error to halt the execution of the
> > program. Is there an easy, clean way to force dbsqlexec to fail? Do I
> > need a stored procedure to return an error code and then deal with
> > that?
> If you insist on using DB-Library, you should set up an message
> handler with dbmsghandle. This is how DB-Libarary notifies you of
> SQL errors: through a callback. I believe by setting the return
> status in the message handler, you can control whether the command
> fails or not.
> Also, after each submitted command, you should always loop with
> dbresults and dbnextrow to get all result sets and rows. The message
> handler may in fact no be invoked until you call dbresults, since
> the message also is a result in some meaning.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

forcing data format mask without modifting code

I have statement which is comparing a smalldatetime column to literal string as follows:

sales_date ='21-9-2004 0:0:0.000'

when I run the statement in query analyzer it bombs out with:

Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

If I alter the format of the date literal to '2004-09-21 00:00:00' the statement works.

Is there anyway of forcing the statement to treat '21-9-2004 0:0:0.000' as '2004-09-21 00:00:00' without modifying the statement itself ?There might be a global setting for how datetime fields are treated by default, I've never been tempted to go look for it. I'd rather do a CONVERT instead. There's also a 'SET DATEFORMAT' that might work for you.

What's wrong with changing the statement?|||Where is the data coming from? Is it always in that format? Can you use SUBSTRING?|||Unfortunately the data is in a liternal string 'DD-MM-YYYY' when in fact I require it be in 'MM-DD-YYYY' format.|||Couldn't you do something like...

cast(day(sales_date()) as varchar(2)) + '-' +
cast(month(sales_date()) as varchar(2)) + '-' +
cast(year(sales_date()) as char(4)) + ' 0:0:0.000'

??

Forcing '0' to set number of decimal places

Ok heres my problem.
Ive got a field that is rounded off to a set number of decimal places that
is defind in another field. Heres the code...
=ROUND (Fields!Transaction_Quan_.Value, Fields!Decimal_Places.Value)
my problem is that when the value is zero, it returns just 0, not 0.00 for
example. This is a requirement of the report.
Ive tried using the format feature, and dont have access to the SQL at the
moment to so some sort of CAST, it all needs to be done in RS for now.
Any ideas?
Cheers
Rob.Quick work around:
=IIF(Fields!Transaction_Quan_.Value >0, ROUND
(Fields!Transaction_Quan_.Value, Fields!Decimal_Places.Value), "0.00")
There's probably a more scientific way to do it, but that should make 0
appear as 0.00. ;)
Kaisa M. Lindahl Lervik
"MACNR" <MACNR@.discussions.microsoft.com> wrote in message
news:97D2A9F3-F623-4D3C-81B3-A06ECB41E8CB@.microsoft.com...
> Ok heres my problem.
> Ive got a field that is rounded off to a set number of decimal places that
> is defind in another field. Heres the code...
> =ROUND (Fields!Transaction_Quan_.Value, Fields!Decimal_Places.Value)
> my problem is that when the value is zero, it returns just 0, not 0.00 for
> example. This is a requirement of the report.
> Ive tried using the format feature, and dont have access to the SQL at the
> moment to so some sort of CAST, it all needs to be done in RS for now.
> Any ideas?
> Cheers
> Rob.
>|||Thanks for the reply kaisa, but the main problem is that 'the man' wants 0's
to be rounded to the correct number of decimal places aswell. so if the
decimal places amount is 4, then it needs to be 0.0000 etc. Irritating i know.
Cheers anyway though ;)
"Kaisa M. Lindahl Lervik" wrote:
> Quick work around:
> =IIF(Fields!Transaction_Quan_.Value >0, ROUND
> (Fields!Transaction_Quan_.Value, Fields!Decimal_Places.Value), "0.00")
> There's probably a more scientific way to do it, but that should make 0
> appear as 0.00. ;)
> Kaisa M. Lindahl Lervik
> "MACNR" <MACNR@.discussions.microsoft.com> wrote in message
> news:97D2A9F3-F623-4D3C-81B3-A06ECB41E8CB@.microsoft.com...
> > Ok heres my problem.
> >
> > Ive got a field that is rounded off to a set number of decimal places that
> > is defind in another field. Heres the code...
> >
> > =ROUND (Fields!Transaction_Quan_.Value, Fields!Decimal_Places.Value)
> >
> > my problem is that when the value is zero, it returns just 0, not 0.00 for
> > example. This is a requirement of the report.
> >
> > Ive tried using the format feature, and dont have access to the SQL at the
> > moment to so some sort of CAST, it all needs to be done in RS for now.
> >
> > Any ideas?
> >
> > Cheers
> >
> > Rob.
> >
> >
>
>

Force Excel Column type when exporting from SSRS

Hi all,

I have a tricky behavior here. I have a column in my report which contains alphanumeric codes. When I have a code like 17E001 and I export the report to Excel, excel kindly shows that alphanumeric code to 1+E7 and the value of the column is changed to 1700 which is defintly not what I want.

So I was wondering if there is any way to force the column types when exporting from SSRS?

Sbastien.

By the way if there is a way to force all columns to be formated as Text that will do for me as the excel reports are only used to process data using SSIS.

For Loop help

I have a problem not receiving any data back from a for loop in my report. The column data shows only as False or True.

Here is my code below. The one in VB and the one i have in Crystal Reports

Crystal Reports Code:

Dim ServicePeriod As number
ServicePeriod = {command.Advisor_Service_Period}
Dim amount As number
Dim i as number
For i=1 To 28
If ServicePeriod > 53 Then
formula = amount =+ 2000
ElseIf ServicePeriod >= 27 And ServicePeriod <= 53 Then
formula = amount =+ 1500
ElseIf ServicePeriod >= 1 And ServicePeriod <= 26 Then
formula = amount =+ 1000
ElseIf ServicePeriod = 0 Then
formula = amount =+ 800
End If
ServicePeriod =+ 1
Next i

VB Code:

Dim ServicePeriod As Integer = 1
Dim amount As Integer
For i As Integer = 1 To 28
If ServicePeriod > 53 Then
amount += 2000
ElseIf ServicePeriod >= 27 And ServicePeriod <= 53 Then
amount += 1500
ElseIf ServicePeriod >= 1 And ServicePeriod <= 26 Then
amount += 1000
ElseIf ServicePeriod = 0 Then
amount += 800
End If
ServicePeriod += 1Next iI don't think you can use += in Crystal to perform addition, so I think you are returning whether amount = value, i.e. a boolean.|||I don't think you can use += in Crystal to perform addition, so I think you are returning whether amount = value, i.e. a boolean.

Thanks for the quick response. removing the = helped eliminate the bool problem but for some reason i am still getting bad data. Should i maybe take a different approach on how to retreive this data? It returns 1000 for every record and does not seem to loop.|||Is 1000 the correct value for the first record in the report?
Did you put WhilePrintingRecords at the top of the formula?
I see you're using Basic syntax, which I'm not particularly familiar with. Does setting the formula 'variable' result in the formula ending? Maybe you need to use another variable to hold the calculated value and return it as the formula result at the end.|||Is 1000 the correct value for the first record in the report?
Did you put WhilePrintingRecords at the top of the formula?
I see you're using Basic syntax, which I'm not particularly familiar with. Does setting the formula 'variable' result in the formula ending? Maybe you need to use another variable to hold the calculated value and return it as the formula result at the end.

It does not seem to loop and add values. It instead just checks once and adds a value rather than looping for a set amount of times. How would you write the loop with crystal syntax. I have been stuck with this for a while any help is greatly appreciated.|||This is still basic syntax:

whileprintingrecords
Dim ServicePeriod As number
Dim amount As number
Dim i as number

amount = 0
ServicePeriod = 54

For i=1 To 28
If ServicePeriod > 53 Then
amount = amount + 2000
ElseIf ServicePeriod >= 27 And ServicePeriod <= 53 Then
amount = amount + 1500
ElseIf ServicePeriod >= 1 And ServicePeriod <= 26 Then
amount = amount + 1000
ElseIf ServicePeriod = 0 Then
amount = amount + 800
End If
ServicePeriod = ServicePeriod + 1
Next i

formula = amount|||This is still basic syntax:

whileprintingrecords
Dim ServicePeriod As number
Dim amount As number
Dim i as number

amount = 0
ServicePeriod = 54

For i=1 To 28
If ServicePeriod > 53 Then
amount = amount + 2000
ElseIf ServicePeriod >= 27 And ServicePeriod <= 53 Then
amount = amount + 1500
ElseIf ServicePeriod >= 1 And ServicePeriod <= 26 Then
amount = amount + 1000
ElseIf ServicePeriod = 0 Then
amount = amount + 800
End If
ServicePeriod = ServicePeriod + 1
Next i

formula = amount

works perfectly. Thanks for all the help.