Need Help ASAP! FoxPro to MSSQL 2000 Conversion question..

I am in the process of converting an old FoxPro 2.6 based DB to MSSQL 2000..

It appears that FoxPro 2.6 stores it's null value dates as 12:00:00 AM which is causing errors when importing the data into MSSQL 2000.

The format of all of the other dates is mm/dd/yyyy

Has anyone else encountered this? is there a filter, or a a script or switch somewhere to replace the NULL values with something that MSSQL is happier with?

..:: EDIT ::..

Looking @. the properties of the affected columns, it looks like they are set as smalldatetime, but if i switch them to datetime with a length of 8 it imports just fine.

So.. now my task is to update all of the columns from smalldatetime to datetime.

sugggestions?

Thanks in advance!

Joe..::bump::..

anyone?|||Use DTS.sql

Need good idea

Hi guys

We have a following problem. For security reasons in each table in our
DB we have addition field which is calculated as hash value of all
columns in particular row.

Every time when some field in particular row is changed we create and
call select query from our application to obtain all fields for this
row and then re-calculate and update the hash value again.

Obviously such approach is very ineffective, the alternative is to
create trigger on update event and then execute stored procedure which
will re-calculate and update the hash value. The problem with this
approach is that end user could then change the date in the tables and
then run this store procedure to adjust hash value.

We are looking for some solution that could speed up the hash value
updating without allowing authorized user to do it

Thanks in advance,
LeonVlad Olevsky wrote:
> Hi guys
> We have a following problem. For security reasons in each table in our
> DB we have addition field which is calculated as hash value of all
> columns in particular row.
> Every time when some field in particular row is changed we create and
> call select query from our application to obtain all fields for this
> row and then re-calculate and update the hash value again.
> Obviously such approach is very ineffective, the alternative is to
> create trigger on update event and then execute stored procedure which
> will re-calculate and update the hash value. The problem with this
> approach is that end user could then change the date in the tables and
> then run this store procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing authorized user to do it
> Thanks in advance,
> Leon
In DB2 for LUW you can define the column as a generated column.
I presume you have some sort of UDF already that does the actually
hashing. Last I heard SS 2005 will have persistent generated columns as
well.
In general (x-product) you can use a combination of a check constraint
and (before) triggers.

One must but wonder WHY this column is required. Are you affraid of
corruption or sabotage?

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab|||"Vlad Olevsky" <leonid4142@.yahoo.com> schrieb im Newsbeitrag news:50540181.0505030658.64f68390@.posting.google.c om...
> Obviously such approach is very ineffective, the alternative is to
> create trigger on update event and then execute stored procedure which
> will re-calculate and update the hash value. The problem with this
> approach is that end user could then change the date in the tables and
> then run this store procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing authorized user to do it
As Frank pointed out, try to create a trigger which calls a function.
Let the function run with the grants of the caller and give only
authorized callers the exec grant of the function.

Greetings!
Volker|||Vlad Olevsky wrote:

> Hi guys
> We have a following problem. For security reasons in each table in our
> DB we have addition field which is calculated as hash value of all
> columns in particular row.
> Every time when some field in particular row is changed we create and
> call select query from our application to obtain all fields for this
> row and then re-calculate and update the hash value again.
> Obviously such approach is very ineffective, the alternative is to
> create trigger on update event and then execute stored procedure which
> will re-calculate and update the hash value. The problem with this
> approach is that end user could then change the date in the tables and
> then run this store procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing authorized user to do it
> Thanks in advance,
> Leon

This may come as a shock to you Leon but the solution in each of the
products whose usenet group you copied on this uses a completely
different solution.

I'd suggest you start by apologizing, to all, for your lack of
identifying the product and version and for posting to every usenet
group you can spell.

And then repost in the one, and only, group where your query is
appropriate.

Thank you.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)|||Serge Rielau (srielau@.ca.ibm.com) writes:
> In DB2 for LUW you can define the column as a generated column.
> I presume you have some sort of UDF already that does the actually
> hashing. Last I heard SS 2005 will have persistent generated columns as
> well.

