analysis services 2000 error "cannot access registry on server(servername) or you are not a

I recently had to reinstall AS2000. Since then , if i dont run the service under the local account i get a "could not access registry on (localserver) or analysis you are not part of Olap administrators group on this server" error.

I am in the sql server admins group, and the olap admins group, as is the service account I want to run AS2000 under. I have granted admin access explicitly to the Hikey_local_machine\ software\microsoft\olap server registry entries, to the service account and my account.

I have installed AS2000 SP3a and rebooted the machine. I am still stuck with the error. When i check the "actual permissions" of my account, and the service account to run the AS service under, they have full admin permission to the machine.

If i run it under "local System Account" it works, but i need to run it under the service account.

All google tells me is to install sp 3a, which i have done twice, to no avail.

Has anyone solved this bug, or do they know the reason for it?

Hello! If you have AS2000 on a separate server and the administration console on another workstation you can check that they have the same service pack installed.

I also know, from experience, that this problem problem can appear if the pasword for the service account have extended its time limit.

Any firewalls between workstations and the server?

HTH

Thomas Ivarsson

|||hi. thanks for replying. Im working exclusively off the server, not connecting from another pc. no firewalls in use in the domain either.

Analysis Services

How do you grant just read access to the cube data to an end user?

Thanks, Edwina Derrick

Are you talking about a writeback cube? In that case just disable it in the role the user belongs to.

|||Nope - just read for any dimension. I have tried all combinations and end up giving all access to the cube for them to be able to read the data.

Thanks, Edwina Derrick|||

You are aware of the security features with roles and such in AS?

|||I am not sure I understand what you mean by security features with roles and such in AS.
I set up a role and gave them read access to the dimensions. This was not enough. I gave them read access to everything and they still couldn't view the data. Once I granted full administrator rights to the cube they could read the data.

Thanks, Edwina Derricksql

analysis service

Hi

I was doing steps on page 15 / 16 of attached sheet

I was doing the microsoft example about cube to setup DSN SOURCE CONNECTION to ACCESS database (food mart) when i do a test it works

but after i do design storage and go to Process the cube
it gives me error

'test connection failed because of an error in initializing provide
(microsoft) ODBC DRIVER manager data source name not found and no default driver specified

one more error found
Microsoft ODBC DRIVER MANAGER driver sql connect attr failed: IMOO6

ThanksOriginally posted by sjumma
Hi

I was doing steps on page 15 / 16 of attached sheet

I was doing the microsoft example about cube to setup DSN SOURCE CONNECTION to ACCESS database (food mart) when i do a test it works

but after i do design storage and go to Process the cube
it gives me error

'test connection failed because of an error in initializing provide
(microsoft) ODBC DRIVER manager data source name not found and no default driver specified

one more error found
Microsoft ODBC DRIVER MANAGER driver sql connect attr failed: IMOO6

Thanks

Ensure you have applied SP3. I'm pretty sure there is an MDAC upgrade.|||Hi

I have already upgraded with the patch but still does not work

analysis serices, cubes and access

How to get data from cubes in analyses manager to MS access using for
example ODBC ? Does existe any other posibilities to get data from cubes
directly to the accees ?
thanks
KrisHow did you get them in cube format to begin with? If you mean transfer
the data from the underlying warehouse in SQL to Access you can use DTS.
The example that ships with AS (foodmart) is actually in Access, so you
can build cubes from data within Access. If this doesn't answer your
question, please elaborate.

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!sql

Analysis Manager Remote Access

Hi,

I juste begin using Analysis Manager.

I want to know if i can create, process and manage cubes in a distant server.

I have the connection to the database on a distant server and i want to use Analysis Manager (installed on the server) from my PC (which have Anaylisi Manager Too).

I have just build a cube on my Analysis Manager with the server database but the cube does not appears on the Analysis Manager of the distant server.

Please help me, ;)

Thanks

moustachuAnalysis Services is in its second revision from Microsoft. It has a little while to go before it matures.

