ODBC Update on a linked table failed

I created a form in Access 2003 who's recordsource is an ODBC linked table from
SQL Server 2000. The form has a subform that lists all the records in that
table. The form also has all the fields from that table as separate controls.
I have a command button to add new records, which works without any
problems. When the user clicks on any record in the subform, I fill in the
controls on the form, and this works fine. But If I try to edit anything in
the record, when it tries to update the record, I get this message , ODBC
Update on a linked table "Table Name" failed - [Microsoft][ODBC SQL Server
Driver]Timeout expired(#0). It also takes 1-2 minutes before this message
appears. If I remove the subform or if I use a table thats not linked for the
recordsource, it works fine. What am I doing wrong?
You probably have some locking or blocking issues based on
how the form and recordsources are designed. You can view
whatever SQL statement are being executed by running a trace
or running Profiler. In terms of optimizing your form
itself, you would probably want to post that on one of the
Access newsgroups. Try one of these:
microsoft.public.access.formscoding
microsoft.public.access.odbcclientsvr
-Sue
On Fri, 17 Nov 2006 11:01:02 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:

>I created a form in Access 2003 who's recordsource is an ODBC linked table from
>SQL Server 2000. The form has a subform that lists all the records in that
>table. The form also has all the fields from that table as separate controls.
>I have a command button to add new records, which works without any
>problems. When the user clicks on any record in the subform, I fill in the
>controls on the form, and this works fine. But If I try to edit anything in
>the record, when it tries to update the record, I get this message , ODBC
>Update on a linked table "Table Name" failed - [Microsoft][ODBC SQL Server
>Driver]Timeout expired(#0). It also takes 1-2 minutes before this message
>appears. If I remove the subform or if I use a table thats not linked for the
>recordsource, it works fine. What am I doing wrong?
|||Sorry, I'm not familiar with these. How do you run a trace or Profiler?
It is definitely a locking issue, because I tried splitting the form into
two separate forms, clearing out the subform list before opening up the new
form for editing, and it works fine. I just don't understand why the subform
that has the record list is locking the record. The subform properties are
RecordsetType=Snapshot, RecordLocks=NoLocks, Query properties are
RecordsetType=Snapshot, RecordLocks=NoLocks.
"Sue Hoegemeier" wrote:

> You probably have some locking or blocking issues based on
> how the form and recordsources are designed. You can view
> whatever SQL statement are being executed by running a trace
> or running Profiler. In terms of optimizing your form
> itself, you would probably want to post that on one of the
> Access newsgroups. Try one of these:
> microsoft.public.access.formscoding
> microsoft.public.access.odbcclientsvr
> -Sue
> On Fri, 17 Nov 2006 11:01:02 -0800, Crossh
> <Crossh@.discussions.microsoft.com> wrote:
>
>
|||The subform design, properties, etc would probably be better addressed
in a Microsoft Access newsgroup.
To run profiler, from the start button go to the SQL Server program
group and you will find profiler. You can find more information on
using the tool in Books Online (the SQL Server help file).
-Sue
On Tue, 21 Nov 2006 07:24:02 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sorry, I'm not familiar with these. How do you run a trace or Profiler?
>It is definitely a locking issue, because I tried splitting the form into
>two separate forms, clearing out the subform list before opening up the new
>form for editing, and it works fine. I just don't understand why the subform
>that has the record list is locking the record. The subform properties are
>RecordsetType=Snapshot, RecordLocks=NoLocks, Query properties are
>RecordsetType=Snapshot, RecordLocks=NoLocks.
>
>"Sue Hoegemeier" wrote:
|||I actually posted it in both newsgroups. I wasn't sure which was causing the
problem, SQL or Access. You were the first to respond. Thanks so much for
your help.
"Sue Hoegemeier" wrote:

> The subform design, properties, etc would probably be better addressed
> in a Microsoft Access newsgroup.
> To run profiler, from the start button go to the SQL Server program
> group and you will find profiler. You can find more information on
> using the tool in Books Online (the SQL Server help file).
> -Sue
> On Tue, 21 Nov 2006 07:24:02 -0800, Crossh
> <Crossh@.discussions.microsoft.com> wrote:
>
>
|||Yeah...it's not as cut and dry as it might seem. It's timing
out due to locking, blocking type of issues in SQL Server
but then again that would be related to how the form and
subform is designed. What you can do is use profiler or even
just execute sp_who2, sp_lock, query sysprocesses when you
hit the issue. As long as it's timing out, you should be
able to capture it with those (but Profiler would be
better). From there, you would want to determine what is
being executed, what part of what action on the form,
subform is leading to the problem. And then from there...you
can look at the design. Most of it will be related to how
the forms are populated, what kind of binding and that type
of thing. I can't remember enough Access off the top of my
head to give you enough direction on how you may want to
rethink the form, subform design.
-Sue
On Tue, 21 Nov 2006 09:19:01 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I actually posted it in both newsgroups. I wasn't sure which was causing the
>problem, SQL or Access. You were the first to respond. Thanks so much for
>your help.
>"Sue Hoegemeier" wrote:

ODBC Update on a linked table failed

I created a form in Access 2003 who's recordsource is an ODBC linked table f
rom
SQL Server 2000. The form has a subform that lists all the records in that
table. The form also has all the fields from that table as separate controls
.
I have a command button to add new records, which works without any
problems. When the user clicks on any record in the subform, I fill in the
controls on the form, and this works fine. But If I try to edit anything in
the record, when it tries to update the record, I get this message , ODBC
Update on a linked table "Table Name" failed - [Microsoft][ODBC SQL
Server
Driver]Timeout expired(#0). It also takes 1-2 minutes before this message
appears. If I remove the subform or if I use a table thats not linked for th
e
recordsource, it works fine. What am I doing wrong?You probably have some locking or blocking issues based on
how the form and recordsources are designed. You can view
whatever SQL statement are being executed by running a trace
or running Profiler. In terms of optimizing your form
itself, you would probably want to post that on one of the
Access newsgroups. Try one of these:
microsoft.public.access.formscoding
microsoft.public.access.odbcclientsvr
-Sue
On Fri, 17 Nov 2006 11:01:02 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:

>I created a form in Access 2003 who's recordsource is an ODBC linked table
from
>SQL Server 2000. The form has a subform that lists all the records in that
>table. The form also has all the fields from that table as separate control
s.
>I have a command button to add new records, which works without any
>problems. When the user clicks on any record in the subform, I fill in the
>controls on the form, and this works fine. But If I try to edit anything in
>the record, when it tries to update the record, I get this message , ODBC
>Update on a linked table "Table Name" failed - [Microsoft][ODBC SQL
Server
>Driver]Timeout expired(#0). It also takes 1-2 minutes before this message
>appears. If I remove the subform or if I use a table thats not linked for t
he
>recordsource, it works fine. What am I doing wrong?|||Sorry, I'm not familiar with these. How do you run a trace or Profiler?
It is definitely a locking issue, because I tried splitting the form into
two separate forms, clearing out the subform list before opening up the new
form for editing, and it works fine. I just don't understand why the subform
that has the record list is locking the record. The subform properties are
RecordsetType=Snapshot, RecordLocks=NoLocks, Query properties are
RecordsetType=Snapshot, RecordLocks=NoLocks.
"Sue Hoegemeier" wrote:

> You probably have some locking or blocking issues based on
> how the form and recordsources are designed. You can view
> whatever SQL statement are being executed by running a trace
> or running Profiler. In terms of optimizing your form
> itself, you would probably want to post that on one of the
> Access newsgroups. Try one of these:
> microsoft.public.access.formscoding
> microsoft.public.access.odbcclientsvr
> -Sue
> On Fri, 17 Nov 2006 11:01:02 -0800, Crossh
> <Crossh@.discussions.microsoft.com> wrote:
>
>|||The subform design, properties, etc would probably be better addressed
in a Microsoft Access newsgroup.
To run profiler, from the start button go to the SQL Server program
group and you will find profiler. You can find more information on
using the tool in Books Online (the SQL Server help file).
-Sue
On Tue, 21 Nov 2006 07:24:02 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sorry, I'm not familiar with these. How do you run a trace or Profiler?
>It is definitely a locking issue, because I tried splitting the form into
>two separate forms, clearing out the subform list before opening up the new
>form for editing, and it works fine. I just don't understand why the subfor
m
>that has the record list is locking the record. The subform properties are
>RecordsetType=Snapshot, RecordLocks=NoLocks, Query properties are
>RecordsetType=Snapshot, RecordLocks=NoLocks.
>
>"Sue Hoegemeier" wrote:
>|||I actually posted it in both newsgroups. I wasn't sure which was causing the
problem, SQL or Access. You were the first to respond. Thanks so much for
your help.
"Sue Hoegemeier" wrote:

> The subform design, properties, etc would probably be better addressed
> in a Microsoft Access newsgroup.
> To run profiler, from the start button go to the SQL Server program
> group and you will find profiler. You can find more information on
> using the tool in Books Online (the SQL Server help file).
> -Sue
> On Tue, 21 Nov 2006 07:24:02 -0800, Crossh
> <Crossh@.discussions.microsoft.com> wrote:
>
>|||Yeah...it's not as cut and dry as it might seem. It's timing
out due to locking, blocking type of issues in SQL Server
but then again that would be related to how the form and
subform is designed. What you can do is use profiler or even
just execute sp_who2, sp_lock, query sysprocesses when you
hit the issue. As long as it's timing out, you should be
able to capture it with those (but Profiler would be
better). From there, you would want to determine what is
being executed, what part of what action on the form,
subform is leading to the problem. And then from there...you
can look at the design. Most of it will be related to how
the forms are populated, what kind of binding and that type
of thing. I can't remember enough Access off the top of my
head to give you enough direction on how you may want to
rethink the form, subform design.
-Sue
On Tue, 21 Nov 2006 09:19:01 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I actually posted it in both newsgroups. I wasn't sure which was causing th
e
>problem, SQL or Access. You were the first to respond. Thanks so much for
>your help.
>"Sue Hoegemeier" wrote:
>

ODBC to linked server

I have created a linked server using an OLE DB service in a SQL Server 2000
database running on Windows NT 4.
I can see all the tables and views of the linked server in Enterprise
manager, but when I go to a client machine and try to setup a DSN to the
linked server using an SQL Server driver all I can see for a default
database is the example Microsoft databases (Northwind, Pub, etc.). How do I
get the ODBC driver to use the linked server as a default database?
Any help would be greatly appreciated.ODBC cannot "see" linked servers. You can issue queries using the fully qua
lified server.db.dbo.tablename to be able to select from the table using a p
XXX-through query

ODBC Select Data Source in Crystal reports - continuously prompts

I've created an ODBC entry for crystal reports. When I go to refresh and
run a report, it always prompts me with a select data source and SQL server
is at the bottom of the list under dBase, Excel and MS Access Database. If
I select it, the report runs. How do I have it automatically choose that
source so I don't have to select it each time?
Thank you.You should ask this question in a Crystal support newsgroup or site.
This one is for SQL Server, so your pickings will be slim.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Fri, 26 Dec 2003 13:41:50 -0500, "microsoft"
<advice1one@.hotmail.com> wrote:

quote:

---

>I've created an ODBC entry for crystal reports. When I go to refresh and
>run a report, it always prompts me with a select data source and SQL server
>is at the bottom of the list under dBase, Excel and MS Access Database. If
>I select it, the report runs. How do I have it automatically choose that
>source so I don't have to select it each time?
>Thank you.
>

---

|||Thank you, I figured it out, I needed to change the ODBC connector to named
pipes.
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:do51vv0gshsu7e2g9mr6bl0i9sinf4bq4t@.
4ax.com...

quote:

---

> You should ask this question in a Crystal support newsgroup or site.
> This one is for SQL Server, so your pickings will be slim.
> -- Mary
> MCW Technologies
> http://www.mcwtech.com
> On Fri, 26 Dec 2003 13:41:50 -0500, "microsoft"
> <advice1one@.hotmail.com> wrote:
>
server[QUOTE]
If[QUOTE]
>

---

ODBC readonly (viewonly) linked tables....

Hi,

I have a prod database (main bread & bread DB) and have a small access
database that sales team is using... I created a login inside SQL 2000
and gave db_read permission and SELECT permissions... and public.
Role of public is EXEC store procs and view some systables...
I linked those tables that I gave permissions through ODBC link into
access db with the user that I created inside SQL as readonly user...
but to my surprise when I ran a insert query from access on SQL
tables... I was able to update and insert data... if my ODBC link is
only for readonly.. why was I able to unpdate & insert data into SQL
table?
I don't want to give write/update/insert permissions for SQL tables to
sales team... or anyone outside SQL 2000 server DB.
Is there any easy way to create viewonly users inside SQL?
I created users like this: security -logins -new login...
select none serverrole.. db access (bread & butter DB)

Thank you,
hj

******
Pardon my English...Hitesh Joshi (hitesh287@.gmail.com) writes:

Quote:

Originally Posted by

I have a prod database (main bread & bread DB) and have a small access
database that sales team is using... I created a login inside SQL 2000
and gave db_read permission and SELECT permissions... and public.
Role of public is EXEC store procs and view some systables...
I linked those tables that I gave permissions through ODBC link into
access db with the user that I created inside SQL as readonly user...
but to my surprise when I ran a insert query from access on SQL
tables... I was able to update and insert data... if my ODBC link is
only for readonly.. why was I able to unpdate & insert data into SQL
table?

Exactly how does the connection information for the ODBC link look like?
(Minus any password of course).

Exactly how did you set up this user as a readonly user?

--
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

ODBC Program & Heterogeneous queries problem

Best All,

What I am doing is following:
1. I created a stored procedure "testit" which select records from another data source in another SQL Server, like:

------------
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
go

create procedure testit
as begin
select * from [AnotherSQLServer].bugtest.dbo.mssent
end

grant execute on testit to public
------------

2. I can execute this stored procedure in SQL Query Analyser with success.

3. BUT: now I write a ODBC program trying to call this stored procedure, then it give me following "famous" error:
42000 [Microsoft][ODBC SQL Server Driver][SQL Server]Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

The ODBC program looks like this:
--------
strcpy((char *) sqltext, "{call testit}");
rc = SQLPrepare(hstmt, (unsigned char *) sqltext, SQL_NTS);
rc = SQLExecute(hstmt);
--------

My Question:
1. Does ODBC API support Heterogeneous queries ?
2. If well, how to solve above problem?

Thanks a lot,

MelThe solution can be found here:

http://dbforums.com/t905850.html

use some thing like this (before calling SQLConnect() ):
SQLSetConnectAttr( hdbc,SQL_COPT_SS_ANSI_NPW,(void *) SQL_AD_ON, 0);

ODBC on remote computer won't access

I'm attempting to connect to an SQL Server Express database from a remote PC, but it isn't connecting. I have a user account created in the database, and I'm using the SQL authentication with that user account. I have success with this if I'm on the server that the database is on, but not remotely. Both are on the same network, even plugged into the same hub. There doesn't appear to be any network problems.

Any ideas? Thanks!

Please follow this instruction to enable remote connections: http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

If you still see problem, follow the instruction here and give us more details http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

odbc link failed

Hello,
I have a windows SBS2003 server with msde installed.
I created some odbc datalinks in microsoft acces to one of my sql databases.
The links are ok end i can access the odbc links in microsoft acces on my
server.
Problem :
When i open the access database on my workstation and try to connect to one
of the odbc links, i getr the followin error.
ODBC-connection to 'SQL Server<servername> failed
Microsoft Access can't open the table in Datasheet View
Any suggestions?
Thank you,
Hi
Your MSDE instance was probably installed without the network protocols
See http://support.microsoft.com/?kbid=814130 on how to run svrnetcn.exe.
John
"Ponch" wrote:

> Hello,
> I have a windows SBS2003 server with msde installed.
> I created some odbc datalinks in microsoft acces to one of my sql databases.
> The links are ok end i can access the odbc links in microsoft acces on my
> server.
> Problem :
> When i open the access database on my workstation and try to connect to one
> of the odbc links, i getr the followin error.
> ODBC-connection to 'SQL Server<servername> failed
> Microsoft Access can't open the table in Datasheet View
> Any suggestions?
> Thank you,
>
>
|||Hello, Thanks for the tip, it solved my problem.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4FD2882D-37A0-4EE7-B7A2-1C8BDEF50908@.microsoft.com...[vbcol=seagreen]
> Hi
> Your MSDE instance was probably installed without the network protocols
> See http://support.microsoft.com/?kbid=814130 on how to run svrnetcn.exe.
> John
> "Ponch" wrote:

odbc link failed

Hello,
I have a windows SBS2003 server with msde installed.
I created some odbc datalinks in microsoft acces to one of my sql databases.
The links are ok end i can access the odbc links in microsoft acces on my
server.
Problem :
When i open the access database on my workstation and try to connect to one
of the odbc links, i getr the followin error.
ODBC-connection to 'SQL Server<servername> failed
Microsoft Access can't open the table in Datasheet View
Any suggestions?
Thank you,Hi
Your MSDE instance was probably installed without the network protocols
See http://support.microsoft.com/?kbid=814130 on how to run svrnetcn.exe.
John
"Ponch" wrote:

> Hello,
> I have a windows SBS2003 server with msde installed.
> I created some odbc datalinks in microsoft acces to one of my sql database
s.
> The links are ok end i can access the odbc links in microsoft acces on my
> server.
> Problem :
> When i open the access database on my workstation and try to connect to on
e
> of the odbc links, i getr the followin error.
> ODBC-connection to 'SQL Server<servername> failed
> Microsoft Access can't open the table in Datasheet View
> Any suggestions?
> Thank you,
>
>|||Hello, Thanks for the tip, it solved my problem.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4FD2882D-37A0-4EE7-B7A2-1C8BDEF50908@.microsoft.com...[vbcol=seagreen]
> Hi
> Your MSDE instance was probably installed without the network protocols
> See http://support.microsoft.com/?kbid=814130 on how to run svrnetcn.exe.
> John
> "Ponch" wrote:
>

odbc link failed

Hello,
I have a windows SBS2003 server with msde installed.
I created some odbc datalinks in microsoft acces to one of my sql databases.
The links are ok end i can access the odbc links in microsoft acces on my
server.
Problem :
When i open the access database on my workstation and try to connect to one
of the odbc links, i getr the followin error.
ODBC-connection to 'SQL Server<servername> failed
Microsoft Access can't open the table in Datasheet View
Any suggestions?
Thank you,Hi
Your MSDE instance was probably installed without the network protocols
See http://support.microsoft.com/?kbid=814130 on how to run svrnetcn.exe.
John
"Ponch" wrote:
> Hello,
> I have a windows SBS2003 server with msde installed.
> I created some odbc datalinks in microsoft acces to one of my sql databases.
> The links are ok end i can access the odbc links in microsoft acces on my
> server.
> Problem :
> When i open the access database on my workstation and try to connect to one
> of the odbc links, i getr the followin error.
> ODBC-connection to 'SQL Server<servername> failed
> Microsoft Access can't open the table in Datasheet View
> Any suggestions?
> Thank you,
>
>|||Hello, Thanks for the tip, it solved my problem.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4FD2882D-37A0-4EE7-B7A2-1C8BDEF50908@.microsoft.com...
> Hi
> Your MSDE instance was probably installed without the network protocols
> See http://support.microsoft.com/?kbid=814130 on how to run svrnetcn.exe.
> John
> "Ponch" wrote:
>> Hello,
>> I have a windows SBS2003 server with msde installed.
>> I created some odbc datalinks in microsoft acces to one of my sql
>> databases.
>> The links are ok end i can access the odbc links in microsoft acces on my
>> server.
>> Problem :
>> When i open the access database on my workstation and try to connect to
>> one
>> of the odbc links, i getr the followin error.
>> ODBC-connection to 'SQL Server<servername> failed
>> Microsoft Access can't open the table in Datasheet View
>> Any suggestions?
>> Thank you,
>>
>>

ODBC link and viewonly

Hi,
I have a prod database (main bread & bread DB) and have a small access
database that sales team is using... I created a login inside SQL 2000
and gave db_read permission and SELECT permissions... and public.
Role of public is EXEC store procs and view some systables...
I linked those tables that I gave permissions through ODBC link into
access db with the user that I created inside SQL as readonly user...
but to my surprise when I ran a insert query from access on SQL
tables... I was able to update and insert data... if my ODBC link is
only for readonly.. why was I able to unpdate & insert data into SQL
table?
I don't want to give write/update/insert permissions for SQL tables to
sales team... or anyone outside SQL 2000 server DB.
Is there any easy way to create viewonly users inside SQL?
I created users like this: security -> logins -> new login...
select none serverrole.. db access (bread & butter DB)
Thank you,
hj
******
Pardon my English...I posted this message in this group too...
http://groups.google.com/group/comp...c24fc65a5f400c7
Hitesh wrote:
> Hi,
> I have a prod database (main bread & bread DB) and have a small access
> database that sales team is using... I created a login inside SQL 2000
> and gave db_read permission and SELECT permissions... and public.
> Role of public is EXEC store procs and view some systables...
> I linked those tables that I gave permissions through ODBC link into
> access db with the user that I created inside SQL as readonly user...
> but to my surprise when I ran a insert query from access on SQL
> tables... I was able to update and insert data... if my ODBC link is
> only for readonly.. why was I able to unpdate & insert data into SQL
> table?
> I don't want to give write/update/insert permissions for SQL tables to
> sales team... or anyone outside SQL 2000 server DB.
> Is there any easy way to create viewonly users inside SQL?
> I created users like this: security -> logins -> new login...
> select none serverrole.. db access (bread & butter DB)
>
> Thank you,
> hj
>
> ******
> Pardon my English...|||I think I figured this one out.. give db_denywrite permission for the
db I want to give readonly..
ty
Hitesh wrote:[vbcol=seagreen]
> I posted this message in this group too...
>
> http://groups.google.com/group/comp...c24fc65a5f400c7
>
> Hitesh wrote:

ODBC link and viewonly

Hi,
I have a prod database (main bread & bread DB) and have a small access
database that sales team is using... I created a login inside SQL 2000
and gave db_read permission and SELECT permissions... and public.
Role of public is EXEC store procs and view some systables...
I linked those tables that I gave permissions through ODBC link into
access db with the user that I created inside SQL as readonly user...
but to my surprise when I ran a insert query from access on SQL
tables... I was able to update and insert data... if my ODBC link is
only for readonly.. why was I able to unpdate & insert data into SQL
table?
I don't want to give write/update/insert permissions for SQL tables to
sales team... or anyone outside SQL 2000 server DB.
Is there any easy way to create viewonly users inside SQL?
I created users like this: security -> logins -> new login...
select none serverrole.. db access (bread & butter DB)
Thank you,
hj
******
Pardon my English...I posted this message in this group too...
http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/d95aea73b7b5a00c/7c24fc65a5f400c7?hl=en#7c24fc65a5f400c7
Hitesh wrote:
> Hi,
> I have a prod database (main bread & bread DB) and have a small access
> database that sales team is using... I created a login inside SQL 2000
> and gave db_read permission and SELECT permissions... and public.
> Role of public is EXEC store procs and view some systables...
> I linked those tables that I gave permissions through ODBC link into
> access db with the user that I created inside SQL as readonly user...
> but to my surprise when I ran a insert query from access on SQL
> tables... I was able to update and insert data... if my ODBC link is
> only for readonly.. why was I able to unpdate & insert data into SQL
> table?
> I don't want to give write/update/insert permissions for SQL tables to
> sales team... or anyone outside SQL 2000 server DB.
> Is there any easy way to create viewonly users inside SQL?
> I created users like this: security -> logins -> new login...
> select none serverrole.. db access (bread & butter DB)
>
> Thank you,
> hj
>
> ******
> Pardon my English...|||I think I figured this one out.. give db_denywrite permission for the
db I want to give readonly..
ty
Hitesh wrote:
> I posted this message in this group too...
>
> http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/d95aea73b7b5a00c/7c24fc65a5f400c7?hl=en#7c24fc65a5f400c7
>
> Hitesh wrote:
> > Hi,
> >
> > I have a prod database (main bread & bread DB) and have a small access
> >
> > database that sales team is using... I created a login inside SQL 2000
> > and gave db_read permission and SELECT permissions... and public.
> > Role of public is EXEC store procs and view some systables...
> > I linked those tables that I gave permissions through ODBC link into
> > access db with the user that I created inside SQL as readonly user...
> > but to my surprise when I ran a insert query from access on SQL
> > tables... I was able to update and insert data... if my ODBC link is
> > only for readonly.. why was I able to unpdate & insert data into SQL
> > table?
> > I don't want to give write/update/insert permissions for SQL tables to
> > sales team... or anyone outside SQL 2000 server DB.
> > Is there any easy way to create viewonly users inside SQL?
> > I created users like this: security -> logins -> new login...
> > select none serverrole.. db access (bread & butter DB)
> >
> >
> > Thank you,
> > hj
> >
> >
> > ******
> > Pardon my English...

ODBC Import of Citadel to SQL 2005

How do you import using integration services or what ever to import from a Citadel database? I have created an ODBC Data Source using National Instruments ODBC driver for Citadel? I have tried using Microsoft Access, but the field names are longer than 62 characters and Access won't "link" to the ODBC Data Source.

The SQL Server Management Studio\Tasks\Import Wizard doesn't appear to have a way to specify an ODBC Data Source.

Try building a new data flow using the DataReader Source component.|||Thanks, I was able to access ODBC through the "SQL Server Business Intelligence Development Studio". Although that starts a new learning curve for me. Plus there seems to be something I don't understand about National Instrument's Citadel ODBC driver--when I query one of the tables only 35 records are returned and there should be several hundred thousand records available and several thousand for the queries date and time range. Not your problem--next step is to contact NI support about their driver, etc.