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!

losing some results

i have created a report that fits the layout to achieve the fields that i
require, i then created an aspx page where my users can select any number of
fields and values to use in the where clause of the sql statement. My aspx
page then builds an sql statement based on these selections and passes this
sql statement to the report as a parameter. The report calls a stored
procedure that executes the sql statement passed in. This works great for
all but one situation that i have found. When a user enters '%bel%' to use
in the where clause for some reason when it gets to reporting services
report the sql statement is modified to 'l%'. Dropping the '%be'. Is
'%be' a reserved command.
example:
if my table had the following entries in a column name city Boston,
Belville,New York,Detroit,Los Angeles, Lakeville
my user wants to find all cities that have 'bel' in the name
the resulting sql would be select city from table where city like '%bel%'
i setup up my report to show the parameters when the aspx page redirects to
the report using the url of the report
the sql that shows up in the parameter field is select city from table where
city like 'l%'
Any help would be appreciated.
Thank youSolved my own problem. what i had to do was replace all my '%' to '%25' to
encode my url before i issued a response.redirect.
"Mike" <mike.no.spam.please@.no.spam.com> wrote in message
news:u2LsSw6tEHA.1596@.TK2MSFTNGP10.phx.gbl...
>i have created a report that fits the layout to achieve the fields that i
>require, i then created an aspx page where my users can select any number
>of fields and values to use in the where clause of the sql statement. My
>aspx page then builds an sql statement based on these selections and passes
>this sql statement to the report as a parameter. The report calls a stored
>procedure that executes the sql statement passed in. This works great for
>all but one situation that i have found. When a user enters '%bel%' to use
>in the where clause for some reason when it gets to reporting services
>report the sql statement is modified to 'l%'. Dropping the '%be'. Is
>'%be' a reserved command.
> example:
> if my table had the following entries in a column name city Boston,
> Belville,New York,Detroit,Los Angeles, Lakeville
> my user wants to find all cities that have 'bel' in the name
> the resulting sql would be select city from table where city like '%bel%'
>
> i setup up my report to show the parameters when the aspx page redirects
> to the report using the url of the report
> the sql that shows up in the parameter field is select city from table
> where city like 'l%'
> Any help would be appreciated.
> Thank you
>

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 error redirection problem

Hi,

Maybe someone can assist me in solving the following error.
I created a facttable with surrogate keys. In the package that processes the fact data a lookup-task is supposed to find the appropriate dimension key.
If a certain dimension row is not found, this error is redirected.
In this errorflow a stored procedure will add the missing row in the dimensiontable, after which another lookup takes place. Then the results of both lookups are brought together in a union all task.

When I test this errorflow I notice that the missing dimension row is indeed added to the dimensiontable. The only task that turns red is the first lookup. Please refer below for the errormessages.
I understand the first one; that is why I created the redirection. But what does the rest mean? And even if I redirect the error for further processing is it still counted as a raised error? If the maximum allowed is 1 (as indicated) will the task still fail?

Any help will be greatly appreciated. Thanks in advance and regards,

Albert.

The errormessages are as follows:
Error: 0xC020901E at Xforms, lookups en wegschrijven, Lookup LosplaatsKey [5071]: Row yielded no match during lookup.
Error: 0xC0047072 at Xforms, lookups en wegschrijven, DTS.Pipeline: No object exists with the ID 5317.
Error: 0xC0047022 at Xforms, lookups en wegschrijven, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Lookup nwe losplaats" (5315) failed with error code 0xC0047072. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Xforms, lookups en wegschrijven, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread1" has exited with error code 0xC0047072. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047039 at Xforms, lookups en wegschrijven, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread2" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Error: 0xC0047039 at Xforms, lookups en wegschrijven, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread3" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Error: 0xC0047021 at Xforms, lookups en wegschrijven, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread2" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047021 at Xforms, lookups en wegschrijven, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread3" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
.
.
.
Task failed: Xforms, lookups en wegschrijven
Warning: 0x80019002 at FactRittenInit: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (8) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "FactRittenInit.dtsx" finished: Failure.

It looks like it is not set to redirect. The error "Row yielded no match during lookup." hints this.
if you are certain the row is in the dim, is the case the same? Lookups are case sensitive.