You can remotely administer an Analysis Services machine, but you need to have all of the datasources available on the remote machine with access to the data. Apparently the MMC application needs to get all metadata/dimension data for itself, rather than rely on the Analysis Services service to serve it up (ok, all together 5 times fast).

Good luck with it. I am hoping that AS makes many changes in the next revision.|||I thank you for your answer,

All the datasources are on the remote machine. But I do not Know How I can remotly administer an Analysis Services machine ?

moustachu

Originally posted by MCrowley
Analysis Services is in its second revision from Microsoft. It has a little while to go before it matures.

You can remotely administer an Analysis Services machine, but you need to have all of the datasources available on the remote machine with access to the data. Apparently the MMC application needs to get all metadata/dimension data for itself, rather than rely on the Analysis Services service to serve it up (ok, all together 5 times fast).

Good luck with it. I am hoping that AS makes many changes in the next revision.sql

An workaround (was The Answer (was Re: UDA and SQL Data Access))

Is it possible for a user-defined aggregate to perform basic DML operations through ADO.NET (read, update, insert, delete)?

Why would I want to do that, you might ask? Rather than carry forward an accumulation of data, what I want to do is insert the data into a temporary table and retrieve it at the Terminate method call.

I created an aggregate to do the above using VS2005. My aggregate compiles, and deploys through VS2005, but I get the following error when I attempt to run it in debugger:

Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

All functions in UDA must be compatible with User Defined Functions. But UDFs couldn't consist update/insert/delete DML. So I couldn't use them in context connection. But you could create different connection and run update statements through it.

|||

I tried a couple of different options, but it appears that the user-defined aggregates are prohibited from having any kind of connection via SQLCLR in the database.

I have not been able to find any documentation to support or refute this claim, but it appears that UDAs are severely limited in SQL Server 2005.

|||

I finally found a reference that specifies an answer to the question of whether user-defined aggregates can perform database access or not. The answer is "No".

I quote from the Microsoft Whitepaper, Using CLR Integration in SQL Server 2005 (Rathakrishnan, et al.):

A "UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used.UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used."

Thus UDAs have some significant limitations in this version of SQL Server. They are:

No data access|||

vb_hal,

I have been dealing with the same problems as you. In my case, I wanted to create an UDA for calculating the percentile of a set of numbers (as in the PERCENTILE function in Excel).

What I wished for was to be able to write something like

SELECT dbo.PERCENTILE(some_column, 0.5)

FROM some_table

GROUP BY some_other_column

To work arround the multiple arguments problem, I created an UDT called PercentileParameters and an UDF called PP that acts as a sort of constructor. As a result, the query now looks like this:

SELECT dbo.PERCENTILE( dbo.PP(some_column, 0.5) )

FROM some_table

GROUP BY some_other_column

I also had some trouble with the 8000 bytes limit. To work arround it, I gave my assembly EXTERNAL_ACCESS rights and used them to store data as needed. I know it's not very efficient, and that it's impossible in some settings due to security issues, but it works for me.

So there you go. I just thought I'd share these couple ideas with people facing the same problems as I am (and are looking for a quick and dirty way out of it, just as I was).

--

Carlos

|||

I've been trying to find exactly what you seem to have. I've been looking for a function in SQL Server that does the same thing as PERCENTILE in Excel. Oracle has an implementation called PERCENTILE_CONT and I've seen ways to do the calculations in SQL Server 2005 but not as a function. Is there any way you could share your code with me?

From the documentation I've read on UDAs, I'd have to create an assembly in a .Net language to create my own aggregate. Quite a daunting task from my perspective since my background is strictly SQL Server code and Admin. I could get around the multivalued function issue because I use 5 static percentile values (.1,.25,.5,.75,.9). I could just create 5 UDAs.

Any feedback is greatly appreciated.

blackjackIT

|||

Jourdan, can you shaer your dbo.Percentile and dbo.PP function if possible. I'm trying to do the same UDA for percentile as you.

Thanks!

