database question

I have an Access application that links to SQL Server tables via an ODBC link.

I execute the following query in the Access application,

SELECT customerName From customers Where stateCode = 'CA'

My question:

Who (Access, SQL Server or the ODBC provider) selects based on the state code?

Does SQL Server only return customerNames where stateCode = 'CA'
OR
Does SQL Server return ALL rows and let Access select the customerNames where stateCode = 'CA'
OR
Does SQL Server return ALL rows and let the ODBC provider select and return only the appropriate rows to Access?

Thanks for your helpThe cleanest way to determine this is to run the query while SQL Profiler is running, and see what gets passed to SQL Server. My guess is that Access only asks for the rows where stateCode is CA.|||Agree with D.R. However, from what I remember it depends on the ODBC driver. If the remote server (in this case SQL) implements a specific part of the ODBC capabiliies then it will do the query on the server, otherwise the client has to do it. I'm pretty sure SQL will implement that capability.

Database Permission

Could someone please tell me how I can trap permission errors in
vb.net on a stored procedure:
ie. Execute permission denied on object 'sel_mytable', database
'mydatabase', owner 'dbo'

I would like to print a message like
response.write("You don't have permission to select on this table") as
opposed to the cryptic message I am receiving.

Thanks in advance
Julie BarnetYou can trap a SqlException and translate the error as desired in your code.
VB.Net WinForm example:

Try
sqlCommand.ExecuteReader()
Catch ex As SqlException
Dim PermissionError As Boolean = False
For Each sqlError As SqlError In ex.Errors
If sqlError.Number = 229 Then
PermissionError = True
Exit For
End If
Next sqlError
If PermissionError = True Then
MessageBox.Show("You don't have permission to select on this
table")
Else
MessageBox.Show("Unexpected error: " & ex.ToString())
End If
End Try

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Julie Barnet" <barnetj@.pr.fraserpapers.com> wrote in message
news:438e1811.0312081052.d233a1f@.posting.google.co m...
> Could someone please tell me how I can trap permission errors in
> vb.net on a stored procedure:
> ie. Execute permission denied on object 'sel_mytable', database
> 'mydatabase', owner 'dbo'
> I would like to print a message like
> response.write("You don't have permission to select on this table") as
> opposed to the cryptic message I am receiving.
> Thanks in advance
> Julie Barnet|||I am databinding a datagrid. I tried that and it still doesn't seem
to work. I will paste my code...

Is there something I'm missing?

Public Function BindGrid(ProcName as String, myDataGrid as
DataGrid, cmd as SqlCommand, con as SqlConnection) as Integer
Dim ds As DataSet
Dim da As new SqlDataAdapter()

Try

cmd.CommandText = ProcName
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = con

da.Selectcommand = cmd

ds = new DataSet()
da.Fill(ds, "MyTable")

myDataGrid.DataSource=ds.Tables("MyTable").DefaultView
myDataGrid.DataBind()
return ds.Tables("MyTable").Rows.Count

catch exp as SqlException
HttpContext.Current.Response.Write("Exception3")

End Try
End Function|||I ran your code and got the "Exception3" message. What symptoms are you
getting?

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Julie Barnet" <barnetj@.pr.fraserpapers.com> wrote in message
news:438e1811.0312090444.3e7dd4ab@.posting.google.c om...
> I am databinding a datagrid. I tried that and it still doesn't seem
> to work. I will paste my code...
> Is there something I'm missing?
> Public Function BindGrid(ProcName as String, myDataGrid as
> DataGrid, cmd as SqlCommand, con as SqlConnection) as Integer
> Dim ds As DataSet
> Dim da As new SqlDataAdapter()
> Try
> cmd.CommandText = ProcName
> cmd.CommandType = CommandType.StoredProcedure
> cmd.Connection = con
> da.Selectcommand = cmd
> ds = new DataSet()
> da.Fill(ds, "MyTable")
> myDataGrid.DataSource=ds.Tables("MyTable").DefaultView
> myDataGrid.DataBind()
> return ds.Tables("MyTable").Rows.Count
>
> catch exp as SqlException
> HttpContext.Current.Response.Write("Exception3")
> End Try
> End Function

Database Output Problem for a SQL Server 2000 stored procedure

Hi,

I have a problem with "database output" window in executing a select-sp in vs 2005 standard. The db is a sql server 2000 one.

When I execute the sp within VS, the database output correctly displays the execution information:

No rows affected.
(1 row(s) returned)
@.RETURN_VALUE = 3

but I can't see the returned rows (3 rows are returned); I only see the column names and no data.

Running [dbo].[spBkm_GetList] ( @.IDUser = <DEFAULT>, [.......]).
IDBkm UIBkm IDUser
------- ------------ ---No rows affected.

If I run the same sp in Sql Server Manager Studio Express it correctly shows the data of the 3 rows returned.

The sp uses

EXECsp_executesql @.Sql, @.ParamList, .....

for executing the sql statement and the last line of sp is

RETURN@.@.ROWCOUNT

I've tried removing the "RETURN @.@.ROWCOUNT" with no success.

The problem affects only one sp, the others, which are absolutely similar, work properly .

I don't know what is the problem...

Any idea?

Thanks in advance

Ive' resolved the problem in a strange way, it seems to me a bug...

If I exclude from the select statement a field of type uniqueidentifier, alla data are correctly displayed!

I've tried on other sp and the behaviour is the same.

Hope this may help someone else with the same problem...