using Excel functions in SQL server

I'm trying to use an excel function 'NormsInv" in a View on SQL server. MSKB says
Quote: Microsoft® SQL Server™ 2000 Analysis Services supports many functions in the Microsoft Excel worksheet library, which is automatically registered if installed on the computer with Analysis Services. Functions not supported in this release are marked by an asterisk in this table. (NormsInv is NOT marked by an asterisk)


I am confused about this "Analysis services" stuff, which I'm not (as far as I know) using. I'm just using SQL Server Management Studio. Should the excel functions be available?


Post your answer or comment

comments powered by Disqus
Hi,

Is there a difference between the statistical functions in access and those in SQL server?

For instance, is there a median function in SQL-server?

I have been searching through the forum for a way to create a View that only lists transactions that have occurred in the current month. I was able to create an Access Query that used DateSerial to create the following Criteria under the TransactionDate field:
[INDENT]BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND [INDENT]DateSerial(Year(Date()), Month(Date())+1, 0)

When trying this in a View the first error was the Date() function. I replaced the date function with GETDATE(), the Year and Month with DATEPART() to create this function:
[INDENT]BETWEEN DateSerial(DATEPART(yy,GETDATE()), [INDENT]DATEPART(mm,GETDATE()), 1) AND [INDENT]DateSerial(DATEPART(yy,GETDATE()), [INDENT]DATEPART(mm,GETDATE())+1, 0)

But SQL Server then reminded me that DateSerial is not a valid function in SQL Server. Any ideas on creating a way to restrict data in a View to only those transactions occurring in the current month is most appreciated.

Ok

I want to create pivot in SQL Server 2008 and link it to an excel 07 worksheet.

And I kind of want that download into excel to be fired by a vba function.

I'm guessing create a view and then use the connection manager.
Ok that seems fine.

But how do you trigger that load from a VBA excel function?

Thanks

Hi All,

I m using MS-access as backend and I am using the following query where I use FIRST() as aggregrate function.

SELECT FIRST(COUNTRY) AS FIRSTOFCOUNTRY, ZONECODE FROM LOCATIONS GROUP BY COUNTRY,ZONECODE
HAVING ZONECODE = "ZONE01"

Now I have to shift my access database to SQL server 2000 where First() function does not work. I had searched for that but still cant find the alternative.

Can any one help me in solving this problem?

Thanks in advance,

Regards,

Jigs

I have loads of experience with Access but very little with SQL server, so please bear with me....

I am using SQL Server 2005 and Access 2002. Using mdb, NOT adp.

I have created a paramaterized table-value function in SQL server, named
fnTrainingSummary3

The function based on a View named TrainingSummary3c. The function's only purpose is to allow the user to filter the view using EmployeeID as a parameter. Trying to filter out everything server-side except the training data for that one employee that the user will choose.

So, in the SQL server manager, I can see that the function is there.... but how do I use it from Access???? I thought maybe it would link, just like a Table or a View, but it's not there in the list when I try to link from Access.

In Chipman and Baron's "Microsoft Access Developer's Guide to SQL Server", pg 416, there is an example where they indicate I would just use:
Quote: SELECT * FROM fnTrainingSummary3('12345') where 12345 is the EmployeeID. Which would lead me to believe that my Access database should be seeing that fnTrainingSummary3 as a table...?


Am I going at this totally the wrong way?

hi
i have a date field in sql server 2000, smalldatetime data type
what i cant get to is the hh:mm

for example

31/08/2005 12:01:00

using

CAST(DATEPART(hh, dbo.tbl_log.date) AS varchar) + ':' + CAST(DATEPART(mi, dbo.tbl_log.date) AS varchar)

i get 12:1

what i require is 12:01

thank you in advance

Hi all,

I have upsized my SQL database. Now when I create tables in my database they dont show in the SQL server.
How do I make this happen?

I am using access 2007 and SQL server 2008.

Thanks

Martin

Is it possible without using bcp or SSIS to create a csv file from a query or a table in SQL Server 2005?

Running a bcp requires xp_cmdshell, due to safety protocols i can't use that.
Creating a SSIS package requires cooperation from a different department i don't want that.

So i am looking for a different solution.

Thanks.

Hi All -
We are in the process of trying to move over a bunch of queries from Access 2007 to SQL Server 2008. I am having some difficulty trying to figure out how to convert the following code, which is a crosstab query in Access, to a pivot table in SQL Server. I believe this is one of the more complex ones.