An workaround (was The Answer (was Re: UDA and SQL Data Access))

Is it possible for a user-defined aggregate to perform basic DML operations through ADO.NET (read, update, insert, delete)?

Why would I want to do that, you might ask? Rather than carry forward an accumulation of data, what I want to do is insert the data into a temporary table and retrieve it at the Terminate method call.

I created an aggregate to do the above using VS2005. My aggregate compiles, and deploys through VS2005, but I get the following error when I attempt to run it in debugger:

Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

All functions in UDA must be compatible with User Defined Functions. But UDFs couldn't consist update/insert/delete DML. So I couldn't use them in context connection. But you could create different connection and run update statements through it.

|||

I tried a couple of different options, but it appears that the user-defined aggregates are prohibited from having any kind of connection via SQLCLR in the database.

I have not been able to find any documentation to support or refute this claim, but it appears that UDAs are severely limited in SQL Server 2005.

|||

I finally found a reference that specifies an answer to the question of whether user-defined aggregates can perform database access or not. The answer is "No".

I quote from the Microsoft Whitepaper, Using CLR Integration in SQL Server 2005 (Rathakrishnan, et al.):

A "UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used.UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used."

Thus UDAs have some significant limitations in this version of SQL Server. They are:

No data access|||

vb_hal,

I have been dealing with the same problems as you. In my case, I wanted to create an UDA for calculating the percentile of a set of numbers (as in the PERCENTILE function in Excel).

What I wished for was to be able to write something like

SELECT dbo.PERCENTILE(some_column, 0.5)

FROM some_table

GROUP BY some_other_column

To work arround the multiple arguments problem, I created an UDT called PercentileParameters and an UDF called PP that acts as a sort of constructor. As a result, the query now looks like this:

SELECT dbo.PERCENTILE( dbo.PP(some_column, 0.5) )

FROM some_table

GROUP BY some_other_column

I also had some trouble with the 8000 bytes limit. To work arround it, I gave my assembly EXTERNAL_ACCESS rights and used them to store data as needed. I know it's not very efficient, and that it's impossible in some settings due to security issues, but it works for me.

So there you go. I just thought I'd share these couple ideas with people facing the same problems as I am (and are looking for a quick and dirty way out of it, just as I was).

--

Carlos

|||

I've been trying to find exactly what you seem to have. I've been looking for a function in SQL Server that does the same thing as PERCENTILE in Excel. Oracle has an implementation called PERCENTILE_CONT and I've seen ways to do the calculations in SQL Server 2005 but not as a function. Is there any way you could share your code with me?

From the documentation I've read on UDAs, I'd have to create an assembly in a .Net language to create my own aggregate. Quite a daunting task from my perspective since my background is strictly SQL Server code and Admin. I could get around the multivalued function issue because I use 5 static percentile values (.1,.25,.5,.75,.9). I could just create 5 UDAs.

Any feedback is greatly appreciated.

blackjackIT

|||

Jourdan, can you shaer your dbo.Percentile and dbo.PP function if possible. I'm trying to do the same UDA for percentile as you.

Thanks!

An workaround (was The Answer (was Re: UDA and SQL Data Access))

Is it possible for a user-defined aggregate to perform basic DML operations through ADO.NET (read, update, insert, delete)?

Why would I want to do that, you might ask? Rather than carry forward an accumulation of data, what I want to do is insert the data into a temporary table and retrieve it at the Terminate method call.

I created an aggregate to do the above using VS2005. My aggregate compiles, and deploys through VS2005, but I get the following error when I attempt to run it in debugger:

Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

All functions in UDA must be compatible with User Defined Functions. But UDFs couldn't consist update/insert/delete DML. So I couldn't use them in context connection. But you could create different connection and run update statements through it.

|||

I tried a couple of different options, but it appears that the user-defined aggregates are prohibited from having any kind of connection via SQLCLR in the database.

I have not been able to find any documentation to support or refute this claim, but it appears that UDAs are severely limited in SQL Server 2005.

|||