Try deleting the lookup and re-adding it?|||

Hi Crispin,

Thanks for your reply. What I described is not yet a production situation, but a test in development.

The testcase is such that indeed one lookup should not yield a match. This row is then redirected and added by means of a stored procedure. After this a new lookup is performed and then the result is unioned with the first flow.

| Error redirect

V

Lookup Key --> OLE DB Command (proc adds row)

| |

V V

Union all <-- Lookup new key

|

V

The behaviour is that 'Lookup Key' notices that one dimension row is missing (which is true) and turns red. The lookup is redirected as expected, the 'OLE DB Command' adds the missing row in the dimension table and the 'Lookup new key' and 'Union all' succeed.

Nevertheless then the process stops because of the errormessages mentioned before.

Albert

|||I'm more concerned with this error:

Error: 0xC0047072 at Xforms, lookups en wegschrijven, DTS.Pipeline: No object exists with the ID 5317.|||It looks like your second failure is on the second lookup - after the new row is added. Lookups, by default, cache all their data at the beginning of the data flow. So even if you are adding the new rows, the cached values in the second lookup won't have it. To work around this, go to the Advanced tab of the second lookup, and check enable memory restriction, which turns off caching.|||

Hi John,

You are right! Never thought of that. Thanks (you save me a headache).

Albert.

Looking For Technique To Default Execute Permission on any new SPr

Hi,
I'm looking for a way, for when any procedure is created or updated in a
user database, that a particular DB role be granted execute permissions on
the proc.
This role is a: datareader,datawriter, and ddladmin, but can not be
db_owner. I know that I can grant execute en-masse using a variety of T-SQL
techniques, but I would have to do this periodically. Also, adding a GRANT
on the tail-end of the CREATE PROCEDURE would not work for me.
I was beginning to create a trigger on sysobjects for CREATE,UPDATE when the
object type is 'P' (for procedure) to then issue the GRANT, but soon realize
d
that triggers on system tables are not permitted!
Does anyone have any suggestions?
Thanks very much,
SteveTIf you are on SQL Server 2005, you could grant execute
permissions on the schema to the role -
GRANT EXECUTE ON SCHEMA::YourSchema TO YourRole
Otherwise, not much you can do if you can't add a grant
statement to the create procedure script.
-Sue
On Thu, 2 Nov 2006 13:49:02 -0800, SteveT
<SteveT@.discussions.microsoft.com> wrote:

>Hi,
>I'm looking for a way, for when any procedure is created or updated in a
>user database, that a particular DB role be granted execute permissions on
>the proc.
>This role is a: datareader,datawriter, and ddladmin, but can not be
>db_owner. I know that I can grant execute en-masse using a variety of T-SQ
L
>techniques, but I would have to do this periodically. Also, adding a GRANT
>on the tail-end of the CREATE PROCEDURE would not work for me.
>I was beginning to create a trigger on sysobjects for CREATE,UPDATE when th
e
>object type is 'P' (for procedure) to then issue the GRANT, but soon realiz
ed
>that triggers on system tables are not permitted!
>Does anyone have any suggestions?

Looking for how to for SQLexpress

I recently installed SQL express on my winXP machine at home... Installed
and configured IIS as requested. I have created a simple table for testing
but cannot establish a DSN connection using either SQL or SQL Native driver
It mentioned something about allowing remote connections, but that feature
is turned on.. so not sure what else i need to configure to allow me to
connect so i can start testing some things out..
any sites or even books i can take a look at to complete this would be
great.Hi Daniel
"Daniel" wrote:

> I recently installed SQL express on my winXP machine at home... Installed
> and configured IIS as requested. I have created a simple table for testing
> but cannot establish a DSN connection using either SQL or SQL Native drive
r
> It mentioned something about allowing remote connections, but that feature
> is turned on.. so not sure what else i need to configure to allow me to
> connect so i can start testing some things out..
> any sites or even books i can take a look at to complete this would be
> great.
>
A remote connection should only be needed if you are trying to access your
SQL Express instance from another machine. It can be changed by using the th
e
Surface Area Configuration Tool SQLSAC.exe see previous post
http://tinyurl.com/23dum6 for links on this.
John|||I figured as much... when i installed it i took all the defaults and didnt
change anything.. but i will try this later tonight...
thanks for the reply.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:75A3F905-C157-4283-A407-3DA7BA72A7D7@.microsoft.com...
> Hi Daniel
> "Daniel" wrote:
>
> A remote connection should only be needed if you are trying to access your
> SQL Express instance from another machine. It can be changed by using the
> the
> Surface Area Configuration Tool SQLSAC.exe see previous post
> http://tinyurl.com/23dum6 for links on this.
> John

