I'VE 3 QUESTIONS

FIRST, If I make a view like this:
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="D:\";Extended properties=DBase III')...dav
So If it is called from Client, Does Data to be read from client's D:\ or
server's D:\?
If Data is still read from server, how can data be read from client?
Second, At Ms Access, I ever make query like this.
SELECT noid, FIRST(Fddate) AS fdate
from TB1
GROUP BY noid
I wanna make like it in SQL Server 2000. Can I do it?
Third, I've data like it
field1 field2
--
a1 3
a1 4
a1 23
b1 35
b1 30
b1 31
I wanna delete records, but first record of group (field1) is not deleted.
How syntax SQL to do it?> So If it is called from Client, Does Data to be read from client's D:\ or
> server's D:\?

> If Data is still read from server, how can data be read from client?
Its read from the server, if you want to read it from the client you have
to put the data on a network share that the server can reach and open it.

> SELECT noid, FIRST(Fddate) AS fdate
> from TB1
> GROUP BY noid
With no background information thatll be just a guess to, but you can use
semething like MIN()

> How syntax SQL to do it?
Delete
From SomeTable ST
Where field2 NOT IN
(Select TOP 1 field2 From sometable Where ST2.field1 = ST.field1 order by
field2)
HTH, Jens Suessmeyer.
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:eH9MZy2jFHA.3448@.TK2MSFTNGP12.phx.gbl...
> FIRST, If I make a view like this:
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="D:\";Extended properties=DBase III')...dav
> So If it is called from Client, Does Data to be read from client's D:\ or
> server's D:\?
> If Data is still read from server, how can data be read from client?
> Second, At Ms Access, I ever make query like this.
> SELECT noid, FIRST(Fddate) AS fdate
> from TB1
> GROUP BY noid
> I wanna make like it in SQL Server 2000. Can I do it?
> Third, I've data like it
> field1 field2
> --
> a1 3
> a1 4
> a1 23
> b1 35
> b1 30
> b1 31
> I wanna delete records, but first record of group (field1) is not deleted.
> How syntax SQL to do it?
>|||> FIRST, If I make a view like this:
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="D:\";Extended properties=DBase III')...dav
> So If it is called from Client, Does Data to be read from client's D:\ or
> server's D:\?
> If Data is still read from server, how can data be read from client?
It is read from the server. To read it from the client, try using the UNC
name of the shared folder in the "data source".

> Second, At Ms Access, I ever make query like this.
> SELECT noid, FIRST(Fddate) AS fdate
> from TB1
> GROUP BY noid
> I wanna make like it in SQL Server 2000. Can I do it?
Use MIN or MAX aggregate functions.

> Third, I've data like it
> field1 field2
> --
> a1 3
> a1 4
> a1 23
> b1 35
> b1 30
> b1 31
> I wanna delete records, but first record of group (field1) is not deleted.
> How syntax SQL to do it?
delete t1
where exists(select * from t1 as a where a.field1 = t1.field1 and a.field2 <
t1.field2)
--or
delete t1
where field2 > (select min(a.field2) from t1 as a where a.field1 = t1.field1
)
AMB
"Bpk. Adi Wira Kusuma" wrote:

> FIRST, If I make a view like this:
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="D:\";Extended properties=DBase III')...dav
> So If it is called from Client, Does Data to be read from client's D:\ or
> server's D:\?
> If Data is still read from server, how can data be read from client?
> Second, At Ms Access, I ever make query like this.
> SELECT noid, FIRST(Fddate) AS fdate
> from TB1
> GROUP BY noid
> I wanna make like it in SQL Server 2000. Can I do it?
> Third, I've data like it
> field1 field2
> --
> a1 3
> a1 4
> a1 23
> b1 35
> b1 30
> b1 31
> I wanna delete records, but first record of group (field1) is not deleted.
> How syntax SQL to do it?
>
>

It's possible to call a view from a different SQL Server?

dear friends,

It's possible to call a view from a different SQL Server? I need to create a connection inside one view from the first server to call the second server...