I finally found a reference that specifies an answer to the question of whether user-defined aggregates can perform database access or not. The answer is "No".

I quote from the Microsoft Whitepaper, Using CLR Integration in SQL Server 2005 (Rathakrishnan, et al.):

A "UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used.UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used."

Thus UDAs have some significant limitations in this version of SQL Server. They are:

No data access|||

vb_hal,

I have been dealing with the same problems as you. In my case, I wanted to create an UDA for calculating the percentile of a set of numbers (as in the PERCENTILE function in Excel).

What I wished for was to be able to write something like

SELECT dbo.PERCENTILE(some_column, 0.5)

FROM some_table

GROUP BY some_other_column

To work arround the multiple arguments problem, I created an UDT called PercentileParameters and an UDF called PP that acts as a sort of constructor. As a result, the query now looks like this:

SELECT dbo.PERCENTILE( dbo.PP(some_column, 0.5) )

FROM some_table

GROUP BY some_other_column

I also had some trouble with the 8000 bytes limit. To work arround it, I gave my assembly EXTERNAL_ACCESS rights and used them to store data as needed. I know it's not very efficient, and that it's impossible in some settings due to security issues, but it works for me.

So there you go. I just thought I'd share these couple ideas with people facing the same problems as I am (and are looking for a quick and dirty way out of it, just as I was).

--

Carlos

|||

I've been trying to find exactly what you seem to have. I've been looking for a function in SQL Server that does the same thing as PERCENTILE in Excel. Oracle has an implementation called PERCENTILE_CONT and I've seen ways to do the calculations in SQL Server 2005 but not as a function. Is there any way you could share your code with me?

From the documentation I've read on UDAs, I'd have to create an assembly in a .Net language to create my own aggregate. Quite a daunting task from my perspective since my background is strictly SQL Server code and Admin. I could get around the multivalued function issue because I use 5 static percentile values (.1,.25,.5,.75,.9). I could just create 5 UDAs.

Any feedback is greatly appreciated.

blackjackIT

|||

Jourdan, can you shaer your dbo.Percentile and dbo.PP function if possible. I'm trying to do the same UDA for percentile as you.

Thanks!

An error occurred while trying to access the database information. The msdb database could not b

I'am doing functionality test on DTS packages and saving my DTS packages to meta data services instead of saving them as local packages. We would like to see what information would be provided by saving them this way, but when we try to open the meta data browser (the 3rd icon under DTS) we get the following error:

An error occurred while trying to access the database information. The msdb database could not be opened.

You might want to try asking this in the DTS group: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

An error has occurred during report processing.

Hi all,

When i try to access the report Manager through the URL I get this error:

An error has occurred during report processing.

Cannot create a connection to data source 'rs2005sbsDW'.

The ConnectionString property has not been initialized.

Colud someone help me out.

Could some one out there help me out?

Ronald

An error has occurred during report processing.

Hi all,

When i try to access the report Manager through the URL I get this error:

An error has occurred during report processing.

Cannot create a connection to data source 'rs2005sbsDW'.

The ConnectionString property has not been initialized.

Colud someone help me out.

Could some one out there help me out?

Ronald

An array in an object class

In my object class I have an array

Public myArray() as String

I pass this object class as the datasource to reportviewer

