Loss of Decimals Upon Link to Access

Hi. I have an Access DB that's linked to a SQL DB view. The SQL view is base
d
on a table which has some data types as float. I created a view on the table
.
The view shows me units of a product divided by units of all products. The
results are expressed in the view as decimals. So, for example, .499857. Thi
s
is what I want. However, when I link the view to Access, all of my decimals
become zero. For example, .499857 becomes 0. I'm completely confounded. Any
suggestions would be fantastic! Thanks!If the SQL view is correct and you can use Query Analyzer to
view the results and they are as expected then this is more
of any MS Access issue. Make sure you have the latest Jet
service pack installed on the client.
But this is more of an Access issue so you would want to try
posting in one of the Access newsgroups. When posting your
question, be sure to include versions (version of SQL
Server, version of Access), what service packs you are
using.
-Sue
On Wed, 19 Apr 2006 08:11:02 -0700, Mike C
<MikeC@.discussions.microsoft.com> wrote:

>Hi. I have an Access DB that's linked to a SQL DB view. The SQL view is bas
ed
>on a table which has some data types as float. I created a view on the tabl
e.
>The view shows me units of a product divided by units of all products. The
>results are expressed in the view as decimals. So, for example, .499857. Th
is
>is what I want. However, when I link the view to Access, all of my decimals
>become zero. For example, .499857 becomes 0. I'm completely confounded. Any
>suggestions would be fantastic! Thanks!

Loose ODBC conection

I am very new to all this

I have a access databse wich uses a ODBC conection to mysql server. I
can open the and run the database find but if i leave the dadabase
loaded but do not use it when i go back to it is has lost the
conection and i need to close the databse and reopen it

If there a way to make sure i alwasy have a coenction

Thanks(S.Dickson@.shos.co.uk) writes:

Quote:

Originally Posted by

I am very new to all this
>
I have a access databse wich uses a ODBC conection to mysql server. I
can open the and run the database find but if i leave the dadabase
loaded but do not use it when i go back to it is has lost the
conection and i need to close the databse and reopen it
>
If there a way to make sure i alwasy have a coenction

That seems like an Access problem to me. Maybe you should ask in an
Access newsgroup.

Do you use MySQL or is that just a typo? This news group is for
MS SQL Server, and not MySQL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Looping database queries

Hi, newbie here:

I have created a small (5 fields) Access table which I will be
converting to MS-SQL Server format when the ASP code has been
completed. It is basically a collection of links to news releases from
mining companies. The group of people who will be browsing this
database want to know if the news release pertains to their area.
Sometimes the news release pertains to multiple areas if the mining
properties are scattered. Given the possibility of a one-to-many
relationship, ie one news release, many areas, I created an additional
table for the areas. I created the ASP code to pull down the news
release information, then loop through the area records such as:

set RSNewsRelease = Server.CreateObject("ADODB.Recordset")
NewsRelSQL = "Select date, company, title, newsreleaseID from
newsreleases;"

do while not RSNewsRelease.EOF
'display news release date, company and title
response.write RSNewsRelease(0).Value & RSNewsRelease(1).Value &
RSNewsRelease(2).Value

'loop through areas
set RSAreas = Server.CreateObject("ADODB.Recordset")
'run query
do while not RSAreas.EOF
'display areas
Loop
set RSAreas = nothing
Loop

In other words, the only way I could get the results I wanted was to
set the Recordset to nothing, then reset it with each iteration of the
outer loop.

Is there a better way to do this?

JulesJules (julian.rickards@.ndm.gov.on.ca) writes:
> I have created a small (5 fields) Access table which I will be
> converting to MS-SQL Server format when the ASP code has been
> completed. It is basically a collection of links to news releases from
> mining companies. The group of people who will be browsing this
> database want to know if the news release pertains to their area.
> Sometimes the news release pertains to multiple areas if the mining
> properties are scattered. Given the possibility of a one-to-many
> relationship, ie one news release, many areas, I created an additional
> table for the areas. I created the ASP code to pull down the news
> release information, then loop through the area records such as:

It would probably be more effecient to bring up all information in
in one query:

SELECT nr.date, nr.company, nr.title, a.area
FROM newsreleases nr
JOIN areas a ON nr.newslreaseid = a.newsrleaseid
ORDER BY nr.date, nr.company, nr.title

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:

> It would probably be more effecient to bring up all information in
> in one query:
>
> SELECT nr.date, nr.company, nr.title, a.area
> FROM newsreleases nr
> JOIN areas a ON nr.newslreaseid = a.newsrleaseid
> ORDER BY nr.date, nr.company, nr.title

I basically understand your code - nr and a are aliases. The only
problem I have with your example is that, as I understand it, if a
newsrelease pertains to 3 areas, then this SQL code will result in
three "entries" in the recordset array such as (simplified):