TRANSFORM Count(dbo_v_CCP_SCCA_EventSummary.Event_Id) AS CountOfEvent_Id
SELECT dbo_vLabTestTypes.Problem, dbo_v_CCP_SCCA_EventSummary.Location_Service_Descr iption
FROM dbo_v_CCP_SCCA_EventSummary INNER JOIN dbo_vLabTestTypes ON dbo_v_CCP_SCCA_EventSummary.Event_Id = dbo_vLabTestTypes.Event_ID
WHERE (((dbo_v_CCP_SCCA_EventSummary.Event_Type_Level_1) ="Laboratory test"))
GROUP BY dbo_vLabTestTypes.Problem, dbo_v_CCP_SCCA_EventSummary.Location_Service_Descr iption
PIVOT Year([Event_Occurence_Date]) & " Q" & DatePart("q",[Event_Occurence_Date]);

I have attached an Excel spreadsheet so you can see what the code produces.

I have been googling this to death without a whole lot of success so thought I would check here to see if anyone has had some success with making the conversion.

Thanks in advance for the assistance!

Stacy

Hi,

I am trying to use the YIELD worksheet function in Access but can't get it to work. It comes with Error Sub or function not defined.

As per help file I have installed msowcf.dll but it still doesn't work.

Any ideas?

Thanks

