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 help

CREATE TABLE [dbo].[TP_Large_Void_Rawdata_794981854_2007319_10613_7944] ([Rec_Date_Tm] [datetime] NULL, [QCluster_Count_1_SA2] [int] NULL , [QCluster_Count_2_SA2] [int] NULL , [QCluster_Count_3_SA2] [int] NULL , [QCluster_Count_4_SA2] [int] NULL , [QCluster_Count_5_SA2] [int] NULL , [QCluster_Count_1_SB2] [int] NULL , [QCluster_Count_2_SB2] [int] NULL , [QCluster_Count_3_SB2] [int] NULL , [QCluster_Count_4_SB2] [int] NULL , [QCluster_Count_5_SB2] [int] NULL) ON [PRIMARY]

GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[TP_Large_Void_Rawdata_794981854_2007319_10613_7944] TO [irisaoidb]

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) CREATE TABLE [dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944] ([lm0805] [varchar] (50) NULL, [rsc725] [varchar] (50) NULL) ON [PRIMARY]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944] TO [irisaoidb]

INSERT INTO TP_Large_Void_Rawdata_794981854_2007319_10613_7944 SELECT Rec_Date_Tm, QCluster_Count_1_SA2, QCluster_Count_2_SA2, QCluster_Count_3_SA2, QCluster_Count_4_SA2, QCluster_Count_5_SA2, QCluster_Count_1_SB2, QCluster_Count_2_SB2, QCluster_Count_3_SB2, QCluster_Count_4_SB2, QCluster_Count_5_SB2 FROM tbl_IRISAOI_Rawdata_KM1 (nolock),TP_DefaultCharts_Store_LotNo__794981854_2007319_10613_7944 (nolock),TP_DefaultCharts_Store_Product__794981854_2007319_10613_7944 (nolock),TP_DefaultCharts_Store_Testcell__794981854_2007319_10613_7944 (nolock) WHERE Rec_Date_Tm >= '2007-03-19 00:00:00' AND Rec_Date_Tm <= '2007-03-19 20:00:00' AND Testcell = TP_DefaultCharts_Store_Testcell__794981854_2007319_10613_7944.Selected_Value AND Product = TP_DefaultCharts_Store_Product__794981854_2007319_10613_7944.Selected_Value AND Lot_No = TP_DefaultCharts_Store_LotNo__794981854_2007319_10613_7944.Selected_Value and tester in (ALL) and upper(Lot_Id_Code) in (ALL) and upper(Bin_No) in (ALL) and upper(Grade_No) in (ALL)

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'ALL'.

can anyone point out what mistake that i've made here?

The ALL is a parameter that passed to the stored procedure when invoked.

|||

oops, in fact i've left out the CHAR(39) on both side of the parameter...

now the problem is solved.

sql

Need better workaround for hidden prompt without read-only behavior

I have many reports that have a parameter in them which I want to default to NULL and not show (plus other parameters I do want to show). If I leave the prompt field empty, I get the read-only error. The workaround has been to have the prompt field contain a single space, which works well as far as producing the behavior I want. However, every time you open the report in the designer, it automatically removes the space. What is a better solution?

When you clear the prompt string in Report Manager, make sure you do not check the prompt user flag.

The parameter values in the server override the designer parameter values. So even though the prompt string is back, it will still be hidden on the server.

In SQL 2005, we have made this behavor more clear.

|||I am not clearing the prompt string in Report Manager, I am doing so in the designer. These are reports that get installed at customer production sites by basically importing the rdl through a utility built into our software. I do not have access to maintaining the reports through report manager. Any suggestions?|||You would need to write in your iinstallation script that published the report to clear out the prompt string. The call is SetReportParameters.

Again, this is an RDL property in SQL 2005.sql

need an alternative for this simple query

I need to do something like this. how would i do this?
select * from t1
where c1=@.c1
and c2=isnull(@.c2,null)
but i know this wont work because if @.c2 is null it'll try to compare
'c2=null' which should never be true. so how would you do this? thank you.How about:
select * from t1
where c1=@.c1
and COALESCE(c2, '')=iCOALESCE(@.c2,'')
Stu|||wut needs to happen is this...
--when @.c2 is not null
select * from t1
where c1=@.c1
and c2=@.c2
--when @.c2 is null
select * from t1
where c1=@.c1
and c2 is null
How would i do this? I dont think coalesce does it...
sometimes i want to get a value where it's null.|||So in the first case: @.c2 is not null
THe optimizer runs the following query
select * from t1
where c1=@.c1
AND c2 = @.c2 -- if c2 is null, then compare an empty string to @.c2,
which won't match
In the second case, if @.c2 is NULL, then turn it inot an empty string.
You only want columns that are null in the database, and those are also
returned as an empty string. Emptry string=empty string,|||I dont have a problem writing 2 different queries. I need to know if I can
comine the 2 options in one query... thanks. I already have it working with
2
queries...|||try this..
select * from t1
where c1=@.c1
and isnull(c2,0) = isnull(@.c2,0)|||I'm sorry but this wont work also. I need to see if @.c2 is null or not.
if it is not null. I need to 'select * from t1 where c1=@.c1 and c2 = @.c2'
if it's null, I need to 'select * from t1 where c1=@.c1 and c2 is null'
there are null entries in the db and i can't change it. that's how it was
setup... i need to compare @.c2 to a varchar sometime and to NULL sometime...|||I'm sorry, I was trying to explain why the first query I submitted will
work, and obviuosly there is a misunderstanding. Test the query I
submitted the first time:
select * from t1
where c1=@.c1
and COALESCE(c2, '')=COALESCE(@.c2,'')
It will work; I do this often.
Omnibuzz's suggestion is essentially the same as mine, but the
performance will work best depending on the datatype of the column
involved; if a varchar, compare an empty string. If an integer,
compare 0 (assuming that 0 is not a valid value in your colum).|||try this then
select * from t1 where c1=@.c1 and
((c2 is null and @.c2 is null) or (c2 = @.c2))
"tpp" wrote:
> I'm sorry but this wont work also. I need to see if @.c2 is null or not.
> if it is not null. I need to 'select * from t1 where c1=@.c1 and c2 = @.c2'
> if it's null, I need to 'select * from t1 where c1=@.c1 and c2 is null'
> there are null entries in the db and i can't change it. that's how it was
> setup... i need to compare @.c2 to a varchar sometime and to NULL sometime...[/colo
r]|||Hi
create table #t (c1 int ,c2 int,c3 char(1))
insert into #t values (1,10,'a')
insert into #t values (1,null,'b')
select * from #t
declare @.c1 int,@.c2 int
set @.c1=1
set @.c2 =10
if @.c2 is null
select * from #t where c1=@.c1 and c2 is null
else
select * from #t where c1=@.c1 and c2=@.c2
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:F2516C09-2430-48F6-8FBD-2716B77AA640@.microsoft.com...
>I need to do something like this. how would i do this?
> select * from t1
> where c1=@.c1
> and c2=isnull(@.c2,null)
> but i know this wont work because if @.c2 is null it'll try to compare
> 'c2=null' which should never be true. so how would you do this? thank you.