June IBM New President Toronto
June IBM New President Cleveland
June IBM New President New York

If this is correct, I then have to find a way to cycle through the
identical recordsets (identical except for the area field). OK, just a
sec, I could add the newsrelease id to the SELECT statement and then
do a:

do while "id is the same"
response.write location
recordset.movenext
loop

I won't be back at work until Monday so I will have to wait till then
to try this out.

Thanks,

Jules|||Jules (julian@.jrickards.ca) writes:
> I basically understand your code - nr and a are aliases. The only
> problem I have with your example is that, as I understand it, if a
> newsrelease pertains to 3 areas, then this SQL code will result in
> three "entries" in the recordset array such as (simplified):
> June IBM New President Toronto
> June IBM New President Cleveland
> June IBM New President New York

Yes, this is what you would receive.

> If this is correct, I then have to find a way to cycle through the
> identical recordsets (identical except for the area field). OK, just a
> sec, I could add the newsrelease id to the SELECT statement and then
> do a:
> do while "id is the same"
> response.write location
> recordset.movenext
> loop

Yes, doing that sort of logic is not very complicated.

There is something called the Shape Provider in ADO, so that you can
bring up two related recordsets in one query. ADO is not my home ground,
and I've only read about shape, so I'm not providing any example.
And for many purposes a non-normalized recordset like this one is
the simplest way to go.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Lookup fields in SQL Server

I am new to using SQL Server and need some help creating lookup fields if it is possible.

I use the lookup wizard to create lookup fields in access. Can you do the same in SQL?

I have successfully created relationships in SQL but I want to lookup the related fields during data entry like in Access with a dropdown box with the available options.

You would have to write the code to populate the box, but it works the same in access. In fact, you can use access as a front-end to SQL Server tables. Create the tables in SQL Server, open an access project, and connect to the database, linking the tables in to access. Then build everything in Access just like you always have.

You can also use any other application, like Visual Basic or Web pages to create the same thing, but you'll need to learn one of those languages to do so.

Buck Woody

Lookup fields in SQL Server

I am new to using SQL Server and need some help creating lookup fields if it is possible.

I use the lookup wizard to create lookup fields in access. Can you do the same in SQL?

I have successfully created relationships in SQL but I want to lookup the related fields during data entry like in Access with a dropdown box with the available options.

You would have to write the code to populate the box, but it works the same in access. In fact, you can use access as a front-end to SQL Server tables. Create the tables in SQL Server, open an access project, and connect to the database, linking the tables in to access. Then build everything in Access just like you always have.

You can also use any other application, like Visual Basic or Web pages to create the same thing, but you'll need to learn one of those languages to do so.

Buck Woody

Looking for 'upsizing' from Access/PivotCharts

Hi All!

I'm doing analysis of system performance metrics recorded from a web application performance test. I have approximately 15 tables, each with unique system and test metrics, ranging in size from 6 thousand to 6 million rows. E.g:

iostats: time, host, disk, read_bps, write_bps, ...
netstats: time, host, active_conns, xmit_errs, resets, ...
teststats: time, test_name, virtual_users, ...
timerstats: time, script_name, timer_name, elapsed_time, ...

Last year i used MS Access + pivot tables to do the analysis of this information, but Access isn't cutting it this year. It frequently goes out to lunch and never returns whenever I try to build the charts i'm looking for (most of which use PivotChart views)

I've spent a few days trying to get my head around using SSAS for this purpose, but have run into a few stumbling blocks

1 - The information in the 15 tables is loosely correlated on time of day, some have one sample per second, others one sample every thirty seconds, and still others are whenever an update decides to roll in. I'm having a very difficult time figuring out how to make a usable 'time' dimension that i can use to chart the information.

2 - I seem to constantly run into 'missing attribute value' errors when trying to build and deploy my analysis services projects that contain cubes. Frequently this seems to be due to cases where i don't have specific information on a specific dimension (e.g. host / cpu / etc) for a given time period.

3 - I'm not sure how to visualize some of the output from the cube interactions that do work. I can get a table working in the cube browser, but how do i chart that? Is the Excel AS plugin the only approach available? I tried to view the tables via access linked tables, but a) Access (2002) complains it can't modify tables against SQL 2005, and b) Access doesn't pull the entire set of data back, just the first 50k records or so.

4 - If i want to calculate disk utilization on a per disk per host basis, would that be a valid use of a dimension hierarchy?

I'm kind of running out of time. My game plan for today is to recalculate all tables to exactly one minute sample intervals and try again, hoping that the reduced row count will make things a little more smooth.

Thanks for any advice!!!

Bob

Several ideas for you:

Try to see if Office 2007 beta is going to work for you. Install Office2007 beta and try if you can fit your data sizes there.

Second. If you are using Analysis Services Excel add-in is not the only choice. You can use Excel Pivot Tables to connect to Analysis Services. You can use many other client applications to diplay Analysis Services data.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Hi Edward!

Thanks for the response. I may take a look at office 2007 today to see how it goes.

Looking for opinions.....want to use SQL Server to store images

I have a client who wants to be able to upload images to his website for his customers to access. It will probably max out at 100 images a month...so not a huge amount of data. I am using asp.net 2.0 and SQL Server 2005.

Does anyone have thoughts or opinions on why I should or should not take this approach?

You could store images on disk or you could store images in SQL server.
It would seem that SQL would require more overhead than Disk so...

Does SQL offer you something that you require that disk storage does not?

If it does, then use it. Pulling images from a db is not an uncommon practice. The small performance drop could be mitigated by caching the images (in-memory for small images or disk for large images).

If nothing you are doing actually requires SQL server though, i would stick to disk.

|||I would use SQL Server. It simplifies quite a bit, and you don't have to give users write permissions any where on the web server making it more secure.|||

I am already using sql server for other reasons so I wouldn't be setting it up specifically for this. Also I would think that sql server would organize the files for you, where they live and whatnot, thus less that I would have to deal with from a programmatic standpoint, not to mention the directory permissions as mentioned above. The images that will be stored will be a little larger than a business card.

Thanks for your responses..

Looking for input on SQL 2005 and IIS Security

I am just looking for feedback and maybe pointers for research regarding securing SQL 2005 for IIS access. I am currently working on a project for building a new retail website and our sysadm guys have some concerns regarding exposing our SQL box to the DMZ via direct connections to the IIS box. Now we have not completely come to a conclusion of saying that it is not possible, but we are very concerned due to recent credit card and customer data problems in the industry.

So far we have mainly talked about just exposing web services(limiting the exposure of data that can be retrieved) on an internal IIS box that would be called allowed to be accessed from only the IIS box in the DMZ using User Accounts and Client SSL Certs. What I am most concerned about is the performance of this design. I would like to try and provide a connection to the SQL box directly for the devs, but I am not sure what the best practice would be for securing this connection through the firewall and also monitoring it in case our DMZ IIS box gets owned.

Any input or direction to resources would be much appreciated. I have read quite a few papers so far and just want to get feedback on architectures and designs.

Thanks in advance.

Options include: using IPSEC (or SSL) to control the connection between the IIS box and SQL Server (and Developers). Only 'approved' IP addresses would be allowed to connect to SQL Server. That protects SQL Server from being directly attacked, but not from attack from the IIS box.

Using IIS's capability to use Application Pools, you can further restrict access to SQL Server through the Application.

Here are some resources that might be useful:

Web Applications -Connect to SQL Server
Configuring an ASP.NET 2.0 Application to Work with Microsoft SQL Server 2000 or SQL Server 2005
http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=395
How To: Create a Service Account for an ASP.NET 2.0 Application
http://msdn2.microsoft.com/en-us/library/ms998297.aspx
How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0
http://msdn2.microsoft.com/en-us/library/ms998300.aspx

Looking for help with a stored procedure!

Please, please help me !!!!

I have a stored procedure that I need to modify. The stored procedure
is used in an Access DB program where user selects a ProjectID and
views/or can email data for different Units.
I have to make sure that if a projectid is for Fire' Unit report goes
to EVERYONE in a select list and one more user
(v_ddcemployee.employee_id = 2234). If it's not Fire' Unit then
report goes to EVERYONE on select list excluding this specific user.
I have tried case statement and If Else but nothing works I don't
know how to do this

select ProgUnitName from v_progunit where ProgUnitID = 9 <=== will
return Fire'

Here is my Stored procedure:

CREATE PROCEDURE sp_GetFullReportCCList
@.ProjectID varchar (11) AS
SELECT v_progunit.ProgUnitName, tlkpSafetyTitle.SafetyTitleDesc,
v_ddcemployee.[Last_Name] + ', ' + v_ddcemployee.[First_Name] AS Name
FROM v_DDCemployee
INNER JOIN (tlkpSafetyTitle INNER JOIN (tblSafetyPersnl INNER JOIN
v_progunit ON tblSafetyPersnl.ProgUnitID = v_progunit.ProgUnitID) ON
tlkpSafetyTitle.SafetyTitleID = tblSafetyPersnl.SafetyTitleID) ON
v_DDCemployee.employee_id = tblSafetyPersnl.Employee_ID
WHERE tblSafetyPersnl.SafetyTitleID In (5,6,7,8,10,11,13,15,19)
AND v_progunit.Division='S'
OR tblSafetyPersnl.SafetyTitleID In (5,7,11,13,14,15,19,20)
AND v_progunit.Division='I'