How do I access the array from the report? (I'm using reportviewer in visual studio 2005)

Thanks!

I wanted to add that the array is visible from within the report designer, but I can't seem to access items within the array.. I've tried:

Fields!myArray(0).Value, and it just gives an error.. It seems to work with parameters, but not with the data fields... HELP!

An Access OnFormat procedure would be replaced in SQL Reporting Services by what?

In the field, the operators are presented with 5 categories, each category having between 3 and 5 possible selections, for example:

Weather - clear(1), cloudy(2), rain(4) or snow(8).

If the operator selected cloudy and rain, the value of Weather would be 6.

I want to print a report in the office that will list the categories and the available selections for each, putting an "X" next to those selections that were checked by the operators.

In Access, it was easy - I plugged a decode procedure into the OnFormat event...

cnum = obj![WEATHR]
If cnum - 8 >= 0 Then
obj![SNOW] = "X"
cnum = cnum - 8
End If
If cnum - 4 >= 0 Then
obj![RAIN] = "X"
cnum = cnum - 4
End If
If cnum - 2 >= 0 Then
obj![CLOUDY] = "X"
cnum = cnum - 2
End If
If cnum - 1 >= 0 Then
obj![CLEAR] = "X"
End If

Making the change to SQL Reporting Services, I'm clueless...as you can tell, I'm not even sure how to ask the question.

Any help would be greatly appreciated.

There are probably a number of other ways to do this, this is just the first that came to mind.

Add the following on the Code tab of your project (select Report..Report Properties):

Public Function DecodeWeather(ByVal Weather As Integer, ByVal WeatherType As String) As String
DIM cnum AS Integer = Weather
If cnum - 8 >= 0 Then
If WeatherType = "SNOW" Then
Return "X"
Else
cnum = cnum - 8
End If
End If
If cnum - 4 >= 0 Then
If WeatherType = "RAIN" Then
Return "X"
Else
cnum = cnum - 4
End If
End If
If cnum - 2 >= 0 Then
If WeatherType = "CLOUDY" Then
Return "X"
Else
cnum = cnum - 2
End If
End If
If cnum - 1 >= 0 Then
If WeatherType = "CLEAR" Then
Return "X"
End If
End If

End Function

Then add a calculated field to your dataset (on the Layout tab, right click the dataset and choose add) named Snow and add the following to the expression:

=Code.DecodeWeather(Fields!ID.Value, "SNOW")

Repeat for the other fields, passing the appropriate name to the function.

Hope this helps.

|||

Excellent answer!

Thank you.

An Access OnFormat procedure would be replaced in SQL Reporting Services by what?

In the field, the operators are presented with 5 categories, each category having between 3 and 5 possible selections, for example:

Weather - clear(1), cloudy(2), rain(4) or snow(8).

If the operator selected cloudy and rain, the value of Weather would be 6.

I want to print a report in the office that will list the categories and the available selections for each, putting an "X" next to those selections that were checked by the operators.

In Access, it was easy - I plugged a decode procedure into the OnFormat event...

cnum = obj![WEATHR]
If cnum - 8 >= 0 Then
obj![SNOW] = "X"
cnum = cnum - 8
End If
If cnum - 4 >= 0 Then
obj![RAIN] = "X"
cnum = cnum - 4
End If
If cnum - 2 >= 0 Then
obj![CLOUDY] = "X"
cnum = cnum - 2
End If
If cnum - 1 >= 0 Then
obj![CLEAR] = "X"
End If

Making the change to SQL Reporting Services, I'm clueless...as you can tell, I'm not even sure how to ask the question.

Any help would be greatly appreciated.

There are probably a number of other ways to do this, this is just the first that came to mind.

Add the following on the Code tab of your project (select Report..Report Properties):

Public Function DecodeWeather(ByVal Weather As Integer, ByVal WeatherType As String) As String
DIM cnum AS Integer = Weather
If cnum - 8 >= 0 Then
If WeatherType = "SNOW" Then
Return "X"
Else
cnum = cnum - 8
End If
End If
If cnum - 4 >= 0 Then
If WeatherType = "RAIN" Then
Return "X"
Else
cnum = cnum - 4
End If
End If
If cnum - 2 >= 0 Then
If WeatherType = "CLOUDY" Then
Return "X"
Else
cnum = cnum - 2
End If
End If
If cnum - 1 >= 0 Then
If WeatherType = "CLEAR" Then
Return "X"
End If
End If

End Function

Then add a calculated field to your dataset (on the Layout tab, right click the dataset and choose add) named Snow and add the following to the expression:

=Code.DecodeWeather(Fields!ID.Value, "SNOW")

Repeat for the other fields, passing the appropriate name to the function.

Hope this helps.

|||

Excellent answer!

Thank you.

Ammending Data in Field

Hi

New to crystal reports, but familiar with access.

I have been asked to ammend a label that is generated using Crystal 8.5.

Couple of questions:

(1) I cannot see any of the fields for the report, even when I lasso the entire report all I can see is the outline for each box - how can I change this?

(2) For one particular field I have been asked to add a barcode to this. I have the barcode font, and I have copied the exisiting field and changed the font. However, I need to add an '*' to the beginning and end of the exisitng component number - how can I do this?

(3) In the component field, I need to be able to delete any hyphens or spaces in the number (this is for the same field in (2)).

So for example if the component number is:

095-12-6567 7

I need to be able to convert it to:

*0951265677*

Please note that the space or hyphen may not always be present...

Thanks in advance...1 Give more details
2 Create a formula having the code
"*" & {field} & "*" and place it in the details section
(3) Create formula having the code
replace({field},"-","")|||Hi

Thanks for the help - that works like a charm :)

