determine if value in field is number

I have a varchar field in a table that could contain values such as 123,
345, ABC, or CDF
I want to do a select statement on the table to return records where this
field could be number (123, 345) and exclude those that are not (ABC, CDF).
Is there an IsNum function or something like that?
hi matt,
make use of isnumeric function, which returns 1 for valid numeric value.
ex:
select *
from
<table>
where isnumeric(col) = 1
Vishal Parkar
vgparkar@.yahoo.co.in

Detecting the network UserID

Hey guys,
I would like to determine the network id of the current user so that I can
pass that value to a stored procedure that the report references.
I tried add the following code in the Code tab of the Report Properties tab:
Function GetUserID() AS String
Return System.Environment.UserName
End Function
I then referenced this function as the default value for the @.vcUserId
argument that the stored procedure wants by assigning =Code.GetUserID() as
the Non-queried default value for the report parameter.
This approach works well when I preview the report in Visual Studio.
Unfortunately, it fails when I deploy the report to the server.
Can anyone offer some suggestions on how to approach this issue?
Thanks in advance,
-JimI'd guess that the reason this doesn't work is because when you run it
in VS.net the application is running as you or some other acceptable
local user
(http://msdn2.microsoft.com/en-us/library/system.environment.username.aspx).
When it runs online it probably can't execute or it returns the
ASP.NET worker process's user or something similar and not useful. I
know for ASP.NET you can call HttpContext.User.Idenity.Name (or
something similar, I'm working from memory mostly) to see who the
logged in user is, this might work better in report services. Or there
might be a separate way to get the current user from the report manager
that I'm not aware of.|||Use the global variable User!UserID.value
You can get to this with the expression builder. It returns domain\username,
if you don't want the domain then you will need to strip it off.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Terence Tirella" <ttirella@.literate.com> wrote in message
news:1141851303.988200.237700@.u72g2000cwu.googlegroups.com...
> I'd guess that the reason this doesn't work is because when you run it
> in VS.net the application is running as you or some other acceptable
> local user
> (http://msdn2.microsoft.com/en-us/library/system.environment.username.aspx).
> When it runs online it probably can't execute or it returns the
> ASP.NET worker process's user or something similar and not useful. I
> know for ASP.NET you can call HttpContext.User.Idenity.Name (or
> something similar, I'm working from memory mostly) to see who the
> logged in user is, this might work better in report services. Or there
> might be a separate way to get the current user from the report manager
> that I'm not aware of.
>sql

detect existence of datetime fields

I'm looking for an efficient t-sql script to loop through all user
tables in a db and determine/print the value of each row having a
datetime field (including cases where there are multiple datetime
fields per row)

Help greatly appreciated.."JTWhaler" <jonahturnedwhaler@.yahoo.com> wrote in message
news:ed564348.0401022017.34730d8@.posting.google.co m...
> I'm looking for an efficient t-sql script to loop through all user
> tables in a db and determine/print the value of each row having a
> datetime field (including cases where there are multiple datetime
> fields per row)
> Help greatly appreciated..

Here is a script that does a reasonable task of generating
a useful data dictionary out of any database.

To identify the datetime fields simply add
a restriction line in the WHERE bit ... such as
and t.name in ('DateTime', 'SmallDateTime')

select substring(o.name,1,50) as "Table Name",
o.type "Typ",
c.colid,
substring(c.name,1,30) as "Column Name",
substring(t.name,1,30) as "DataType",
c.length

from sysobjects o
left join syscolumns c on (o.id=c.id)
left join systypes t on (c.xusertype=t.xusertype)
where o.type = 'U'
order by 1,3|||jonahturnedwhaler@.yahoo.com (JTWhaler) wrote in message news:<ed564348.0401022017.34730d8@.posting.google.com>...
> I'm looking for an efficient t-sql script to loop through all user
> tables in a db and determine/print the value of each row having a
> datetime field (including cases where there are multiple datetime
> fields per row)
> Help greatly appreciated..

Hi Jonah

The attached T-sql will create a database table that contains an entry
for each datetime column in the database

drop table datetime_column

create table datetime_column
(
table_name varchar(200)
,column_name varchar(200)
,datetime_value datetime null
)
declare @.table_schema as varchar(200)
declare @.table_name as varchar(200)
declare @.column_name as varchar(200)
declare @.query as nvarchar(1000)

DECLARE column_cursor CURSOR FOR
SELECT
table_name, column_name
FROM
INFORMATION_Schema.columns
where
data_type = 'datetime'
and table_name != 'datetime_column'

open column_cursor

FETCH NEXT FROM
column_cursor
INTO
@.table_name, @.column_name

while @.@.fetch_status = 0
begin
select @.query = ' insert into datetime_column select ' + '''' +
@.table_name + '''' + ',' + '''' + @.column_name + '''' + ',' +
@.column_name + ' from ' + @.table_name
--select @.query
exec sp_executesql @.query

FETCH NEXT FROM
column_cursor
INTO
@.table_name, @.column_name
end

close column_cursor
deallocate column_cursor
select * from datetime_column

DESPERATE help needed....

Hello all,

I am a total newbie to SQL. I created this sp and then with C++,
called it. My return value was 100 (which it was)

CREATE PROCEDURE sp_StoreIPs
@.IPSource varchar(16),
@.IPTarget varchar(16),
@.TimeDate varchar(20),
@.Name varchar(250)
as
declare @.iReturn int
Set @.iReturn = 100
return @.iReturn
GO

Then when I added a INSERT statement like ...

CREATE PROCEDURE sp_StoreIPs
@.IPSource varchar(16),
@.IPTarget varchar(16),
@.TimeDate varchar(20),
@.Name varchar(250)
as
declare @.iReturn int
Insert into LookUP (IPSource, IPTarget,TimeDate, Name) Values
(@.IPSource,@.IPTarget,@.TimeDate,@.Name)
Set @.iReturn = 100
return @.iReturn
GO

My return value was 0. I am assuming the the INSERT statement is
returning the 0 but how can I get around this?

Thanks
Ralph Krausse
www.consiliumsoft.com
Use the START button? Then you need CSFastRunII...
A new kind of application launcher integrated in the taskbar!
ScreenShot - http://www.consiliumsoft.com/ScreenShot.jpg"Ralph Krausse" wrote:

<snip
> CREATE PROCEDURE sp_StoreIPs
> @.IPSource varchar(16),
> @.IPTarget varchar(16),
> @.TimeDate varchar(20),
> @.Name varchar(250)
> as
> declare @.iReturn int
> Insert into LookUP (IPSource, IPTarget,TimeDate, Name) Values
> (@.IPSource,@.IPTarget,@.TimeDate,@.Name)
> Set @.iReturn = 100
> return @.iReturn
> GO
>
> My return value was 0. I am assuming the the INSERT statement is
> returning the 0 but how can I get around this?

<snip
Ralph,

I think you're half correct: an insert can return a message about records
affected... but I don't know why that would affect the return value unless
the insert failed: are you checking to insure successful execution? My
guess would be that the INSERT is failing.

In any event, to suppress the record count coming back as a message, you can
use SET NOCOUNT ON/OFF:

CREATE PROCEDURE blah
AS

SET NOCOUNT ON

INSERT SomeTable (fld) VALUES ('a')

SET NOCOUNT OFF

RETURN 100
GO

And the NOCOUNT setting can be handy even if this isn't your problem: it's
always nice to eliminate unnecessary network chitchat :)

Craig|||[posted and mailed, please reply in news]

Ralph Krausse (gordingin@.consiliumsoft.com) writes:
> I am a total newbie to SQL. I created this sp and then with C++,
> called it. My return value was 100 (which it was)

And what API did you use to call the procedure from C++?

> CREATE PROCEDURE sp_StoreIPs

Don't use the sp_ prefix for the names of stored procedure. This prefix
is reserved for system procedures, and SQL Server first looks for
procedures with this prefix in the master database.

> Then when I added a INSERT statement like ...
>...
> My return value was 0. I am assuming the the INSERT statement is
> returning the 0 but how can I get around this?

As Craig said, the INSERT statement generates a kind of result set to
inform of the number of rows consumed. If you don't get that result
set, you will not get the return value, nor the value of output parameter,
since these are not availble until all result sets have been consumed.

Since I don't know which API you are using, I cannot say which methods you
should use, but you should always make it a habit to fetch all result set.

SET NOCOUNT ON, which Craig suggested, is a very good idea, if you are
not interested in these record counts, since they cause extra round-trips
to the server. However, result sets may appear of other reasons, for
instance a trigger with a SELECT statement in it. (Which is poor practice,
but accidents happens.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Designing Crystal Report with Brought forward value

Please i am new to crystal report design even though I have been able to design some simple reports. I would appreciate your kind assistance.

I have a crystal report from VB.Net that has series of columns (fields) generated from the following two tables.

1. tblAgt - Table
fconsole (float field)
fagent
B1

2. tblDebit - Table
fmending (Date field)
ActNo
fagent
ftrans
fgrssprem (float field)
Comm
The report has the following format

Date: 01/01/2004 To 31/12/2004
Account No: 38000 To 38092

Balance bf: 00.00

fmending ActNo Fagent ftr fgrssprem Comm. Net Amt
02/02/04 38837 db001 DR 5,000.00 350.00 4,650
02/07/04 38838 db002 CR 15,000.30 200.00 15,200.30
03/02/02 29388 db003 pyt 2,000.00 100.00 1,900.00
--------------------------

The Date and the Accounts Group header were generated with the parameter fields (Range)

The Date Range (StartDate & EndDate) header
The Account Range (StartAcct & EndAcct) header

The DB or CR or Pyt entry determines whether the fgrssprm is negative or positive e.g. To get the Netamt

if ftrans = CR, then Comm is added
if ftrans = DR, then Comm is deducted

The Balance bf: is where I am having a problem with because it is suppose
to be generated from all fgrssprem that falls before StartDate eg. 1/01/2004
ie. fmending(tablefield) < startdate (parameter field)

Balance bf = sum(fgrssprem(tbldebit) is added to fconsole in table (tblagt) ie. Balance bf = fgrssprem + fconsole

I am trying to filter for all the dates that is less than startdate and to get the sum of fgrssprem is giving me 0.00 as my result.

I would be greatful if you could put me through.

Thanks

fieYou need to write a query in the Back end and design the report using that query which you should store it as Stored Procedure. What is the Database you are using?