Actually, SQL 2000 has it as well. The difference is that PERSISTED is
a keyword in SQL 2005, and, I assume, that in SQL 2005 you can persist
a computed colum, without indexing it.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Serge Rielau (srielau@.ca.ibm.com) writes:
>>In DB2 for LUW you can define the column as a generated column.
>>I presume you have some sort of UDF already that does the actually
>>hashing. Last I heard SS 2005 will have persistent generated columns as
>>well.
>
> Actually, SQL 2000 has it as well. The difference is that PERSISTED is
> a keyword in SQL 2005, and, I assume, that in SQL 2005 you can persist
> a computed colum, without indexing it.
Yes, in SS2000 the generated column is virtual (i.e. not persisted). The
planned syntax in the standard is "GENERATED BY REFERENCE", being the
default for compatibility with SS2000.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab|||Serge Rielau (srielau@.ca.ibm.com) writes:
> Yes, in SS2000 the generated column is virtual (i.e. not persisted).

Unless, as I said, it is indexed, in which case it is implicitly persisted.

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

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

Need correction...

I want to display a list of member after filtering two condition...

1) Select MID from TableMember where AID value EQUAL to Dropdownlist.value
2) Select MID from TableStatus where AID NOT EQUAL to Label.Text

I try something like this... is not working... Can anyone correct the statement for me...

"Select * from VIEW1 where MID in (Select MID from TableMember where Aid='" & Ddl1.SelectedItem.Value & "') and MID not in(select Mid from TableStatus where Aid <>'" & Label.Text & "')"

Thank you...do TableMember and TableStatus have the same MID column ?

if so, try

select tablemember.* from tablemember,tablestatus
where tablemember.mid=tablestatus.mid and
tabmemember.aid=@.dpval and tablestatus.aid <> @.label

and pass the parameters accordingly.

hth|||Yes, TableMember and TableStatus have the same MID column

But one more thing is I am using a VIEW from SQL Server.

Which I think you have left out ?|||treat it as a table and add another join stmt to join the view with the other tables...

hth

Need call a DB function in the middle of the dataflow process

All,

I have to use a field that is calculated in a data flow process and call a database function (return a value) to do anther calculation; then return a value back to the data flow.I tried OLD DB Command but I cannot configure to return a value back to the same data flow.

If there any transformations that can call a DB function and get a value from the function in the middle of the data flow process?Need more detailed instruction.

The data flow is Like:

SourceDB à New_filed 1 = field1 + filed2 à New_filed 2= DB_function (New_filed 1) à Destination DB

Thanks in Advance

Jessie

HI, to return a value from a DBfunction, you need to add a derived column into the pipeline and map the OLEDB command return value to this newly added derived column. In the OLEDB command text you insert the following command:

EXEC ? = DB_Function(?)

Then on the mapping tab, you map the return value (first ?) to the derived column and the parameter (second ?) to your New_Field 1 parameter. This way, the new derived column gets the parameter from the DB_function.

HTH,
Ccote

|||

Hi, Ccote,

Thanks so much for the reply. I have followed your instruction but still getting errors. Here is the detail.

before the OLE command, I added a derived column, make a new column(NEW_Col) there with the same data type as the DB_function return value (int), set the default value to 0.

In the OLE command,I put exec ?=[dbo].[F_DBfunction] (?,?,?) in the SQLcommand field, mapping the 3 input column with the parameters, map the first ? with the NEW_Col from the derived column.

The OLE command does not let me to add any new column to the OLE command output.

I got error when I click on REFRESH, “Invalid parameter number ‘

Where I’m missing here?

Thanks

Jessie

|||

I got it, after change exec ?= [dbo].[F_DBfunction] (?,?,?) to exec ?= [dbo].[F_DBfunction] ?,?,?

Thanks

Need an expression format to put decimals .00

I have the following field(Fields!SequenceNO.Value), coming form database.

i would like to have an expression IIF...

if the value coming from database is just 1, then make it 1.00 or

if the value coming from database has a period or point say 1.01 then show as it is.

thank you very much for the help / information....

Hello,

You can do this a couple different ways...

1. Select your textbox that will hold Fields!SequenceNO.Value, then change the Format property to #.00

or

2. Replace Fields!SequenceNO.Value with the expression: =Format(Fields!SequenceNO.Value, "#.00")

Both of these will make all of your numbers in the column have 2 decimals.

Hope this helps.

Jarret

Need advise on Insert trigger

Hi all,

I am trying to create a insert trigger that gets a value from the new record and use that to get additional data from other tables and update the new record

this is howfar i came:

