Accessing Excel functions in SQL SP

Hi,
I am trying to access an Excel function in a stored procedure. First of all,
is it possible, if it is, can someone give me an example.
Thank you.
--
RamIt may be possible with sp_OAMethod, but why do it to yourself? Even if you
can get to work without blowing up your computer, performance is going to be
rubbish!
SQL has loads of functions, and you can roll your own, do you know about
user-defined functions? They're great!
Anyway, tell us which function you want to emulate and we'll see what we can
do.
Plus, also bear in mind, SQL might have the function you need, but you just
don't know it's name; eg MID in Excel is called SUBSTRING in T-SQL, there's
a
ROUND function, CHARINDEX is the same as FIND in Excel, if you need to do
some conditional logic, there's the CASE statement instead of Excel's IF.
Remember to post some DDL, sample data, expected results etc.
Damien
First of all, there are few things you can't do in SQL
"ram4tech" wrote:

> Hi,
> I am trying to access an Excel function in a stored procedure. First of al
l,
> is it possible, if it is, can someone give me an example.
> Thank you.
> --
> Ram|||Hi Damien:
The looked in BOL and on the net, but didn't had much luck. I wasn't aware
that there might be some performance issues. The excel function I am plannin
g
on using is IRR().
Thank you.
--
Ram
"Damien" wrote:
> It may be possible with sp_OAMethod, but why do it to yourself? Even if y
ou
> can get to work without blowing up your computer, performance is going to
be
> rubbish!
> SQL has loads of functions, and you can roll your own, do you know about
> user-defined functions? They're great!
> Anyway, tell us which function you want to emulate and we'll see what we c
an
> do.
> Plus, also bear in mind, SQL might have the function you need, but you jus
t
> don't know it's name; eg MID in Excel is called SUBSTRING in T-SQL, there'
s a
> ROUND function, CHARINDEX is the same as FIND in Excel, if you need to do
> some conditional logic, there's the CASE statement instead of Excel's IF.
> Remember to post some DDL, sample data, expected results etc.
> Damien
> First of all, there are few things you can't do in SQL
> "ram4tech" wrote:
>|||Hello Ram !
http://groups.google.de/group/micro...5d4e46703ec82cd
HTH, jens Suessmeyer.|||Right, well you'd need to know the formula that sits behind IRR to recreate
it, but I guess it boils down to addition, subtraction, maybe an average or
two? T-SQL can do all that, but Excel is better at sums, I'll give it that.
So maybe you should play to the strengths, ie SQL for holding data,
concurrent access, raw power, Excel for sums.
Have you considered linking in to your server, eg a pivot table or external
query?
"ram4tech" wrote:
> Hi Damien:
> The looked in BOL and on the net, but didn't had much luck. I wasn't aware
> that there might be some performance issues. The excel function I am plann
ing
> on using is IRR().
> Thank you.
> --
> Ram
>
> "Damien" wrote:
>|||Here is a T-SQL implementation of IRR I posted a while back.
It is probably less robust than the Excel version, but it may still
work for you:
http://groups.google.co.uk/groups?q...eam+kass+newton
Steve Kass
Drew University
ram4tech wrote:

>Hi Damien:
>The looked in BOL and on the net, but didn't had much luck. I wasn't aware
>that there might be some performance issues. The excel function I am planni
ng
>on using is IRR().
>Thank you.
>

Access to statistical functions in stored procs

Is there anyway I can use the Correlation function in a stored proc? The
correlation function is part of OLAP. I have an application which requires
the display of the correlation coefficients between a base data set and many
instances of another data set. If I can't use the Correlation function in
OLAP, does anyone have suggestions of what I might be able to use?
AlanS
If this is for T-SQL and not MDX queries with analysis
services, then you can write your own. You can find some
examples if you search the SQL Server newsgroups on google.
One such example can be found at:
http://tinyurl.com/3rlhw
Another option would be to use the correlation function in
Excel and call the Excel functions in SQL Server using the
OLE Automation stored procedures. You can find more
information on the OLE Automation stored procedures in SQL
Server books online.
-Sue
On Fri, 3 Sep 2004 08:29:09 -0700, "AlanS"
<AlanS@.discussions.microsoft.com> wrote:

>Is there anyway I can use the Correlation function in a stored proc? The
>correlation function is part of OLAP. I have an application which requires
>the display of the correlation coefficients between a base data set and many
>instances of another data set. If I can't use the Correlation function in
>OLAP, does anyone have suggestions of what I might be able to use?
>AlanS