WRT item 1.

In design view everything is invisible, I cannot see text boxs or anything similar. When I lasso everything I see a blue dashed outline showing where it is. I then click at random, somewhere close to the box that I wish to edit/move until I hit the correct one.

When I populate the report with actual data everything (even empty fields) becomes visible|||Did you check this in Preview mode?

Amending a stored procedure

Hi

I have a stored procedure that I have deployed with a database to an sqlserver. To access the database and the stored procedure I am now using SQL Server Management Studio Express. When I was amending the stored procedure locally, I simply "opened" it, made the amendments and then "saved" it. With S.M.S.E. if you click on "modify" a SQLQuery opens up and then after making the amendments you can save the SQLQuery. How do you go from here to effect that change to the stored procedure resident on the sqlserver.

Chris

I've worked out that all I have to do is run the query after amending the stored procedure detail.

Chris

|||

When you click the Modify button, it should show you the stored procedure and it will have an ALTER PROCEDURE command at the top. This just tells the database that you want to overwrite the existing stored procedure with whatever text follows. So, if you click the Execute button (it should have an exclamation mark next to it) the command should execute against the database and your stored procedure will be updated.

|||

Mark

Thanks for your reply

Chris

Alternatives to ODBC in Access 2000

Hello All,

Have a bit of a problem that I hope someone will be able to clarify. I have an Access 2000 mdb that connects to two SQL Servers by way of ODBC. Now due to several failures with OBDC (Would get ODBC errors when the connection to SQL was proven not to be down), my boss wants me to eliminate ODBC from the equation and directly connect to the SQL Servers. Now he has suggested I use SQLOLEDB as an alternative, but thus far I seem to only be able to implement this on the VB side of it, which would mean I'd have to send all my SELECTS UPDATES DELETES etc manually, which would turn a minor update into a complete overhaul. I know there is a way to link tables directly with SQL in Access 2002, is there a similar option in 2000, and how would I go about it?

Thanks!Did a little research and it seems possible to use ADP files with SQLOLEDB, but I don't know how or even what ADP files are.

Alternative to horizontal partitioning?