create trigger trUpdateGEOData
on BK_Machine
after insert
as
update BK_Machine
set BK_Machine.LOC_Street = GEO_Postcode.STraatID, BK_Machine.Loc_City = GEO_Postcode.PlaatsID
from BK_Machine join GEO_Postcode on BK_Machine.loc_postalcode = GEO_Postcode.postcode, inserted
where BK_Machine.MachineID = Inserted.MachineID and BK_Machine.Loc_Postalcode = GEO_Postcode.postcode and BK_Machine.LOC_Doornumber <= GEO_Postcode.van and
BK_Machine.LOC_Doornumber <= GEO_Postcode.tem

Trigger runs fine but doesn't do a thing probably it can't find the machineid i think,

can someone help me?

Cheers WimmoSo sorry for posting such a stupid mistake from my hazy view today!!

< should be >

Trying to get awake today, sorry for the disturbance

Cheers Wimmo|||Cleaned up your code:create trigger trUpdateGEOData
on BK_Machine
after insert
as
update BK_Machine
set BK_Machine.LOC_Street = GEO_Postcode.STraatID,
BK_Machine.Loc_City = GEO_Postcode.PlaatsID
from BK_Machine
inner join GEO_Postcode on BK_Machine.loc_postalcode = GEO_Postcode.postcode
and BK_Machine.LOC_Doornumber <= GEO_Postcode.van
and BK_Machine.LOC_Doornumber <= GEO_Postcode.tem
inner join inserted on BK_Machine.MachineID = Inserted.MachineID
Now, it strikes me that the inner join on GEO_Postcode could return no records, in which case no BK_Machine records would be updated. It could also potentially return more than one record, in which case you would get unpredictable results for your update.|||Thanx Blindman,

What do you mean with the inner join: is it when there are more records inserted at the same time with the same machineid?

Wimmo

Need a query...

Hello,
I have a table T1:
Date | Value
--
Jan A
Feb A
Feb B
Mar A
Mar B
Apr A
May A
I need a query to get dataset like this for ANY VALUE with maximum
daterange:
Date | Value
--
Jan A
Feb A
Mar A
Apr A
May A
Thanks,
GBSomething like:
SELECT col1, MAX ( col2 )
FROM tbl
GROUP BY col1 ;
Anith|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. if you had follwoed basic netiquette, would you have
posted this?
CREATE TABLE Foobar
(month_name CHAR(3) NOT NULL,
foo_value CHAR(1) NOT NULL,
PRIMARY KEY (month_name, foo_value));
date range: <<
I am going to guess you mean a foo_value that appears in all the
months.
SELECT foo_value
FROM Foobar AS F1
GROUP BY foo_value
HAVING COUNT(*)
= (SELECT COUNT(DISTINCT month_name) FROM Foobar);
This depends my guess at the DDL you never posted.

Need a good idea