UNION

SELECT v_progunit.ProgUnitName, tlkpProjectTitle.ProjectTitleDesc AS
SafetyTitleDesc,
v_ddcemployee.[Last_Name] + ', ' + v_ddcemployee.[First_Name] AS Name
FROM v_progunit
INNER JOIN (((tblProjectTeam INNER JOIN tlkpProjectTitle ON
tblProjectTeam.ProjectTitleID = tlkpProjectTitle.ProjectTitleID) INNER
JOIN v_tblprojectid ON tblProjectTeam.ProjectID =
v_tblprojectid.ProjectID)
INNER JOIN v_DDCemployee ON tblProjectTeam.loginid =
v_DDCemployee.loginID) ON v_progunit.ProgUnitName =
v_tblprojectid.Unit_Name
WHERE v_progunit.Division='S'
AND v_tblprojectid.ProjectID=@.ProjectID
OR v_progunit.Division='I'
AND v_tblprojectid.ProjectID=@.ProjectID
GO

I will be forever grateful to everyone that helps me :)
SonyaTry this, add the employee_ID to the select, make sure you always get
the employee_id = 2234 and use a derived table.

Select DISTINCT ProgUnitName,SafetyTitleDesc,Name
from ( SELECT v_progunit.ProgUnitName,
tlkpSafetyTitle.SafetyTitleDesc,
v_ddcemployee.[Last_Name] + ', ' + v_ddcemployee.[First_Name] AS Name
,v_DDCEmployee.Employee_ID
FROM v_DDCemployee
INNER JOIN (tlkpSafetyTitle INNER JOIN (tblSafetyPersnl INNER JOIN
v_progunit ON tblSafetyPersnl.ProgUnitID = v_progunit.ProgUnitID) ON
tlkpSafetyTitle.SafetyTitleID = tblSafetyPersnl.SafetyTitleID) ON
v_DDCemployee.employee_id = tblSafetyPersnl.Employee_ID
WHERE tblSafetyPersnl.SafetyTitleID In (5,6,7,8,10,11,13,15,19)
AND v_progunit.Division='S'
OR tblSafetyPersnl.SafetyTitleID In (5,7,11,13,14,15,19,20)
AND v_progunit.Division='I'

UNION

SELECT v_progunit.ProgUnitName, tlkpProjectTitle.ProjectTitleDesc AS
SafetyTitleDesc,
v_ddcemployee.[Last_Name] + ', ' + v_ddcemployee.[First_Name] AS Name
,v_DDCEmployee.Employee_ID
FROM v_progunit
INNER JOIN (((tblProjectTeam INNER JOIN tlkpProjectTitle ON
tblProjectTeam.ProjectTitleID = tlkpProjectTitle.ProjectTitleID) INNER
JOIN v_tblprojectid ON tblProjectTeam.ProjectID =
v_tblprojectid.ProjectID)
INNER JOIN v_DDCemployee ON tblProjectTeam.loginid =
v_DDCemployee.loginID) ON v_progunit.ProgUnitName =
v_tblprojectid.Unit_Name
WHERE v_progunit.Division='S'
AND v_tblprojectid.ProjectID=@.ProjectID
OR v_progunit.Division='I'
AND v_tblprojectid.ProjectID=@.ProjectID your union selects here )
where (progUnitName = 'Fire'
UNION
*** select as above where employee_id = 2234, in
other words - always select this employee ***
)
Where ( ProgUnitName = 'Fire') or ( ProgUnitName <> 'Fire' and
employee_id <> 2234)

Looking for good Migration tool which can convert Access db to SQL

I'm looking for migration tool which can convert Access database to SQL
Server. Any one which works 100%. Any suggestions would be greatly
appreciated.
Regards,
RaoRao
Linked Servers?
---
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Rao" <Rao@.discussions.microsoft.com> wrote in message
news:7215F579-1C4C-4F6D-AF5B-6D0D12117772@.microsoft.com...
> I'm looking for migration tool which can convert Access database to SQL
> Server. Any one which works 100%. Any suggestions would be greatly
> appreciated.
> Regards,
> Rao
>|||You can import the database using DTS. Do mean that you want to also carry
over all the queries, etc. and have the SQL properly translated as well?
"Rao" <Rao@.discussions.microsoft.com> wrote in message
news:7215F579-1C4C-4F6D-AF5B-6D0D12117772@.microsoft.com...
> I'm looking for migration tool which can convert Access database to SQL
> Server. Any one which works 100%. Any suggestions would be greatly
> appreciated.
> Regards,
> Rao
>