Need alittle help with sql statemtn stored procedure

Here is what I am trying to do, If i type to query the stored procedure with two parameters and the two parameters are null, return all rows, else do the query based on the two parameters. Thanks in advance.

Create PROCEDURE [dbo].[Admin_RetrieveCustomerOrders] @.Yearnumint,@.monthnumintASBEGINSET NOCOUNT ON;SELECT o.intOrderId, o.vcCustomerID, o.decTotal, o.dtOrdered,u.vcFirstName, u.vcLastName,(SELECTCOUNT(d.intOrderID)FROM tblOrderDetails dWHERE d.intOrderID = o.intOrderID)AS TotalProductsFROM tblOrders oINNERJOIN tblUserInformation uON o.vcCustomerID = u.vcCustomerIDWHERE YEAR(o.dtOrdered)=@.yearnumANDMONTH(o.dtOrdered)=@.monthnumORDER BY o.dtOrderedDESC END

Thanks Again

Joshua

I think this may work

Essentially you need to modify you WHERE clause to also allow null for the parameters

WHERE (YEAR(o.dtOrdered)=@.yearnumOR @.yearnumisNULL)AND (MONTH(o.dtOrdered)=@.monthnumOR @.monthnumisNULL)

Give the above ago and let us know if it works. 

Need a little help please

I have a simple table:
MyTable
id int null
user varchar(50) null
desc varchar(150) null
No PK or indexes of any kind.
Does the following cause any performance issue:
insert into MyTable (desc, user, id) values ('Bob from Account
Temps','Bob','777')
Does not having indexes or PK hinder the insert? And does the order of the
columns in the insert make a difference?
Thanks all
JDEvery table should have a primary key. Why do you have a table without
one?
> Does not having indexes or PK hinder the insert?
A lack of indexes and constraints doesn't affect the insert per se.
Potentially it affects the *validity* of the insert since you won't be
prevented from inserting redundant data.
> And does the order of the
> columns in the insert make a difference?
If all the column names are listed then logically the order makes no
difference.
--
David Portas
SQL Server MVP
--|||This is a 3rd party application. I'm trying to gather information what will
be presented at a meeting next week.
thanks for the feedback.
JD
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108586063.090431.233100@.l41g2000cwc.googlegroups.com...
> Every table should have a primary key. Why do you have a table without
> one?
> > Does not having indexes or PK hinder the insert?
> A lack of indexes and constraints doesn't affect the insert per se.
> Potentially it affects the *validity* of the insert since you won't be
> prevented from inserting redundant data.
> > And does the order of the
> > columns in the insert make a difference?
> If all the column names are listed then logically the order makes no
> difference.
> --
> David Portas
> SQL Server MVP
> --
>|||>> Does not having indexes or PK hinder the insert?
> A lack of indexes and constraints doesn't affect the insert per se.
It can, but the effect will probably only surface under more extreme conditions.
If there is a clustered index, SQL Server will immediately know where to put this row. If the cl ix
key is always increasining (which isn't necessarily the optimal cl ix key for *searches*), then you
won't get page splits either.
If there isn't a cl ix, then SQL Server need to go by IAMs and PFS pages (I believe, it is still
early in the morning for me) to determine where to put the rows. I imagine that this can be more
work, but I doubt we see the effect unless large databases and heavy load.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108586063.090431.233100@.l41g2000cwc.googlegroups.com...
> Every table should have a primary key. Why do you have a table without
> one?
>> Does not having indexes or PK hinder the insert?
> A lack of indexes and constraints doesn't affect the insert per se.
> Potentially it affects the *validity* of the insert since you won't be
> prevented from inserting redundant data.
>> And does the order of the
>> columns in the insert make a difference?
> If all the column names are listed then logically the order makes no
> difference.
> --
> David Portas
> SQL Server MVP
> --
>