Thanks

Create a linked server then use a fully qualified object name: SELECT * FROM Server2.database.dbo.view|||

I did it and the point 1. works, but in point 2. with Inner join, doesnt work:

1. (OK)

SELECT * FROM GCXNCLISM1S301.SMS_B02.dbo._CPY_OfferManager_1005

2. (NOT OK)

SELECT dbo.v_R_System.Netbios_Name0, dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, dbo.v_GS_COMPUTER_SYSTEM.Model0,

dbo.v_GS_PC_BIOS.SerialNumber0, dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0, dbo.v_R_System.User_Name0

FROM GCXNCLISM1S301.SMS_B02.dbo.v_GS_COMPUTER_SYSTEM INNER JOIN

GCXNCLISM1S301.SMS_B02.dbo.v_R_System ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID INNER JOIN

GCXNCLISM1S301.SMS_B02.dbo.v_GS_X86_PC_MEMORY ON dbo.v_R_System.ResourceID = dbo.v_GS_X86_PC_MEMORY.ResourceID INNER JOIN

GCXNCLISM1S301.SMS_B02.dbo.v_GS_PC_BIOS ON dbo.v_R_System.ResourceID = dbo.v_GS_PC_BIOS.ResourceID

Error:

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_COMPUTER_SYSTEM.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_R_System.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_R_System.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_X86_PC_MEMORY.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_R_System.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_PC_BIOS.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_R_System.Netbios_Name0" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_COMPUTER_SYSTEM.Model0" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_PC_BIOS.SerialNumber0" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_R_System.User_Name0" could not be bound.

THE SINTAX IS DIFFERENT?

THANKS!!

|||

hi,

did you try this :

SELECT T2.Netbios_Name0, T1.Manufacturer0, T1.Model0,

T4.SerialNumber0, T3.TotalPhysicalMemory0, T2.User_Name0

FROM [GCXNCLISM1S301.SMS_B02].[dbo].[v_GS_COMPUTER_SYSTEM] T1 INNER JOIN

[GCXNCLISM1S301.SMS_B02].[dbo].[v_R_System] T2 ON T1.ResourceID = T2.ResourceID INNER JOIN

[GCXNCLISM1S301.SMS_B02].[dbo].[v_GS_X86_PC_MEMORY] T3 ON T2.ResourceID = T3.ResourceID INNER JOIN

[GCXNCLISM1S301.SMS_B02].[dbo].[v_GS_PC_BIOS] T4 ON T2.ResourceID = T4.ResourceID

...

|||

Dear Stephane,

Returns the folow error:

Msg 208, Level 16, State 1, Procedure TESTE2, Line 3

Invalid object name 'GCXNCLISM1S301.SMS_B02.dbo.v_GS_COMPUTER_SYSTEM'.

(1 row(s) affected)

Thanks

|||

OK,

I called the view directly to the remote server and it works. And I have other problem when I do a Inner Join with one table in my seever:

ALTER PROCEDURE [dbo].[TESTE4]

AS

SELECT Netbios_Name0 as CN,TotalPhysicalMemory0 as RAM, Model0=

CASE

WHEN TotalPhysicalMemory0 <600000 THEN Model0 + ' 512MB'

WHEN TotalPhysicalMemory0 >600000 THEN Model0 + ' 1GB'

END, SerialNumber0 as NrSerie,

TotalPhysicalMemory0 as RAM, User_Name0 as UserID, MA_MODELO_ID, MA_Nome

FROM GCXNCLISM1S301.SMS_B02.dbo.v_gestao_desktop_balcoes, ModeloPC_ALIAS

WHERE Model0 NOT LIKE '%ProLiant%'

AND Model0 =MA_Nome

Returns the error:

Msg 468, Level 16, State 9, Procedure TESTE4, Line 4

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

IF I substitute the last line of code for this:

AND Model0 COLLATE Latin1_General_CI_AS =MA_Nome COLLATE Latin1_General_CI_AS