Looking for how to for SQLexpress

I recently installed SQL express on my winXP machine at home... Installed
and configured IIS as requested. I have created a simple table for testing
but cannot establish a DSN connection using either SQL or SQL Native driver
It mentioned something about allowing remote connections, but that feature
is turned on.. so not sure what else i need to configure to allow me to
connect so i can start testing some things out..
any sites or even books i can take a look at to complete this would be
great.Hi Daniel
"Daniel" wrote:
> I recently installed SQL express on my winXP machine at home... Installed
> and configured IIS as requested. I have created a simple table for testing
> but cannot establish a DSN connection using either SQL or SQL Native driver
> It mentioned something about allowing remote connections, but that feature
> is turned on.. so not sure what else i need to configure to allow me to
> connect so i can start testing some things out..
> any sites or even books i can take a look at to complete this would be
> great.
>
A remote connection should only be needed if you are trying to access your
SQL Express instance from another machine. It can be changed by using the the
Surface Area Configuration Tool SQLSAC.exe see previous post
http://tinyurl.com/23dum6 for links on this.
John|||I figured as much... when i installed it i took all the defaults and didnt
change anything.. but i will try this later tonight...
thanks for the reply.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:75A3F905-C157-4283-A407-3DA7BA72A7D7@.microsoft.com...
> Hi Daniel
> "Daniel" wrote:
>> I recently installed SQL express on my winXP machine at home... Installed
>> and configured IIS as requested. I have created a simple table for
>> testing
>> but cannot establish a DSN connection using either SQL or SQL Native
>> driver
>> It mentioned something about allowing remote connections, but that
>> feature
>> is turned on.. so not sure what else i need to configure to allow me to
>> connect so i can start testing some things out..
>> any sites or even books i can take a look at to complete this would be
>> great.
> A remote connection should only be needed if you are trying to access your
> SQL Express instance from another machine. It can be changed by using the
> the
> Surface Area Configuration Tool SQLSAC.exe see previous post
> http://tinyurl.com/23dum6 for links on this.
> John

Looking for how to for SQLexpress

I recently installed SQL express on my winXP machine at home... Installed
and configured IIS as requested. I have created a simple table for testing
but cannot establish a DSN connection using either SQL or SQL Native driver
It mentioned something about allowing remote connections, but that feature
is turned on.. so not sure what else i need to configure to allow me to
connect so i can start testing some things out..
any sites or even books i can take a look at to complete this would be
great.
Hi Daniel
"Daniel" wrote:

> I recently installed SQL express on my winXP machine at home... Installed
> and configured IIS as requested. I have created a simple table for testing
> but cannot establish a DSN connection using either SQL or SQL Native driver
> It mentioned something about allowing remote connections, but that feature
> is turned on.. so not sure what else i need to configure to allow me to
> connect so i can start testing some things out..
> any sites or even books i can take a look at to complete this would be
> great.
>
A remote connection should only be needed if you are trying to access your
SQL Express instance from another machine. It can be changed by using the the
Surface Area Configuration Tool SQLSAC.exe see previous post
http://tinyurl.com/23dum6 for links on this.
John
|||I figured as much... when i installed it i took all the defaults and didnt
change anything.. but i will try this later tonight...
thanks for the reply.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:75A3F905-C157-4283-A407-3DA7BA72A7D7@.microsoft.com...
> Hi Daniel
> "Daniel" wrote:
> A remote connection should only be needed if you are trying to access your
> SQL Express instance from another machine. It can be changed by using the
> the
> Surface Area Configuration Tool SQLSAC.exe see previous post
> http://tinyurl.com/23dum6 for links on this.
> John