Hi guys
We have a following problem. For security reasons in each table in our
DB we have addition field which is calculated as hash value of all
columns in particular row.
Every time when some field in particular row is changed we create and
call select query from our application to obtain all fields for this
row and then re-calculate and update the hash value again.
Obviously such approach is very ineffective, the alternative is to
create trigger on updating event and then execute stored procedure
which will re-calculate and update the hash value. The problem with
this approach is that end user could then change the date in the
tables and then run this stored procedure to adjust hash value.
We are looking for some solution that could speed up the hash value
updating without allowing unauthorized user to do itHave you checked out CHECKSUM() in BOL?
"Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
news:50540181.0505030708.b3397b6@.posting.google.com...
> Hi guys
> We have a following problem. For security reasons in each table in our
> DB we have addition field which is calculated as hash value of all
> columns in particular row.
> Every time when some field in particular row is changed we create and
> call select query from our application to obtain all fields for this
> row and then re-calculate and update the hash value again.
> Obviously such approach is very ineffective, the alternative is to
> create trigger on updating event and then execute stored procedure
> which will re-calculate and update the hash value. The problem with
> this approach is that end user could then change the date in the
> tables and then run this stored procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing unauthorized user to do it|||If you want to enforce that values can only be changed through your
application, the best way to do that is make sure that only your application
has permissions to use certain stored procedures and tables. For this you
can use application roles.
Having a trigger on the table to calculate the hash value won't do anything
useful, because anyone who updates the table, will fire that trigger and the
hash value will updated correctly.
Jacco Schalkwijk
SQL Server MVP
"Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
news:50540181.0505030708.b3397b6@.posting.google.com...
> Hi guys
> We have a following problem. For security reasons in each table in our
> DB we have addition field which is calculated as hash value of all
> columns in particular row.
> Every time when some field in particular row is changed we create and
> call select query from our application to obtain all fields for this
> row and then re-calculate and update the hash value again.
> Obviously such approach is very ineffective, the alternative is to
> create trigger on updating event and then execute stored procedure
> which will re-calculate and update the hash value. The problem with
> this approach is that end user could then change the date in the
> tables and then run this stored procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing unauthorized user to do it|||How are you using this hash value? Is it supposed to provide some
user-authentication? Without understanding the application of this it's
difficult to recommend an alternative.
David Portas
SQL Server MVP
--|||If the Checksum() function is not sufficient, you might try a computed colum
n.
Thomas
"Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
news:50540181.0505030708.b3397b6@.posting.google.com...
> Hi guys
> We have a following problem. For security reasons in each table in our
> DB we have addition field which is calculated as hash value of all
> columns in particular row.
> Every time when some field in particular row is changed we create and
> call select query from our application to obtain all fields for this
> row and then re-calculate and update the hash value again.
> Obviously such approach is very ineffective, the alternative is to
> create trigger on updating event and then execute stored procedure
> which will re-calculate and update the hash value. The problem with
> this approach is that end user could then change the date in the
> tables and then run this stored procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing unauthorized user to do it|||Jacco raised a very good point. If your intent is to store a hash value of
some sort to indicate that a row has not been tampered with by any means
outside of your application, you should probably generate the hash code and
insert it from the application side, not via a trigger or other mechanism
internal to the database that users would have access to via QA.
"Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
news:50540181.0505030708.b3397b6@.posting.google.com...
> Hi guys
> We have a following problem. For security reasons in each table in our
> DB we have addition field which is calculated as hash value of all
> columns in particular row.
> Every time when some field in particular row is changed we create and
> call select query from our application to obtain all fields for this
> row and then re-calculate and update the hash value again.
> Obviously such approach is very ineffective, the alternative is to
> create trigger on updating event and then execute stored procedure
> which will re-calculate and update the hash value. The problem with
> this approach is that end user could then change the date in the
> tables and then run this stored procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing unauthorized user to do it|||you could continue to use a trigger, but the trigger only does something if
called by your application, so if someone changed a row via query analyser
the trigger will not fire.. see below
create trigger mytrigger on mytable after update
as
begin
if app_name() = 'myapp'
begin
' do stuff here
end
end
go
"Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
news:50540181.0505030708.b3397b6@.posting.google.com...
> Hi guys
> We have a following problem. For security reasons in each table in our
> DB we have addition field which is calculated as hash value of all
> columns in particular row.
> Every time when some field in particular row is changed we create and
> call select query from our application to obtain all fields for this
> row and then re-calculate and update the hash value again.
> Obviously such approach is very ineffective, the alternative is to
> create trigger on updating event and then execute stored procedure
> which will re-calculate and update the hash value. The problem with
> this approach is that end user could then change the date in the
> tables and then run this stored procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing unauthorized user to do it|||Thanks Mark!
The only one remark. To prevent end-user from looking and modifying the
trigger code we could create trigger using 'WITH ENCRYPTION' flag.
This flag encrypts the syscomments entries that contain the text of
CREATE TRIGGER. Using WITH ENCRYPTION prevents the trigger from being
published as part of SQL Server replication. So tamper will never know
what we check within trigger.
Mark wrote:
> you could continue to use a trigger, but the trigger only does
something if
> called by your application, so if someone changed a row via query
analyser
> the trigger will not fire.. see below
> create trigger mytrigger on mytable after update
> as
> begin
> if app_name() = 'myapp'
> begin
> ' do stuff here
> end
> end
> go
> "Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
> news:50540181.0505030708.b3397b6@.posting.google.com...
our
and
this

Navigation in a Graph

In SQL Reporting Services, i am having a problem with
passing the label value(eg. on x-axis) as a parameter to another report.
So for example, lets say i have the days of the week as my x-axis values in
the graph. Is there any way to allow the user to click on this label
value(eg. monday) which will then pass this as a parameter to another
report?
ThanksNot from labels themselves, but you can do this with data points. Pull up
Chart Properties dialog in report designer -> Values -> Edit -> Action tab.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kershni Chetty" <support@.sqrsoftware.com> wrote in message
news:O1FqKospEHA.1988@.TK2MSFTNGP09.phx.gbl...
> In SQL Reporting Services, i am having a problem with
> passing the label value(eg. on x-axis) as a parameter to another report.
> So for example, lets say i have the days of the week as my x-axis values
in
> the graph. Is there any way to allow the user to click on this label
> value(eg. monday) which will then pass this as a parameter to another
> report?
> Thanks
>