Access SQL functions through .net?

I usually access stored procedures using SQL data source. But now I need a string returned from the database. If I write a function in SQL how do I access it from an aspx.vb file?

Put it in a proc and run an EXEC query, same way as any other procedure.

Jeff

|||

If the SQL function returns a string, why not use SqlCommand.ExecuteScalar method:

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["myConn"].ToString()))
{
conn.Open();

string qstring = "SELECT dbo.fn_test('IORI')";

SqlCommand cmd = new SqlCommand(qstring, conn);
string s=cmd.ExecuteScalar().ToString();

Response.Write("The new string is:" + s);
}

|||Cool! Thanks guys!

Access' Last equivalent in SQL2005

Microsoft Access has the functions - First & Last that you can apply to a select list to limit the returned rows to those. I need to convert several queries, developed by a data researcher in Access, that use these functions into genuine Transact SQL.

I can use T-SQL's Top for the First function, but I'm stumped on what to use for Last.

Also, the inherited queries don't have any 'order' clauses, wouldn't they be necessary when using such a function?

Thanks.

For a 'q&d' (quick and dirty') way to accomplish your task, for LAST, just like using the TOP 1 function for the FIRST, you can reverse the sort [ORDER BY DESC] and retrieve the TOP 1 for the LAST.

And a custom function may be more efficient -it depends upon several factors. If you posted your entire query, someone here may be able to provide more directed assistance.

|||

This is the one I'm working on now(first), it's designed to show which students didn't finish school but were pretty close to a degree. It's nasty.

SELECT Last(fice_table.inst_type) AS LastOfinst_type, student_table.academic_year, Max(student_table.term) AS term, Last(student_table.geo_state) AS LastOfgeo_state, student_table.ssn_id, Last(student_table.degree_intent) AS LastOfdegree_intent, Last(student_table.student_level) AS LastOfstudent_level, graduated_student_table.ssn_id, Last(stdnt_lvl_desc_table.description) AS StudentLevel, Last(degr_intnt_desc_table.description) AS DegreeIntent

FROM (((student_table INNER JOIN fice_table

ON student_table.fice_code = fice_table.fice_code)

LEFT JOIN graduated_student_table

ON student_table.ssn_id = graduated_student_table.ssn_id)

INNER JOIN degr_intnt_desc_table

ON (student_table.academic_year = degr_intnt_desc_table.academic_year) AND (student_table.degree_intent = degr_intnt_desc_table.degree_intent))

INNER JOIN stdnt_lvl_desc_table

ON (student_table.academic_year = stdnt_lvl_desc_table.academic_year) AND (student_table.student_level = stdnt_lvl_desc_table.code)

GROUP BY student_table.academic_year, student_table.ssn_id, graduated_student_table.ssn_id

HAVING (((Last(fice_table.inst_type))="1") AND

((student_table.academic_year)="2006" And Not (student_table.academic_year)="2007") AND

((Last(student_table.geo_state))="04") AND

(Not (Last(student_table.degree_intent))="3") AND

((Last(student_table.student_level))="01" Or (Last(student_table.student_level))="02" Or (Last(student_table.student_level))="03" Or (Last(student_table.student_level))="04") AND

((graduated_student_table.ssn_id) Is Null));

And since she is using 'Last' without any ordering, I'm now wondering if the thing has ever worked correctly.

Thanks for your time.

|||

That is an interesting query. There is a bit of difference between Access and SQL Server. Access is a record manager and tables do have some order, so it might not be a problem. In SQL Server, it is much more particular about things like this. SQL Server optimizes query plans in a very strict manner.

|||

Interesting that's a kind word for it

I tried using top with descending indices, it was a disaster. I wound up with 1 row. Access seems able to stack up the Last functions, while SQL only allows a single TOP.

This is looking like it will have to be done with a program or stay in Access. It's beyond my meager T-SQL abilities.

Thanks.

|||

You can specify the number of rows TOP returns

SELECT TOP (5) * ...

or

DECLARE @.cnt int

SET @.cnt = 5

SELECT TOP (@.cnt) * ...

or

SELECT TOP (10) PERCENT * ...

|||

Paul,

What determines the 'LAST' entry in each of the tables?

(Is there a datetime column in each table?)

It would be helpful to have the table DDL...

|||

I'm not sure what determines 'Last' of each column. That lack of ordering was the basis of my questioning the whole history of the use of this query.

But generally, the columns that are 'Last'ed are char(1 or 2) with strictly numeric data. e.g. the domain of fice_table.inst_type is '1', '2', '3', '4' but there is no index on that column. Likewise with student_table.geo_state, the type is char(2) with domain of '00' -> '50' inclusive & '98' & '99' again no index on that column.

And I know that I can specify TOP x <col name>, but I need the Last one of each group by and I can't figure out how to do that. I've been stumped forever by a simple query to get the top 5 scores from each county from a table with a county code column domain of '00' to '75' and an integer score column. I know it can be done and it has to be the grouping that's the trouble.

|||

If you want to post some sample DDL (create table, etc.) and sample data (preferrablyin the form of INSERT statements)...we can all take a stab at coming up with something that works for you.

|||

You should be able to use the row_number() function to some advantage here. Elsewhere you asked about finding the top 5 rows for each student, which you can also do this way.

WITH Ranked AS (
select
<the columns you need>,
row_number() over (
partition by
student_table.academic_year,
student_table.ssn_id,
graduated_student_table.ssn_id
order by
whatever column list will put the rows
you want for each student/year at the top
) as rk
from your join
), TopFiveEachStudentEachYear AS (
select * from Ranked
where rk <= 5
)
select * from TopFiveEachStudentEachYear
-- or a different query against these results

The ordering in the OVER clause may benefit from expressions like:

ORDER BY

student_table.student_level DESC,

case when student table_academic_year = '2006' then 0 else 1 end,

...

Steve Kass

Drew University

www.stevekass.com

|||

OK, here's the DDL, I'm cutting out a LOT of columns and deleting a bunch of indices (it's a huge database):

This first table is kind of the parent of all tables underneath, one row per college:

Code Snippet

USE [sisdb]
GO
/****** Object: Table [dbo].[fice_table] Script Date: 07/03/2007 14:00:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fice_table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[fice_table](
[fice_code] [char](6) NOT NULL,
[school_name] [varchar](62) NOT NULL,
[school_abbr] [varchar](8) NOT NULL,
[school_type] [char](1) NULL,
[inst_type] [char](1) NULL,
[act_code] [char](4) NULL,
[fap_code] [char](3) NULL,
[ipeds_code] [char](6) NULL,
[type_code] [char](1) NULL,
[sreb_cat] [char](2) NULL,
[ets_code] [char](4) NULL,
[system_code] [char](1) NULL,
[title_iv_code] [char](6) NULL,
CONSTRAINT [PK_fice_table] PRIMARY KEY CLUSTERED
(
[fice_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
GO

This is the student_table, one row per student per term per year per college, so a student can have up to 4 rows per year 0 = Summer II, 1 = Fall, 2 = Spring, 3 = Summer I

Code Snippet

USE [sisdb]
GO
/****** Object: Table [dbo].[student_table] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[student_table](
[fice_code] [char](6) NOT NULL,
[academic_year] [char](4) NOT NULL,
[term] [char](1) NOT NULL,
[ssn_id] [char](9) NOT NULL,
...
[geo_county] [char](3) NULL,
[geo_state] [char](2) NULL,
...
[degree_intent] [char](1) NULL,
[attend_status] [char](1) NULL,
[init_admit_date] [char](6) NULL,
[student_level] [char](2) NULL,
...
[diploma_ged] [char](1) NULL,
...
[on_cr_hours] [tinyint] NULL,
[off_cr_hours] [tinyint] NULL,
...
) ON [PRIMARY]
SET ANSI_PADDING ON
/****** Object: Index [PK_student_table] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND name = N'PK_student_table')
ALTER TABLE [dbo].[student_table] ADD CONSTRAINT [PK_student_table] PRIMARY KEY CLUSTERED
(
[fice_code] ASC,
[academic_year] ASC,
[term] ASC,
[ssn_id] ASC
)...
END
GO
SET ANSI_PADDING OFF
GO

/****** Object: Index [_dtw_index_student_table] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND name = N'_dtw_index_student_table')
CREATE NONCLUSTERED INDEX [_dtw_index_student_table] ON [dbo].[student_table]
(
[academic_year] ASC,
[term] ASC,
[fice_code] ASC
)
INCLUDE ( [student_level],
[on_cr_hours]) ...
GO

/****** Object: Index [fice_ssn_admit_idx] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND name = N'fice_ssn_admit_idx')
CREATE NONCLUSTERED INDEX [fice_ssn_admit_idx] ON [dbo].[student_table]
(
[fice_code] ASC,
[ssn_id] ASC,
[admit_status] ASC,
[hs_grad_year] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [SECONDARY]
GO

/****** Object: Index [fice_ssn_enroll_status_idx] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND name = N'fice_ssn_enroll_status_idx')
CREATE NONCLUSTERED INDEX [fice_ssn_enroll_status_idx] ON [dbo].[student_table]
(
[fice_code] ASC,
[ssn_id] ASC,
[enroll_status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [SECONDARY]
GO

/****** Object: Index [fice_year_term_ssn_idx] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND name = N'fice_year_term_ssn_idx')
CREATE NONCLUSTERED INDEX [fice_year_term_ssn_idx] ON [dbo].[student_table]
(
[fice_code] ASC,
[academic_year] ASC,
[term] ASC,
[ssn_id] ASC
)...
GO

Now here is the graduated_student_table, again lot's cut out:

Code Snippet

USE [sisdb]
GO
/****** Object: Table [dbo].[graduated_student_table] Script Date: 07/03/2007 13:54:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[graduated_student_table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[graduated_student_table](
[fice_code] [char](6) NOT NULL,
[academic_year] [char](4) NOT NULL,
[ssn_id] [char](9) NOT NULL,
...
[graduation_date] [char](6) NULL,
[degree_level] [char](2) NULL,
...

[init_enroll_status] [char](1) NULL,
[init_attend_status] [char](1) NULL
) ON [PRIMARY]
SET ANSI_PADDING ON
...

/****** Object: Index [PK_graduated_student_table] Script Date: 07/03/2007 13:54:12 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[graduated_student_table]') AND name = N'PK_graduated_student_table')
ALTER TABLE [dbo].[graduated_student_table] ADD CONSTRAINT [PK_graduated_student_table] PRIMARY KEY CLUSTERED
(
[fice_code] ASC,
[academic_year] ASC,
[ssn_id] ASC,
[degree_1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
END
GO

Now here is the stdnt_lvl_desc_table, there are codes for each student level, 01 freshman, 02 sophomore, etc, etc.

Code Snippet

USE [sisdb]
GO
/****** Object: Table [dbo].[stdnt_lvl_desc_table] Script Date: 07/03/2007 13:58:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stdnt_lvl_desc_table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[stdnt_lvl_desc_table](
[academic_year] [char](4) NOT NULL,
[code] [char](2) NOT NULL,
[description] [varchar](40) NOT NULL,
CONSTRAINT [PK_stdnt_lvl_desc_table] PRIMARY KEY NONCLUSTERED
(
[academic_year] ASC,
[code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF

And the degree_intnt_desc_table:

There are codes for the reason the student began college, 1 thru 9 for Bacc Degree Seeking, Assoc Degree Seeking, etc, etc.

Code Snippet

USE [sisdb]
GO
/****** Object: Table [dbo].[degr_intnt_desc_table] Script Date: 07/03/2007 13:59:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[degr_intnt_desc_table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[degr_intnt_desc_table](
[academic_year] [char](4) NOT NULL,
[degree_intent] [char](1) NOT NULL,
[description] [varchar](50) NULL,
[include_in_fall_cohort] [char](1) NULL,
CONSTRAINT [PK_degr_intnt_desc_table] PRIMARY KEY CLUSTERED
(
[academic_year] ASC,
[degree_intent] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF

As for sample data, I'm afraid I can't let that out and my VSDBPro project isn't finished yet. But, you can imagine that each row in the student_table has a Social Security Number or College Assigned ID and the graduated_student_table has a corresponding ssn_id for graduates.

Access functions to SQL Server

I'm upsizing MS-Access to SQL Server 2005.
I need to convert the following functions:
TRANSFORM
PIVOT
FORMAT
MID

Are there any similar functions in SQL Server?

Also I have a query as follows:

SELECT Mid$([AccountNumber],3,8) AS [Account#], Format([checkamount]*100,"000000000") AS Amount, IIf(IsNull([statusdate]),"",Format([statusdate],"yyyymmdd")) AS [Date]
FROM tblResult;

How do I convert this in SQL Server query?

Hi,

SELECT
SUBSTRING([AccountNumber],3,8) AS [Account#],
RIGHT('000000000' + CAST([checkamount]*100 AS VARCHAR(9)),9) AS Amount,
ISNULL(CONVERT([statusdate],112),'') AS [Date]
FROM tblResult

TRANSFORM => Can be done with Pivot in SQL Server 2005. In SQL 2k you will have to use CASE statements to produce crosstabs queries.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks.

There's one error: StatusDate is not a defined system type
StatusDate is defined as Date in database.

also, is it possible to give an example for pivot?

I have a Transform query as follows:

TRANSFORM Sum(CDbl([CheckAmount]))

AS [The Value]

SELECT "Total

Amount of Checks Issued" AS Type, tblResult.AccountNumber,

tblResult.CheckDate, tblResult.Status, Sum(CDbl([CheckAmount]))

AS Total

FROM tblResult

GROUP BY "Total Amount

of Checks Issued", tblResult.AccountNumber, tblResult.CheckDate,

tblResult.Status

PIVOT IIf(IsNull([statusdate]),"Outstanding",Format([StatusDate],"Short

Date"));

|||

Please check out the following resources for migrating from Access to SQL Server. We also have a tool to help with that.

http://www.microsoft.com/sql/solutions/migration/access/default.mspx

|||I'm using SSMA to convert tables, but unfortunately this tool does not convert queries.|||I don't know the exact behavior of TRANSFORM so you will have to ask in the SSMA newsgroup or look at the examples in Books Online and convert your query. You can also post your current schema and expected results & it will be easier to suggest the query in SQL Server.|||Here's the schema and query:
tblResult
[Autonumber] = <int>
,[AccountNumber] = <varchar(50)>
,[CheckNumber] = <float>
,[CheckAmount] = <money>
,[CheckDate] = <datetime>
,[StatusDate] = <datetime>
,[Status] = <varchar(50)>
,[AccountID] = <varchar(50)>
,[Payee] = <varchar(50)>
,[Soc] = <varchar(50)>
,[ID-1] = <varchar(255)>
,[ID-2] = <varchar(255)>
,[ID-3] = <varchar(255)>

Query:
In MS Access:

SELECT Mid$([AccountNumber],3,8) AS [Account#], Format([checkamount]*100,"000000000") AS Amount, IIf(IsNull([statusdate]),"",Format([statusdate],"yyyymmdd")) AS [Date]
FROM tblResult;

SQL Server:
SELECT
SUBSTRING([AccountNumber],3,8) AS [Account#],
RIGHT('000000000' + CAST([checkamount]*100 AS VARCHAR(9)),9) AS Amount,
ISNULL(CONVERT([statusdate],112),'') AS [Date]
FROM tblResult

Access Functions not Working

Hi--
We just upgraded our server and now have Sql 2005 as a backend to our
Access ADP. With SQL 2000, we had used functions like Date() to provide
default values for certain fields. Now, these functions are not working
on the clients. However, they do work on every computer with SQL 2005
installed. I have 2005 installed on my laptop and the server and am
having no problems on those computers. Is there anything I can do to
the clients to resolve this issue' Thanks in advance.
ChrisDate() is VB/VBA function that only runs inside front app's code (ADP's VBA
code). It has nothing to do with back end, be it SQL Server2000 or SQL
Server2005. If you used Date() in backend query (SP, View...) it should not
have worked at all, because SQL Server2000/2005 would not recognize it.
Your issue is most likely cuased by missing reference(s) that is required by
your ADP app itself. Go to VBA Editor and click menu "Tools->References..."
to looking for missing reference(s).
<creejohnson@.gmail.com> wrote in message
news:1159308950.973840.54110@.i3g2000cwc.googlegroups.com...
> Hi--
> We just upgraded our server and now have Sql 2005 as a backend to our
> Access ADP. With SQL 2000, we had used functions like Date() to provide
> default values for certain fields. Now, these functions are not working
> on the clients. However, they do work on every computer with SQL 2005
> installed. I have 2005 installed on my laptop and the server and am
> having no problems on those computers. Is there anything I can do to
> the clients to resolve this issue' Thanks in advance.
> Chris
>|||Thanks, Norman.
I am aware that Date() is a vba function, but it DID work with sql 2000
and does not work with Sql 2005. It is used only in the front end as a
default value of a text box. That is what is mystifying me. There are
no missing references..already checked that. Any other ideas? The thing
that really makes me wonder is that the defaults work on those
computers that have the client tools/legacy components installed for
sql 2005.
Chris
Norman Yuan wrote:[vbcol=seagreen]
> Date() is VB/VBA function that only runs inside front app's code (ADP's VB
A
> code). It has nothing to do with back end, be it SQL Server2000 or SQL
> Server2005. If you used Date() in backend query (SP, View...) it should no
t
> have worked at all, because SQL Server2000/2005 would not recognize it.
> Your issue is most likely cuased by missing reference(s) that is required
by
> your ADP app itself. Go to VBA Editor and click menu "Tools->References...
"
> to looking for missing reference(s).
> <creejohnson@.gmail.com> wrote in message
> news:1159308950.973840.54110@.i3g2000cwc.googlegroups.com...|||It is hard to debug "not working". Possibly that function result in date bei
ng passed as a string to
SQL Server, and different string formats being messed up due to language set
tings. Use Profiler to
see what is really submitted to SQL Server. Also see
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<creejohnson@.gmail.com> wrote in message news:1159308950.973840.54110@.i3g2000cwc.googlegroup
s.com...
> Hi--
> We just upgraded our server and now have Sql 2005 as a backend to our
> Access ADP. With SQL 2000, we had used functions like Date() to provide
> default values for certain fields. Now, these functions are not working
> on the clients. However, they do work on every computer with SQL 2005
> installed. I have 2005 installed on my laptop and the server and am
> having no problems on those computers. Is there anything I can do to
> the clients to resolve this issue' Thanks in advance.
> Chris
>|||Thanks, Tibor.
I guess I should be more specific on the problem, but by not working, I
mean not working:-) There is nothing in any textbox using the Date()
VBA function when there should be (used to be before the switch)
today's date.
I have a couple of ideas to check into tomorrow, but any help you could
give would be appreciated.
Thanks
Chris
Tibor Karaszi wrote:[vbcol=seagreen]
> It is hard to debug "not working". Possibly that function result in date b
eing passed as a string to
> SQL Server, and different string formats being messed up due to language s
ettings. Use Profiler to
> see what is really submitted to SQL Server. Also see
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <creejohnson@.gmail.com> wrote in message news:1159308950.973840.54110@.i3g2
000cwc.googlegroups.com...|||Seems to be an Access issue, then. I suggest you ask this in an Access group
, as we are more into
the engine here... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<creejohnson@.gmail.com> wrote in message
news:1159404812.588775.157020@.m7g2000cwm.googlegroups.com...
> Thanks, Tibor.
> I guess I should be more specific on the problem, but by not working, I
> mean not working:-) There is nothing in any textbox using the Date()
> VBA function when there should be (used to be before the switch)
> today's date.
> I have a couple of ideas to check into tomorrow, but any help you could
> give would be appreciated.
> Thanks
> Chris
> Tibor Karaszi wrote:
>

Access Functions not Working

Hi--
We just upgraded our server and now have Sql 2005 as a backend to our
Access ADP. With SQL 2000, we had used functions like Date() to provide
default values for certain fields. Now, these functions are not working
on the clients. However, they do work on every computer with SQL 2005
installed. I have 2005 installed on my laptop and the server and am
having no problems on those computers. Is there anything I can do to
the clients to resolve this issue? Thanks in advance.
Chris
Date() is VB/VBA function that only runs inside front app's code (ADP's VBA
code). It has nothing to do with back end, be it SQL Server2000 or SQL
Server2005. If you used Date() in backend query (SP, View...) it should not
have worked at all, because SQL Server2000/2005 would not recognize it.
Your issue is most likely cuased by missing reference(s) that is required by
your ADP app itself. Go to VBA Editor and click menu "Tools->References..."
to looking for missing reference(s).
<creejohnson@.gmail.com> wrote in message
news:1159308950.973840.54110@.i3g2000cwc.googlegrou ps.com...
> Hi--
> We just upgraded our server and now have Sql 2005 as a backend to our
> Access ADP. With SQL 2000, we had used functions like Date() to provide
> default values for certain fields. Now, these functions are not working
> on the clients. However, they do work on every computer with SQL 2005
> installed. I have 2005 installed on my laptop and the server and am
> having no problems on those computers. Is there anything I can do to
> the clients to resolve this issue? Thanks in advance.
> Chris
>
|||Thanks, Norman.
I am aware that Date() is a vba function, but it DID work with sql 2000
and does not work with Sql 2005. It is used only in the front end as a
default value of a text box. That is what is mystifying me. There are
no missing references..already checked that. Any other ideas? The thing
that really makes me wonder is that the defaults work on those
computers that have the client tools/legacy components installed for
sql 2005.
Chris
Norman Yuan wrote:[vbcol=seagreen]
> Date() is VB/VBA function that only runs inside front app's code (ADP's VBA
> code). It has nothing to do with back end, be it SQL Server2000 or SQL
> Server2005. If you used Date() in backend query (SP, View...) it should not
> have worked at all, because SQL Server2000/2005 would not recognize it.
> Your issue is most likely cuased by missing reference(s) that is required by
> your ADP app itself. Go to VBA Editor and click menu "Tools->References..."
> to looking for missing reference(s).
> <creejohnson@.gmail.com> wrote in message
> news:1159308950.973840.54110@.i3g2000cwc.googlegrou ps.com...
|||It is hard to debug "not working". Possibly that function result in date being passed as a string to
SQL Server, and different string formats being messed up due to language settings. Use Profiler to
see what is really submitted to SQL Server. Also see
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<creejohnson@.gmail.com> wrote in message news:1159308950.973840.54110@.i3g2000cwc.googlegrou ps.com...
> Hi--
> We just upgraded our server and now have Sql 2005 as a backend to our
> Access ADP. With SQL 2000, we had used functions like Date() to provide
> default values for certain fields. Now, these functions are not working
> on the clients. However, they do work on every computer with SQL 2005
> installed. I have 2005 installed on my laptop and the server and am
> having no problems on those computers. Is there anything I can do to
> the clients to resolve this issue? Thanks in advance.
> Chris
>
|||Thanks, Tibor.
I guess I should be more specific on the problem, but by not working, I
mean not working:-) There is nothing in any textbox using the Date()
VBA function when there should be (used to be before the switch)
today's date.
I have a couple of ideas to check into tomorrow, but any help you could
give would be appreciated.
Thanks
Chris
Tibor Karaszi wrote:[vbcol=seagreen]
> It is hard to debug "not working". Possibly that function result in date being passed as a string to
> SQL Server, and different string formats being messed up due to language settings. Use Profiler to
> see what is really submitted to SQL Server. Also see
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <creejohnson@.gmail.com> wrote in message news:1159308950.973840.54110@.i3g2000cwc.googlegrou ps.com...
|||Seems to be an Access issue, then. I suggest you ask this in an Access group, as we are more into
the engine here... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<creejohnson@.gmail.com> wrote in message
news:1159404812.588775.157020@.m7g2000cwm.googlegro ups.com...
> Thanks, Tibor.
> I guess I should be more specific on the problem, but by not working, I
> mean not working:-) There is nothing in any textbox using the Date()
> VBA function when there should be (used to be before the switch)
> today's date.
> I have a couple of ideas to check into tomorrow, but any help you could
> give would be appreciated.
> Thanks
> Chris
> Tibor Karaszi wrote:
>

Access Functions not Working

Hi--
We just upgraded our server and now have Sql 2005 as a backend to our
Access ADP. With SQL 2000, we had used functions like Date() to provide
default values for certain fields. Now, these functions are not working
on the clients. However, they do work on every computer with SQL 2005
installed. I have 2005 installed on my laptop and the server and am
having no problems on those computers. Is there anything I can do to
the clients to resolve this issue' Thanks in advance.
ChrisDate() is VB/VBA function that only runs inside front app's code (ADP's VBA
code). It has nothing to do with back end, be it SQL Server2000 or SQL
Server2005. If you used Date() in backend query (SP, View...) it should not
have worked at all, because SQL Server2000/2005 would not recognize it.
Your issue is most likely cuased by missing reference(s) that is required by
your ADP app itself. Go to VBA Editor and click menu "Tools->References..."
to looking for missing reference(s).
<creejohnson@.gmail.com> wrote in message
news:1159308950.973840.54110@.i3g2000cwc.googlegroups.com...
> Hi--
> We just upgraded our server and now have Sql 2005 as a backend to our
> Access ADP. With SQL 2000, we had used functions like Date() to provide
> default values for certain fields. Now, these functions are not working
> on the clients. However, they do work on every computer with SQL 2005
> installed. I have 2005 installed on my laptop and the server and am
> having no problems on those computers. Is there anything I can do to
> the clients to resolve this issue' Thanks in advance.
> Chris
>|||Thanks, Norman.
I am aware that Date() is a vba function, but it DID work with sql 2000
and does not work with Sql 2005. It is used only in the front end as a
default value of a text box. That is what is mystifying me. There are
no missing references..already checked that. Any other ideas? The thing
that really makes me wonder is that the defaults work on those
computers that have the client tools/legacy components installed for
sql 2005.
Chris
Norman Yuan wrote:
> Date() is VB/VBA function that only runs inside front app's code (ADP's VBA
> code). It has nothing to do with back end, be it SQL Server2000 or SQL
> Server2005. If you used Date() in backend query (SP, View...) it should not
> have worked at all, because SQL Server2000/2005 would not recognize it.
> Your issue is most likely cuased by missing reference(s) that is required by
> your ADP app itself. Go to VBA Editor and click menu "Tools->References..."
> to looking for missing reference(s).
> <creejohnson@.gmail.com> wrote in message
> news:1159308950.973840.54110@.i3g2000cwc.googlegroups.com...
> > Hi--
> >
> > We just upgraded our server and now have Sql 2005 as a backend to our
> > Access ADP. With SQL 2000, we had used functions like Date() to provide
> > default values for certain fields. Now, these functions are not working
> > on the clients. However, they do work on every computer with SQL 2005
> > installed. I have 2005 installed on my laptop and the server and am
> > having no problems on those computers. Is there anything I can do to
> > the clients to resolve this issue' Thanks in advance.
> >
> > Chris
> >|||It is hard to debug "not working". Possibly that function result in date being passed as a string to
SQL Server, and different string formats being messed up due to language settings. Use Profiler to
see what is really submitted to SQL Server. Also see
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<creejohnson@.gmail.com> wrote in message news:1159308950.973840.54110@.i3g2000cwc.googlegroups.com...
> Hi--
> We just upgraded our server and now have Sql 2005 as a backend to our
> Access ADP. With SQL 2000, we had used functions like Date() to provide
> default values for certain fields. Now, these functions are not working
> on the clients. However, they do work on every computer with SQL 2005
> installed. I have 2005 installed on my laptop and the server and am
> having no problems on those computers. Is there anything I can do to
> the clients to resolve this issue' Thanks in advance.
> Chris
>|||Thanks, Tibor.
I guess I should be more specific on the problem, but by not working, I
mean not working:-) There is nothing in any textbox using the Date()
VBA function when there should be (used to be before the switch)
today's date.
I have a couple of ideas to check into tomorrow, but any help you could
give would be appreciated.
Thanks
Chris
Tibor Karaszi wrote:
> It is hard to debug "not working". Possibly that function result in date being passed as a string to
> SQL Server, and different string formats being messed up due to language settings. Use Profiler to
> see what is really submitted to SQL Server. Also see
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <creejohnson@.gmail.com> wrote in message news:1159308950.973840.54110@.i3g2000cwc.googlegroups.com...
> > Hi--
> >
> > We just upgraded our server and now have Sql 2005 as a backend to our
> > Access ADP. With SQL 2000, we had used functions like Date() to provide
> > default values for certain fields. Now, these functions are not working
> > on the clients. However, they do work on every computer with SQL 2005
> > installed. I have 2005 installed on my laptop and the server and am
> > having no problems on those computers. Is there anything I can do to
> > the clients to resolve this issue' Thanks in advance.
> >
> > Chris
> >|||Seems to be an Access issue, then. I suggest you ask this in an Access group, as we are more into
the engine here... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<creejohnson@.gmail.com> wrote in message
news:1159404812.588775.157020@.m7g2000cwm.googlegroups.com...
> Thanks, Tibor.
> I guess I should be more specific on the problem, but by not working, I
> mean not working:-) There is nothing in any textbox using the Date()
> VBA function when there should be (used to be before the switch)
> today's date.
> I have a couple of ideas to check into tomorrow, but any help you could
> give would be appreciated.
> Thanks
> Chris
> Tibor Karaszi wrote:
>> It is hard to debug "not working". Possibly that function result in date being passed as a string
>> to
>> SQL Server, and different string formats being messed up due to language settings. Use Profiler
>> to
>> see what is really submitted to SQL Server. Also see
>> http://www.karaszi.com/SQLServer/info_datetime.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> <creejohnson@.gmail.com> wrote in message
>> news:1159308950.973840.54110@.i3g2000cwc.googlegroups.com...
>> > Hi--
>> >
>> > We just upgraded our server and now have Sql 2005 as a backend to our
>> > Access ADP. With SQL 2000, we had used functions like Date() to provide
>> > default values for certain fields. Now, these functions are not working
>> > on the clients. However, they do work on every computer with SQL 2005
>> > installed. I have 2005 installed on my laptop and the server and am
>> > having no problems on those computers. Is there anything I can do to
>> > the clients to resolve this issue' Thanks in advance.
>> >
>> > Chris
>> >
>