new to sql - trying to convert vb function to udf

Hi

I'm an Access/VB coder by experience and trying to move apps to SQL Server 2000.
I have got to grips with the basics of DTS and tables and views and Stored Procedures (to an extent) but now need to upgrade an app that uses a vb function to produce a phased value for a set of budgets.

The VB function looks like this...

----------
Function calcPercentOfBudget(datFromDate As Date, datToDate As Date, iMonth As Integer, cBudget As Currency) As Currency

Dim iDuration As Integer
iDuration = DateDiff("d", datFromDate, datToDate) + 1

' if either date not in current year then 0 (both dates should be in same year)
If Year(datFromDate) <> Year(Now) Or Year(datToDate) <> Year(Now) Then
calcPercentOfBudget = 0
End If

Dim sRatio As Single, idaysInMonth As Integer, idaysInYear As Integer

' if passed month outside of period then 0
If Not (iMonth >= Month(datFromDate) And iMonth <= Month(datToDate)) Then
calcPercentOfBudget = 0
Exit Function
End If

idaysInMonth = daysInMonth(iMonth, Year(Now))


'if from date and to date in same month then 100% of budget
If Month(datFromDate) = Month(datToDate) Then
calcPercentOfBudget = cBudget
Exit Function
End If

' if passed month in From month then ratio of passed month (caters for 1st day of month - 100%)
If Month(datFromDate) = iMonth Then
'calcPercentOfBudget = (idaysInMonth + 1 - Day(datFromDate)) / idaysInYear * cBudget
calcPercentOfBudget = (idaysInMonth + 1 - Day(datFromDate)) / iDuration * cBudget
Exit Function
End If

'if passed month in To month then ratio of passed month (caters for last day of month - 100%)
If Month(datToDate) = iMonth Then
'calcPercentOfBudget = Day(datToDate) / idaysInYear * cBudget
calcPercentOfBudget = Day(datToDate) / iDuration * cBudget
Exit Function
End If

' if passed month within period then 100%
If iMonth > Month(datFromDate) And iMonth < Month(datToDate) Then
'calcPercentOfBudget = idaysInMonth / idaysInYear * cBudget
calcPercentOfBudget = idaysInMonth / iDuration * cBudget
Exit Function
End If

End Function
----------
Function daysInMonth(iMonth As Integer, iYear As Integer) As Integer

Dim datTemp As Date

datTemp = CDate("1/" & CStr(iMonth) & "/" & CStr(iYear))
datTemp = DateAdd("m", 1, datTemp)
datTemp = DateAdd("d", -1, datTemp)

daysInMonth = Day(datTemp)

End Function
----------

I have a UDF function LastDayInMonth to replace the daysInMonth vb function, and that works fine.

However I'm starting to get frustrated in trying to convert the main VB funciton to a UDF function. This is what I have got to, and as you'll see its totally wrong!...

CREATE FUNCTION [dbo].[calcPercentOfBudget] (@.datFrom as datetime, @.datTo as datetime , @.iMonth as int, @.cBudget as money, @.GetDate as datetime)
RETURNS money AS
BEGIN
declare @.iDuration int
declare @.returnvalue money
declare @.sRatio decimal
declare @.iDaysInMonth int

set @.iDuration = datediff(d, @.datFrom, @.datTo)
set @.iDaysinMonth = dbo.LastDayInMonth('1/' + cast(@.iMonth as varchar) + '/' + cast(year(@.getdate) as varchar))