Hi,
In my replication environment I am using merge replication to get access to
our vendors to our orders.
They should not be able to see the price information of the orders. So, I
want to do something like horizontal partitioning.
I realize I could just leave the columns out of the article; but doing that
would be hard for two reasons:
1. I want to keep using the same publication for vendors as for our own
locations. If we leave out some columns we would need to create the exact
same publication with the columns for our own locations that use
replication.
2. Our program would be impacted. Our views depend on the fields. Our UDFs
depend on the fields, etc.
So, my ideal solution would be telling replication to include the column for
'schema' porpouses, but to not synchronize any data contained in it (so
effectively leaving the field NULL in the vendors databases).
While keeping the same database structure (and making life easier) we would
not be giving our vendors sensitive information.
I think there is no way to accomplish that in MSSQL2000 Merge replication.
Any ideas? workaround? (I am starting to consider messing with the
replication triggers myself - but that doesn't seem like a really good idea,
unless I modify the templates SQL uses to generate them).
Thanks for the comments, Jos Araujo.
Can you try to modify the stored procedures that is used to insert or update
the record in your vendors database?
Just a thought.
Perayu
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:OtWgqqU6FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Hi,
> In my replication environment I am using merge replication to get access
> to
> our vendors to our orders.
> They should not be able to see the price information of the orders. So, I
> want to do something like horizontal partitioning.
> I realize I could just leave the columns out of the article; but doing
> that
> would be hard for two reasons:
> 1. I want to keep using the same publication for vendors as for our own
> locations. If we leave out some columns we would need to create the exact
> same publication with the columns for our own locations that use
> replication.
> 2. Our program would be impacted. Our views depend on the fields. Our UDFs
> depend on the fields, etc.
> So, my ideal solution would be telling replication to include the column
> for
> 'schema' porpouses, but to not synchronize any data contained in it (so
> effectively leaving the field NULL in the vendors databases).
> While keeping the same database structure (and making life easier) we
> would
> not be giving our vendors sensitive information.
> I think there is no way to accomplish that in MSSQL2000 Merge replication.
> Any ideas? workaround? (I am starting to consider messing with the
> replication triggers myself - but that doesn't seem like a really good
> idea,
> unless I modify the templates SQL uses to generate them).
> Thanks for the comments, Jos Araujo.
>

alternative for last()

Hi,
Does somebody knows an alternative for the function last() in access. I've a
lot queries which uses last, but there is no alternative in sql server.
I used min/max but that did not gave me the right results. I need to know
the last row in a 1-to-many table situation.
Is there something like a hidden rowid i can use?Jason
See MAX(),MIN() funtions in the BOL.
\
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:OXIlbqZGFHA.3648@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Does somebody knows an alternative for the function last() in access. I've
a
> lot queries which uses last, but there is no alternative in sql server.
> I used min/max but that did not gave me the right results. I need to know
> the last row in a 1-to-many table situation.
> Is there something like a hidden rowid i can use?
>|||Select top 1 * from table order by field desc
Madhivanan|||There is NO exact equivalent of the LAST function of Access in SQL server.
This is based on the relational concept that the rows in a table are not
ordered.
So anything like LAST, FIRST, NEXT etc are totally meaningless.
If you have a column that records data in any chronological order, you can
achieve the same by using the MAX function.

> Is there something like a hidden rowid I can use?
No, there is nothing like that exposed.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:OXIlbqZGFHA.3648@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Does somebody knows an alternative for the function last() in access. I've
> a
> lot queries which uses last, but there is no alternative in sql server.
> I used min/max but that did not gave me the right results. I need to know
> the last row in a 1-to-many table situation.
> Is there something like a hidden rowid i can use?
>|||HI Mad,
How is sytax if i want it to join with another table (the 1 side of
1-to-many)?
<madhivanan2001@.gmail.com> wrote in message
news:1109158964.898453.171390@.f14g2000cwb.googlegroups.com...
> Select top 1 * from table order by field desc
> Madhivanan
>|||If you have the concept of last, then there is some ordering.... If for
example you want the last price ( with the ordering being the title_id),
then sort the rows in the reverse order, so the last row becomes the first
row returned, and select the top 1... ie
select top 1 price from titles order by title_id desc
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:OXIlbqZGFHA.3648@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Does somebody knows an alternative for the function last() in access. I've
> a
> lot queries which uses last, but there is no alternative in sql server.
> I used min/max but that did not gave me the right results. I need to know
> the last row in a 1-to-many table situation.
> Is there something like a hidden rowid i can use?
>|||Please post DDL and sample data so that we don't have to guess what
your table structure looks like:
http://www.aspfaq.com/etiquette.asp?id=5006
You'll aslo have to tell us what you mean by the "last row" there is no
fixed concept of first and last in a table because tables in SQL have
no inherent logical order.
David Portas
SQL Server MVP
--