It works, but the query dont return rows... And I know that there is values that join the both sources... :-(

Can anyone help me with COLLATE?

THANKS!!

iterate through a view?

Hello everybody,

obviously, I'm a newbie, so please forgive me for using the wrong terms accidently.

I have created a view that looks like this:

ID Disc Cut
------
1 11 25
1 5 34
2 1 67
3 8 54
4 5 14
4 1 12
...

ID is a bigint, Disc & Cut ar varchars

As you can see, the ID is not the primary key.
Still, what I need is only the record of the first occurance of each ID,
like this:

ID Disc Cut
------
1 11 25
2 1 67
3 8 54
4 5 14
...

Is there a way that I can iterate through the view and compare one record to
the previous or next?
(I didn't find anything in the Microsoft Library yet...)

Thanks!

NinaHave you tried SELECT DISTINCT ID FROM ...|||Originally posted by grahamt
Have you tried SELECT DISTINCT ID FROM ...

That wouldn't work because these rows:

1 11 25
1 5 34

are not distinct but I need only one of them.|||There is no such thing as "First" in SQL server unless you specify a sort order. You obviously have your data sorted by ID, but after that SQL Server make no guarantee as to the order in which your data will be returned.

It could easily be returned like this on some subsequent execution:

ID Disc Cut
------
1 11 25
1 5 34
2 1 67
3 8 54
4 1 12
4 5 14

You will need to specify a secondary sort order in order to select between duplicate ID values.|||Maybe something like:

USE Northwind
GO

SELECT * FROM Orders l
INNER JOIN (SELECT OrderId, MAX(OrderDate) AS OrderDate
FROM Orders
GROUP BY OrderId) AS r
ON l.OrderId = r.OrderId
AND l.OrderDate = r.OrderDate|||...that'll work if he can specify a secondary sort order.

ist of Databases: do not use master.dbo.sysdatabases...

Is there a SP or view to list databases for a server (2000 & 2005)? SQL 2005
has a view for sysdatabases, but they say it will not be available after thi
s
version.
We have a SP that lists the security for Logins and Databases. It uses
master.dbo.sysdatabases to loop through the databases. We need to modify thi
s
so it is not using the master database tables.
Thanks and God Bless,
ThomBeauxThomBeaux,
The SQL 2005 sys.databases view does not exists on SQL 2000. However,
sp_helpdb works on both releases of SQL Server and is not on the deprecation
list.
If you need the results of sp_helpdb in a table for your processing, do:
CREATE TABLE #db
(name sysname,
db_size nvarchar(13),
owner sysname,
dbid smallint,
created datetime,
status nvarchar (600),
compatibility_level tinyint)
INSERT INTO #db EXEC sp_helpdb
SELECT * FROM #db
Then do what you need with the results.
RLF
"ThomBeaux" <ThomBeaux@.NoSpam.Com> wrote in message
news:7B6B13C5-C01B-43C6-A370-14B191A939D2@.microsoft.com...
> Is there a SP or view to list databases for a server (2000 & 2005)? SQL
> 2005
> has a view for sysdatabases, but they say it will not be available after
> this
> version.
> We have a SP that lists the security for Logins and Databases. It uses
> master.dbo.sysdatabases to loop through the databases. We need to modify
> this
> so it is not using the master database tables.
> --
> Thanks and God Bless,
> ThomBeaux|||Thanks...
--
Thanks and God Bless,
ThomBeaux
"Russell Fields" wrote:

> ThomBeaux,
> The SQL 2005 sys.databases view does not exists on SQL 2000. However,
> sp_helpdb works on both releases of SQL Server and is not on the deprecati
on
> list.
> If you need the results of sp_helpdb in a table for your processing, do:
> CREATE TABLE #db
> (name sysname,
> db_size nvarchar(13),
> owner sysname,
> dbid smallint,
> created datetime,
> status nvarchar (600),
> compatibility_level tinyint)
> INSERT INTO #db EXEC sp_helpdb
> SELECT * FROM #db
> Then do what you need with the results.
> RLF
> "ThomBeaux" <ThomBeaux@.NoSpam.Com> wrote in message
> news:7B6B13C5-C01B-43C6-A370-14B191A939D2@.microsoft.com...
>
>

Issue with identity column in a view

Thanks a lot, Greg!

Well, I have another question.

In SQL-2005, till SP2 version, there is a bug, dialed with treating an identity column in a view as an ordinary int column, when the compatibility verison of the DB is set to 80: http://support.microsoft.com/kb/920974

In SQL Server 2005 Express Edition CTP (since version 9.00.3027), which is available to download from Microsoft site, this bug was fixed.

Please tell if you know, how can I get an evaluation copy of any other edition of this version, where merge publication feature could be tested?

Creating a new thread since your question is a new topic.

This issue is fixed in the latest SQL Server 2005 SP2 CTP, you can access it and read about the fixes here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1055809&SiteID=1.

Issue restoring database

I'm running into a problem restoring a database to MSDE.
Actually, osql reports RESTORE DATABASE successfully
worked but when I view the database from enterprise
manager it tells me the database is still "loading..."
Any suggestions?
Sounds like you are using NORECOVERY with the RESTORE command. This will
leave the database expecting additional restores.
Jim
"Tim" <tim.huenke@.ngc.com> wrote in message
news:0f9c01c4dee6$ebe38f60$a401280a@.phx.gbl...
> I'm running into a problem restoring a database to MSDE.
> Actually, osql reports RESTORE DATABASE successfully
> worked but when I view the database from enterprise
> manager it tells me the database is still "loading..."
> Any suggestions?

Issue Resolved - Interaction between SQL Server and Access

I'm implementing row-level security in a SQL Server database that uses Microsoft Access for the front end. I'm using a UDF (a view behaves the same way) to restrict access to specific rows of a base table based on membership in a role. According to the reading I've done, if the base table has DENY ALL permissions for the role, and the UDF has GRANT ALL, members of the role should be able to update records in the base table via the UDF, without having direct access to the base table. However, I find that unless I grant appropriate permissions on the base table, the user is unable to update the table via the UDF.

Is this expected behavior? Nothing I've read suggests I should have to grant permissions on the columns of the base table.

Yes, that is expected behavior.

Permissions in SQL Server have three values: GRANT, DENY, or 'unsaid'.

If you have been GRANTed permission for a table, obviously you have permission.

If your permission is 'unsaid', then you 'may' still have permission due to permission having been granted to another role that includes you.

But IF you have been explicited DENY(ied), that 'trumps' all.

Think of it this way.

Children have a knack of knowing how to 'scope out' their parents. Perhaps a son wants to go out with friends. He may approach Mom and 'feel her out' to find out if she 'might' say yes WITHOUT directly asking her. He knows that if he asks her and she says 'No', his plans are shot because he cannot then go and ask Dad (DENY). So he will attempt to find out if it is 'safe' to ask her. If it seems safe, he will ask and he's 'home free' (GRANT).

However, if he feels that she would probably say No, then without having asked, he is now free to ask Dad. So Mom was 'unsaid', if permission can be had by another route, it will work for him.

So anytime permission is an explicit DENY, there is no route around it.

Often, in a strong SQL Server security model, TABLE permissions are left 'unsaid', and access is GRANTed through VIEWS, Functions, and Stored Procedures. Users are also added to the db_DenyDataReader and db_DenyDataWriter roles to prohibit them having direct table access.

|||

Thanks for responding.

Ah, yes, that's what I thought. But if I leave the permissions on the base table "unsaid", and grant all on the UDF, Access tells me that the recordset is not updatable (maybe because it can't "see" the PK column?). So I'm back to having to grant permissions in the base table, which is unacceptable.

I read through the good whitepaper on row-level security by Rask, Rubin and Neumann. The architecture they propose is great, but overkill for what I need to do. Nevertheless, I set up a test using their methodology: DENY ALL on the base table, GRANT ALL on a view, and put an INSTEAD OF trigger on the view to verify appropriate access and perform an update. But because Access thinks the recordset isn't updatable, the trigger never fires. If I grant SELECT permissions to just the PK column of the base table, Access thinks the recordset is updatable, but I still can't get the trigger to fire because Access wants at least SELECT permissions on the other base table columns before it will even try to perform the update.

|||

I would suggest the following topics from BOL:

· CREATE VIEW (http://msdn2.microsoft.com/en-us/library/ms187956.aspx), got to the section Updatable Views

· Modifying Data Through a View (http://msdn2.microsoft.com/en-us/library/ms180800.aspx)

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

In addition to Raul's suggestions, I offer the following insight.

Access will allow you to UPDATE or DELETE a row without the table having a primary key (or some unique identifier.)

SQL Server does NOT allow UPDATES or DELETES unless there is a unambiguous way to be certain what row is being addressed. If the VIEW does not include a PK, or unique identifier, it would not be updatable.

|||Thanks for the references, I'll read through them and see where they lead. I note that these are from SQL Server 2005 BOL, and the server that must host the application I'm working with is SQL Server 2000 SP4. I'm just wondering whether you know whether the support for updatable views changed between SQL 2000 and 2005?|||

As far as I understand, updatable views should be supported in SQL Server 2000 SP4, but I am not 100% sure if all the documentation in the links I included may apply to SQL Server 2000 SP4 as well.

I would recommend trying to find the same topic in BOL fro SQL Server 2000 and giving it a try; if you have any further questions please let us know, we will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks to both you and Arnie for responding to this inquiry.

The problem turns out to have been an interaction between SQL Server and Access. In order for Access to update a SQL Server view, the view must be declared the WITH VIEW_METADATA option. If designing the view in Access, this is accomplished by checking the view option "Update using view rules" on the properties page. Once I did this, I was able to DENY ALL on the base table and GRANT ALL on the view, and the view was updatable via Access with appropriate security.

I ran a SQL Profiler trace to see what Access was sending to SQL Server when the option was properly set--it showed that the UPDATE statement Access generated was against the view, not the base tables. Without using the VIEW_METADATA option, Access does not consider the recordset updatable (hence my original question), so it doesn't generate an UPDATE statement. So I couldn't run a trace to see what happens in that case. I expect that if I could, the UPDATE would be going against the base table rather than the view.

Again, thanks for your help.

Issue On Views

I have a view which has a date field. However, this date field is in the form of a number: 20041401. There are also places where the date is 0 (zero). I want to get this date to look like 1/14/04. I did this by creating a view on the first view, and used case to set the 0 (zero) values to null, and then I created another view on the last one to convert the not null values to a date using cast. Is there a simpler way to do what I want? Pretty soon I will lose track of all my views.
Thanks.Maybe you should cast or convert the datetime column the right way in your first view.|||I can't because the date string is in the form of a number. When I execute the view, and it comes across a 0 (zero) I get an error. Because cast can't convert a number to char.|||Not sure I follow..but...

DECLARE @.x int
SELECT @.x = 20041401
SELECT CONVERT(varchar(10),
CONVERT(datetime,SUBSTRING(CONVERT(char(8),@.x),7,2 )
+ '/' + SUBSTRING(CONVERT(char(8),@.x),5,2)
+ '/' + SUBSTRING(CONVERT(char(8),@.x),1,4)),1)|||Originally posted by exdter
I can't because the date string is in the form of a number. When I execute the view, and it comes across a 0 (zero) I get an error. Because cast can't convert a number to char.

Really?

SELECT CONVERT(varchar(10),0)|||That's what I need. Thanks.|||I strongly agree with the notion that, if you're dealing with date-type information that hasn't been stored as a Date field (and which, e.g. for application compatibility reasons, can't be converted now), it is highly desirable that you should use a base view that Casts the value to a date; then base other views on that.|||And doen't that make it an non-updateable view?|||Thanks.