case
when year(@.datFrom) <> year(@.getdate) or year(@.datTo) <> year(@.getdate)
set @.returnvalue = 0
when not(@.iMonth >= Month(@.datFrom) and @.iMonth <= month(@.datTo)
set @.returnvalue 0
when month(@.datFrom) = month(@.datTo)
@.set @.returnvalue = @.cBudget
when month(@.datFrom) = @.iMonth
set @.returnvalue = (@.iDaysInMonth + 1 - day(@.datFrom)) / @.iDuration * @.cBudget
when month(@.datTo) = @.iMonth
set @.returnvalue = Day(datToDate) / iDuration * cBudget
when @.iMonth > month(@.datFrom) and @.iMonth < month(@.datTo)
set @.returnvalue = @.iDaysInMonth / @.iDuration * @.cBudget
end

return @.returnvalue
END

This is my first post to this forum - so any constructive criticism will be welcomed.

Basically, where am I going wrong? - have I got the wrong end of the stick? Have I got the wrong stick? Have I got a stick of dynamite?!

All help greatfully received,
Paul:eek:Case statements work a little differently in T-SQL. Try this:

CREATE FUNCTION [dbo].[calcPercentOfBudget] (@.datFrom as datetime, @.datTo as datetime , @.iMonth as int, @.cBudget as money, @.GetDate as datetime)
RETURNS money AS
BEGIN
declare @.iDuration int
declare @.returnvalue money
declare @.sRatio decimal
declare @.iDaysInMonth int

set @.iDuration = datediff(d, @.datFrom, @.datTo)
set @.iDaysinMonth = dbo.LastDayInMonth('1/' + cast(@.iMonth as varchar) + '/' + cast(year(@.getdate) as varchar))

select @.returnvalue =
case when year(@.datFrom) <> year(@.getdate) or year(@.datTo) <> year(@.getdate) then 0
when not(@.iMonth >= Month(@.datFrom) and @.iMonth <= month(@.datTo) then 0
when month(@.datFrom) = month(@.datTo) then @.cBudget
when month(@.datFrom) = @.iMonth then (@.iDaysInMonth + 1 - day(@.datFrom)) / @.iDuration * @.cBudget
when month(@.datTo) = @.iMonth then Day(datToDate) / iDuration * cBudget
when @.iMonth > month(@.datFrom) and @.iMonth < month(@.datTo) then @.iDaysInMonth / @.iDuration * @.cBudget
end

return @.returnvalue
END

Case in T-SQL returns a value, rather than executes a block of code.|||fantastic, after a couple of other debugs from translation I have a result that works!

Many thanks :)

new to ms sql having trouble with very basic commands

I'm more used to mysql, which i haven't coded for a while, but how do you show tables? or show databases you have available from the osql prompt?hello?|||Use the Information Schema

-- Show Tables
SELECT * FROM INFORMATION_SCHEMA.TABLES

besides the TABLES view of the Schema, there are many others

Here is a list of the views provided by SQL Server 2000

CHECK_CONSTRAINTS
Holds information about constraints in the database

COLUMN_DOMAIN_USAGE
Identifies which columns in which tables are user-defined datatypes

COLUMN_PRIVILEGES
Has one row for each column level permission granted to or by the current user

COLUMNS
Lists one row for each column in each table or view in the database

CONSTRAINT_COLUMN_USAGE
Lists one row for each column that has a constraint defined on it

CONSTRAINT_TABLE_USAGE
Lists one row for each table that has a constraint defined on it

DOMAIN_CONSTRAINTS
Lists the user-defined datatypes that have rules bound to them

DOMAINS
Lists the user-defined datatypes

KEY_COLUMN_USAGE
Lists one row for each column that's defined as a key

PARAMETERS
Lists one row for each parameter in a stored procedure or user-defined function

REFERENTIAL_CONSTRAINTS
Lists one row for each foreign constraint

ROUTINES
Lists one row for each stored procedure or user-defined function

ROUTINE_COLUMNS
Contains one row for each column returned by any table-valued functions

SCHEMATA
Contains one row for each database

TABLE_CONSTRAINTS
Lists one row for each constraint defined in the current database

TABLE_PRIVILEGES
Has one row for each table level permission granted to or by the current user

TABLES
Lists one row for each table or view in the current database

VIEW_COLUMN_USAGE
Lists one row for each column in a view including the base table of the column where possible

VIEW_TABLE_USAGE
Lists one row for each table used in a view

VIEWS
Lists one row for each view

-------------
You probably need to get hold of the SQL books online and the SQL tools; that will help quite a bit.|||thanks, thats been most helpful,

I'm going to bookmark this page :)

which sql tools are you reffering to?

New Time Dimension Role after cube wizard

If I have a data source view consisting of two tables - a fact table and a time dimension table. The fact table has three date columns - transaction begin date, transaction end date, and updated date. When I initially designed the data source view my requirements only called for the trans begin and end dates so I made two joins to the time table from the sales table. I then ran the cube wizard which correctly detected that I intended to use the time table twice for two different Time hierarchies (Roles) on a single time dimension.

I have recently been asked to add the updated date but I cannot figure out how to add a new hierarchy (Role) on the time dimension. Any Ideas?

You should be able to go to the Dimension Usage tab in the cube designer and click on the Add Cube Dimension button on the toolbar (the third button from the left). When the list of dimensions comes up, simply select your Time dimension and add it again. You'll then see the dimension added within the list of dimensions down the left side of the tab, likely with a name like Time (Time 1) or something similar. Now, simply highlight the newly added role-playing dimension, hit F2, and rename it whatever you want. Then, of course, set the appropriate relationship between this new dimension and the measure group(s) within the cube...

HTH,

Dave Fackler

sql

new tables everyday

We have a software package that exports its data in MS Access 97 and 2000
formats. It overwrites and creates a new Access file each time it exports.
These exports serve a handful of front end applications. I'd like to get
this data tied to our SQL 2005 server. Any suggestions on whether to look at
just linking these in vs importing, etc?Hi,
Are you using the data imported just to query against it from an application
that connects to SQL Server? In that case you can indeed avoid the hassle
of importing by setting up linked servers (see
http://msdn2.microsoft.com/en-us/library/ms190479.aspx). This will allow
you to access your data over connections to the SQL Server using a four part
object name i.e. linkedservername.database.schema.table. You can also use
functions like OPENROWSET and OPENDATASOURCE, but if you access the external
data in various places in your code then I would suggest that you use linked
servers instead.
Jonathan
"Schmeg" <sschmeg@.yahoo.com> wrote in message
news:OE54Ig14HHA.1484@.TK2MSFTNGP06.phx.gbl...
> We have a software package that exports its data in MS Access 97 and 2000
> formats. It overwrites and creates a new Access file each time it exports.
> These exports serve a handful of front end applications. I'd like to get
> this data tied to our SQL 2005 server. Any suggestions on whether to look
> at just linking these in vs importing, etc?
>|||On Aug 20, 11:31 am, "Schmeg" <ssch...@.yahoo.com> wrote:
> We have a software package that exports its data in MS Access 97 and 2000
> formats. It overwrites and creates a new Access file each time it exports.
> These exports serve a handful of front end applications. I'd like to get
> this data tied to our SQL 2005 server. Any suggestions on whether to look at
> just linking these in vs importing, etc?
Why do you export the data to Access? Is it for off line use or just
because? I support multipule Access Projects that connect directly to
MS SQL server and they have thousands of lines of code for working
with the data. This works well and is good for rappid development.
On the other hand if you only want a couple of the tables in the SQL
DB available for the users to work in then link the tables in access.

New Tables and Stored procedures are saving as System Tables

When I try to create a new table/stored procedure, MSDE is saving it as
system table/procedure. And this is happening to all the databases on that
instance.
I checked the sysobjects table. The new tables are saved as Type 'U'.
But their status is in -ve nos.
What I can do to fix that?
Hi
You should not rely on using the "FOR MS USE" objects/columns. What does
OBJECTPROPERTY(id,'IsSystemTable') return?
John
"Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
news:45F7565B-FC35-437E-88CC-30E896875DF0@.microsoft.com...
> When I try to create a new table/stored procedure, MSDE is saving it as
> system table/procedure. And this is happening to all the databases on that
> instance.
> I checked the sysobjects table. The new tables are saved as Type 'U'.
> But their status is in -ve nos.
> What I can do to fix that?
>
>

New Tables and Stored procedures are saving as System Tables

When I try to create a new table/stored procedure, MSDE is saving it as
system table/procedure. And this is happening to all the databases on that
instance.
I checked the sysobjects table. The new tables are saved as Type 'U'.
But their status is in -ve nos.
What I can do to fix that?Hi
You should not rely on using the "FOR MS USE" objects/columns. What does
OBJECTPROPERTY(id,'IsSystemTable') return?
John
"Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
news:45F7565B-FC35-437E-88CC-30E896875DF0@.microsoft.com...
> When I try to create a new table/stored procedure, MSDE is saving it as
> system table/procedure. And this is happening to all the databases on that
> instance.
> I checked the sysobjects table. The new tables are saved as Type 'U'.
> But their status is in -ve nos.
> What I can do to fix that?
>
>

new table to ASPNetDb.mdf and foreign key

I was thinking of adding tables to ASPNetDB.mdf and have one of those tables have column userid as a foreign key from aspnet_Users

When I try to create relationship in Diagram, I get error saying that "data typ properties does not match"

userid in aspNet_Users is uniqueidentifier and userid (fk) in new table is int

What should I use, should I do that at all?

Thanks

uniqueidentifier and int is of different type. correct me if i'm wrong, the foreign key must be of same type as the primary key.|||

yes, but even when I use same type it gives me the same error

|||

Try to compare your new table with aspnet_Membership table, this table has an existing relationship with aspnet_Users table. Maybe you are missing a declaration of keys. As you can see both tables has UserID and they are tagged as a primary key and created a relationship based on that keys, but you can always have an option to make it an additional key instead of a primary.

Hope this helps.

|||That helps, good advice. I was able to connect keys from membership table with no problems. Thank you.

new stored procedure problem...

using tables with identity columns and trying to create a stored procedure to load records into 2 tables.
here is what i am trying to do. but when i try to save it i get
"Error 8101: An explicit value for the identity column in table 'XAPCHKDETAIL' can only be specified when a column list is used and IDENTITY_INSERT is ON"
Do i have to break this down into 2 SP's or can i do it some how with this one.
Also, how can i get the XAPCHECKS table to only load the DISTINCT values from the APPHISTF. the APPHISTF will possible have multiple check numbers that are the same but i only need that row in the XAPCHECKS table once.

CREATE PROCEDURE isp_ap_load_checks
@.p_comp char(2),
@.p_vend char(6),
@.p_user char(12),
@.p_date1 char(8),
@.p_date2 char(8)
as

if (@.p_user <= '')
begin
set @.p_user = 'system'
end

if (@.p_date1 <= '00000000')
begin
set @.p_date1 = '00000000'
end

if (@.p_date2 <= @.p_date1)
begin
set @.p_date2 = '99999999'
end

delete from XAPCHECKS
where xapck_comp = @.p_comp and xapck_vend = @.p_vend and xapck_user = @.p_user

delete from XAPCHKDETAIL
where xapcd_comp = @.p_comp and xapcd_vend = @.p_vend and xapcd_user = @.p_user

insert into XAPCHECKS
select apph_comp, apph_vend, @.p_user,
max(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0')), apph_payck, chm_type, chm_stat, apt_bank,
apph_paymnts, apph_stat

from APPHISTF LEFT JOIN APTRANF on apt_comp = apph_comp and apt_vend = apph_vend and
apt_type = apph_type and apt_id = apph_id
LEFT JOIN APBANKF ON apb_code = apt_bank
left join CHMASTF on chm_comp = apb_comp and chm_acct = apb_cash and chm_no = apph_payck
where (apph_comp = @.p_comp) and (apph_vend = @.p_vend) and
(apph_payck > 0 and
(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') > '00000000'))
group by apph_comp, apph_vend, apph_payck, chm_type, chm_stat, apph_paymnts, apph_stat, apph_type, apt_bank, apph_id

insert into XAPCHKDETAIL
select apph_comp, apph_vend, @.p_user, apph_payck, chm_type, chm_stat, apt_bank, apph_type, apph_id

from APPHISTF LEFT JOIN APTRANF on apt_comp = apph_comp and apt_vend = apph_vend and
apt_type = apph_type and apt_id = apph_id
LEFT JOIN APBANKF ON apb_code = apt_bank
left join CHMASTF on chm_comp = apb_comp and chm_acct = apb_cash and chm_no = apph_payck
where (apph_comp = @.p_comp) and (apph_vend = @.p_vend) and
(apph_payck > 0 and
(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') > '00000000'))
group by apph_comp, apph_vend, apph_payck, chm_type, chm_stat, apph_paymnts, apph_stat, apph_type, apt_bank, apph_id

GOPost the DDL for XAPCHKDETAIL.|||here is the sql script for the table...
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[XAPCHKDETAIL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[XAPCHKDETAIL]
GO

CREATE TABLE [dbo].[XAPCHKDETAIL] (
[xapcd_comp] [char] (2) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapcd_vend] [char] (6) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapcd_user] [char] (12) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapcd_check] [int] NOT NULL ,
[xapcd_chk_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapck_check_status] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapcd_bank] [char] (5) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapcd_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[xapcd_id] [char] (18) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[A4GLIdentity] [numeric](9, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [IXAPCHKDETAIL0] ON [dbo].[XAPCHKDETAIL]([xapcd_comp], [xapcd_vend], [xapcd_user], [xapcd_check], [xapcd_chk_type], [xapck_check_status], [xapcd_bank]) ON [PRIMARY]
GO|||Visit http://support.microsoft.com/kb/878501 , an article from Microsoft to resolve the problem.

Cheers,
--Riaz

using tables with identity columns and trying to create a stored procedure to load records into 2 tables.
here is what i am trying to do. but when i try to save it i get
"Error 8101: An explicit value for the identity column in table 'XAPCHKDETAIL' can only be specified when a column list is used and IDENTITY_INSERT is ON"
Do i have to break this down into 2 SP's or can i do it some how with this one.
Also, how can i get the XAPCHECKS table to only load the DISTINCT values from the APPHISTF. the APPHISTF will possible have multiple check numbers that are the same but i only need that row in the XAPCHECKS table once.|||Change this:
insert into XAPCHKDETAIL
select apph_comp, apph_vend, @.p_user, apph_payck, chm_type, chm_stat, apt_bank, apph_type, apph_id
...

...to this:
insert into XAPCHKDETAIL
([xapcd_comp],
[xapcd_vend],
[xapcd_user],
[xapcd_check],
[xapcd_chk_type],
[xapck_check_status],
[xapcd_bank],
[xapcd_type],
[xapcd_id])
select apph_comp, apph_vend, @.p_user, apph_payck, chm_type, chm_stat, apt_bank, apph_type, apph_id
...

ALWAYS enumerate your column names.|||Is there a way that you know of to only load up a distinct record into the XAPCHECKS table. The APPHISTF can have many ID's associated with a single check.
apphistf:
ID 1 check 1
ID 2 check 1
ID 3 check 1
ID 4 check 2
ID 5 check 3
ID 6 check 3
...
what i am trying to accomplish is to get a recap for a vend by check date, check number that doesnt have to tie back to the ID, that is where the XAPCHKDETAIL comes in.|||Either SELECT DISTINCT, or use an aggregate query with MAX or MIN to select one of the IDs to insert.

new records aren't being added to ft catalog (sql 2005 ee)

Hi all,
I have multiple full-text catalogs on about 8 tables (sql server 2005 ee -
all varchar(max)).
A few days ago, they all went to an idle status and won't start picking up
any new changes - all catalogs are auto update.
when i say 'idle' - mean :
objectpropertyex ( [table object_id] , 'TableFulltextPopulateStatus' ) = 0
objectpropertyex ( [catalog_id] , PopulateStatus) = 0
and
objectpropertyex ( table object_id , 'TableFulltextItemCount' ) shows that
there are many rows missing from the table so there is much work to be done.
i have seen this once before and ended up doing a rebuild but I hope to
avoid that this time around as it takes several days to rebuild all catalogs.
Has anyone seen anything similar to this or have any suggestions for the
full-text catalog to start 'picking back up' the auto changes?
Many thanks,
Robert Towne
check the full text logs, they can be found in C:\Program Files\Microsoft
SQL Server\MSSQL.1\MSSQL\LOG and will look like SQLFT0005600015.log
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"sql411@.nospam.com" <sql411nospamcom@.discussions.microsoft.com> wrote in
message news:D77BD52A-7107-4284-8522-60E95C45E15E@.microsoft.com...
> Hi all,
> I have multiple full-text catalogs on about 8 tables (sql server 2005 ee -
> all varchar(max)).
> A few days ago, they all went to an idle status and won't start picking up
> any new changes - all catalogs are auto update.
> when i say 'idle' - mean :
> objectpropertyex ( [table object_id] , 'TableFulltextPopulateStatus' ) = 0
> objectpropertyex ( [catalog_id] , PopulateStatus) = 0
> and
> objectpropertyex ( table object_id , 'TableFulltextItemCount' ) shows that
> there are many rows missing from the table so there is much work to be
> done.
> i have seen this once before and ended up doing a rebuild but I hope to
> avoid that this time around as it takes several days to rebuild all
> catalogs.
> Has anyone seen anything similar to this or have any suggestions for the
> full-text catalog to start 'picking back up' the auto changes?
> Many thanks,
> Robert Towne
|||Thanks, Hilary. I checked the logs initially and only saw a pause (from
backup) and resume (backup finished). it was weird because it happened
across all full-text catalogs - i could still do a contains search but they
just were idle as far as adding new records.
I did a reboot and that cleared it up. (I was hoping to avoid the reboot as
we are always doing business and it is difficult to get a maintenance period).
Thanks again,
robert

New One Here - Dont Laugh

Start playing around with SQL 2000 Server and Visual.net
made an instance of sql, I can go in and add tables,etc.
I downloaded MSDE2000 and installed as per instruction
went to a command prompted and entered
Setup.exe /qb+ INSTANCENAME=VSDOTNET DISABLENETWORKPROTOCOLS=1 SAPWD=<YOUR
SA PASSWORD>
an error was promtped, saying to go to the control panel to make changes?
-not what the instructions said-
Opened Visual and try to connect and I cant
Any ideals as to what went wrong other than a newbie learning?What was the exact error message?
Is "<YOUR SA PASSWORD>" your actual password? I assume you've entered the
real one.
ML
http://milambda.blogspot.com/|||Did you connect to the right sql server using the instance path?
note the back slash after the computer name
Driver={SQL Server};
Server=COMPUTER_NAME\VSDOTNET ;
Database=DB_NAME;
Uid=USER_NAME;
Pwd=PASSWORD;
"David W" <davidw@.mcintoshconstruction.net> wrote in message
news:elkla5uRGHA.224@.TK2MSFTNGP10.phx.gbl...
> Start playing around with SQL 2000 Server and Visual.net
> made an instance of sql, I can go in and add tables,etc.
> I downloaded MSDE2000 and installed as per instruction
> went to a command prompted and entered
> Setup.exe /qb+ INSTANCENAME=VSDOTNET DISABLENETWORKPROTOCOLS=1 SAPWD=<YOUR
> SA PASSWORD>
> an error was promtped, saying to go to the control panel to make changes?
> -not what the instructions said-
> Opened Visual and try to connect and I cant
> Any ideals as to what went wrong other than a newbie learning?
>
>

New Import/Export Wizard error

I am making the adjustment from SQL Server 2000 to SQL Server 2005. In the
past I have created named ranges in Excel that look like data tables (header
row followed by multiple data rows) and used DTS to create database tables i
n
SQL Server 2000. It has worked for several years. Now, using the SQL
Server 2005 import/export wizard, the same named ranges cause the following
error.
- Validating (Error)
Messages
Error 0xc00470fe: Data Flow Task: The product level is insufficient for
component "Source - B11Tbl" (1).
(SQL Server Import and Export Wizard)
Can anyone explain why it 'blows-up' and what I might do to fix it?
Thank you!Hi Mitch
Which version are you running?
John
"Mitch" wrote:

> I am making the adjustment from SQL Server 2000 to SQL Server 2005. In th
e
> past I have created named ranges in Excel that look like data tables (head
er
> row followed by multiple data rows) and used DTS to create database tables
in
> SQL Server 2000. It has worked for several years. Now, using the SQL
> Server 2005 import/export wizard, the same named ranges cause the followin
g
> error.
> - Validating (Error)
> Messages
> Error 0xc00470fe: Data Flow Task: The product level is insufficient f
or
> component "Source - B11Tbl" (1).
> (SQL Server Import and Export Wizard)
> Can anyone explain why it 'blows-up' and what I might do to fix it?
> Thank you!
>|||Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00
(xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600
I hope this gives the version info needed.
Mitch
"John Bell" wrote:
> Hi Mitch
> Which version are you running?
> John
> "Mitch" wrote:
>|||Hi
That does not tell me the edition!
You may want to test if it works with SP1.
John
"Mitch" wrote:
> Microsoft SQL Server Management Studio 9.00.1399.00
> Microsoft Analysis Services Client Tools 2005.090.1399.00
> Microsoft Data Access Components (MDAC) 2000.085.1117.00
> (xpsp_sp2_rtm.040803-2158)
> Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
> Microsoft Internet Explorer 6.0.2900.2180
> Microsoft .NET Framework 2.0.50727.42
> Operating System 5.1.2600
> I hope this gives the version info needed.
> Mitch
> "John Bell" wrote:
>|||Microsoft SQL Server Standard Edition
"John Bell" wrote:
> Hi
> That does not tell me the edition!
> You may want to test if it works with SP1.
> John
> "Mitch" wrote:
>|||Hi Mitch
Then I can't see why you are getting this message! Have you tried this
without the named ranges or a different version of Excel?
John
"Mitch" wrote:
> Microsoft SQL Server Standard Edition
> "John Bell" wrote:
>|||I'll have the IT people check the installation settings for SQL Server 2005.
The 2000 version of DTS still works just fine... The problem is only with
the 2005 version. Thank you for your help!
"John Bell" wrote:
> Hi Mitch
> Then I can't see why you are getting this message! Have you tried this
> without the named ranges or a different version of Excel?
> John
> "Mitch" wrote:
>|||Hi Mitch
SQL 2005 has most thing off by default, but I can't remember having to
change anything special for excel files, although I haven't tried named
ranges.
John
"Mitch" wrote:
> I'll have the IT people check the installation settings for SQL Server 200
5.
> The 2000 version of DTS still works just fine... The problem is only with
> the 2005 version. Thank you for your help!
> "John Bell" wrote:
>|||I get the error message several seconds after identifying the source and it
does not progress (show any more windows) from that point. The message
suggests that it is a Microsoft Jet Database Engine error message. We
watched the server during the attempt (several times) and absolutely nothing
is happening on the server end (SQL Server). My workstation has 2.0 GB RAM
so I doubt that RAM is a problem. The funny part is that the data transfer
works using SQL Server 2000 (which I no longer have installed). Any
suggestions?
"John Bell" wrote:
> Hi Mitch
> SQL 2005 has most thing off by default, but I can't remember having to
> change anything special for excel files, although I haven't tried named
> ranges.
> John
>
> "Mitch" wrote:
>|||Hi
It does sound like a driver issue, I have tested importing named ranges
without any issues.
John
"Mitch" wrote:
> I get the error message several seconds after identifying the source and i
t
> does not progress (show any more windows) from that point. The message
> suggests that it is a Microsoft Jet Database Engine error message. We
> watched the server during the attempt (several times) and absolutely nothi
ng
> is happening on the server end (SQL Server). My workstation has 2.0 GB RA
M
> so I doubt that RAM is a problem. The funny part is that the data transfe
r
> works using SQL Server 2000 (which I no longer have installed). Any
> suggestions?
> "John Bell" wrote:
>

new fact records

Hi there

Is there functionality in SQL 2005 to update only new records, e.g. only records from yesterday. I've seen functionality for dimension tables to only get new records but nothing for fact tables.

Thanks

Not altogether sure I understand the requirement but I'll try.

Once you have your records that need to be updated (by whatever means you use to do that), have a LOOKUP component that checks the fact table to see which of those rows were loaded yesterday (or whenever).

-Jamie

|||

Hi Jamie

Thanks for the reply, I want to do an iterative update of my datawarehouse add new records only rather than deleting the table and reimporting the entire table. I have read about variables etc and I thought there might be a way to query the data warehose to check the most recent record then use that to update my sql query which brings in the data. By the way I'm using ODBC connection for this.

Cheers,

Kiwi

|||

Read this and try method 2:

http://www.sqlis.com/default.aspx?311

-Jamie

|||Thanks very much I'll have a crack and let you know how I go