I am trying to use the Right(0 function in SQL, I used it in A97 and it worked, but it doesnt work in A2k.

Example:

SQL_ADD = "INSERT INTO TMP_E SELECT '" & strCNum & "' AS IP_CNum,'" & strDate & "' AS IP_Date,'" & strSNCut & "' AS M_Serial, Right(Name,10) As FC_Code, Data As FC_Count FROM TMP_CII;"

Any ideas??

Cheers

Will

I have been creating a version of our database for laptop use which would have a standard mdb back end as opposed to the SQL server back end we have in our building.

I have it working fine except that there is one query which has 4 joins within. When I use this query with the mdb back end it is a non-updateable query (won't let me change any of the field values). In SQL server it works just fine.

I have checked all of the table properties and user permissions and everything allows writing to this table, except when it it's in this query.

If I remove the joins, I can then change the data.

Can anyone tell me what the problem might be? Thanks

I have an Access front end for my SQL Server db.

I need to execute a stored procedure in SQL Server from Access or exectute the SQL from access to the SQL Server.

Does anyone know how I'd go about this.

I sort of half know what I'm doing, I've done a fare bit of ASP and VBA, but I can't find any reference to connect to the SQL Server from Access using VBA in my books.

I think if anyone knows the SQL Server vesion of:

SET db = currentdb()

I'd able able to work the rest out.

Thanks

I am looking into getting certified in SQL Server. Anyone have any good advice on where to start, what kind of books or training to acquire, etc?

Can anyone please advise on a security design to use Database Roles in SQL server 2008 when using MS Acess 2007 as a front end.

How can I integrated DB Roles into MS Access when the security use to be handled by the forms when the database was is Acsess?

I link the database through ODBC. I would much like to use .ADP but since Microsoft will stop supporting it I don't see it as a viable option.

Thanks

Hi,

I'm struggling with this aggregate function in SQL, and am wondering if someone could point me in the right direction.

The code is as follows:


SELECT Format(Sum(IIf(DateDiff("h",[start date],[abc_time])

I have written a simple function that I would like to use in a query. The function would return a value for each record where the parameters equals 2 different fields of the same record.


	Code:
	Private Function FctConvertInch(LineDim As String, LineShape As String)

'To Convert a String to a value and then convert that value from mm to inches
'Lines dimension = 00.0000x00.0000 if line is flat
'Lines dimension = 00.0000 if line is round
Dim StNum1 As String
Dim StNum2 As String
Dim DbNum1 As Double
Dim DbNum2 As Double

If LineShape = "round" Then
'"CDbl" convert a string to Double number
FctConvertInch = CDbl([LineDim]) / 25.4
ElseIf LineShape = "flat" Then
StNum1 = Left([LineDim], 7)
StNum2 = Right([LineDim], 7)
DbNum1 = CDbl(StNum1)
DbNum2 = CDbl(StNum2)
FctConvertInch = (DbNum1 / 25.4) & "x" & (DbNum2 / 25.4)
End If
End Function

I have put the function in a new module, and have try to use the function in a query with the fields LineDim and LineShape , I've also added the field FctConvertInch(LineDim,LineShape) but it doesn't work. Am I using the function the the correct way?

I have a field I want to calculate using IIF. There are about 40 possible values, thus I have about 40 nested IIFs. Because I get "query is too complex" when I try to nest all 40 IIFs, I've had to break this down into 3 fields. I then have to do a little more manipulation to end up with the 1 field I originally wanted. All that works fine, however, I'd like to be able to calculate the value in 1 field instead of 4 fields.

I'm not too good with VBA, but looking at some examples, I tried creating the following function (this is just a small sample, but if I'm on the right track there will be about 40 of these "Step" variables and ElseIfs):

~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Function IfThen(BYStep As String)

Dim test2 As Integer
Dim Step070 As Integer
Dim Step075 As Integer
Dim Step080 As Integer


If
BYStep = "07.0" Then
test2 = Step070

ElseIf BYStep = "07.5" Then
test2 = Step075

ElseIf BYStep = "08.0" Then
test2 = Step080

Else: test2 = 0
End If

End Function
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

In my query I have:
IfThen([BYStep]) AS Test2


It took me awhile to get to the above where when I ran the query I didn't get a message about the wrong arguments. Now, I don't get an error message, but the field Test2, comes up empty for every record.

Is there a way to do what I'd like to do (having the 40 if/then/elses in a function, and then using that function in 1 field in the query)?

Good Afternoon,

I'm new in SQL Server and Access and have been given the assignment of buildnig a DB in either application. I have more experience in Access and have built tables and constructed the full relationships this morning. The tables range from 5k to 2.2m records.

Are there any benefits to transfer the DB into SQL Server, mostly for reporting, querying, and/or maintaining the DB?

I am doing my own legwork but any other inputs would be welcome.

thanks,

chucky

I am working with a potential client where the original developer moved out of town and now the client needs his application to evolve. The problem is there is no documentation on the application at all. The front-end of the application is MS Access based and the backend is SQL Server 2000 based. What I would like to do is get a copy of the SQL Server data files and install them on my copy of MDSE and then install the front end on my PC as well and create the ODBC links to the data tables. I am new to SQL Server and I am wondering what is the best way to transfer the tables and relationships setup in SQL Server on the potential client's server to my PC in my office?

Thanks in advance!

The column in question is a varbinary(max) in SQL Server 2008 R2 Express, migrated over from the original MS Access 2000 database. When I do an ODBC link from SQL Server tables to MS Access 2000, the linked table shows that the column is a binary (with 512 length) in the MS Access design view. I want it to be an OLE Object, so that it shows on reports as a signature. This is what it does in a similar database setup, and shows on reports as a signature, so I can't figure out what I did different.

Hello For linking access database to sql server, linking from access to tables in sql server is enough or we have to modify other objects like queries or forms. Because Before I had spilited my access database and it was working. Now I have linked that database to sql server, it will be hanged in some cases.

I have a query that works in SQL Server. However when I try to put it in a scheduled job using the BCP function I cannot get it to work. I want the output of my query to be written to an excel file (.csv file is fine).

SET QUOTED_IDENTIFIER OFF
SELECT EMP.EMPCODE, EMP.LASTNAME, EMP.FIRSTNAME, EMP.CRAFT, EMP.CREW, EMPWAGE.WAGECODE, EMPWAGE.RATE
FROM EMP INNER JOIN EMPWAGE ON EMP.EMPCODE = EMPWAGE.EMPCODE
GROUP BY EMP.EMPCODE, EMP.LASTNAME, EMP.FIRSTNAME, EMP.CRAFT, EMP.CREW, EMPWAGE.WAGECODE, EMPWAGE.RATE, EMPWAGE.PRIMARYWAGE
HAVING (((EMPWAGE.PRIMARYWAGE)="Y"))
ORDER BY EMP.LASTNAME"

I have a problem.
I need to design a simple SSIS package, wherein I use an input excel source and an output SQL server table. All I need to do is to import data from excel to SQl server table by using a check condition on date column in excel sheet.

In the package, I have an excel source wherein I use a sql statement to pull data from excel sheet. I have a date column in the excel sheet so I want to use the statement
Select * from sheet1 ( excel spreadsheet)
where date>(select max(date_val) from [sql server name].[table name])

so I need to only import data which is greater than the max date in the sql server table.

can this be done?

right now, I use a variable and type in the date value.. is there a way to automate the process?
Please help..
I am new to SSIS and finding examples online is hard and i am stuck...
Thanks


Not finding an answer? Try a Google search.