Offline mode ?

Hi all,
I am designing and testing a report using an ODBC connection. The
report is short (one or two pages) but the SQL request has many tables
and takes several minutes. Being in development phase I don't need the
datas to be refreshed from SQL database every time I click on
"preview" to see the report, but this is what happens (I find no other
reason for so long response time...). So I spend a lot of time waiting
for response.
Is there a kind of "offline" mode to see layout modifications without
refreshing datas every time ?
Thanks !
Jean-MarcTry using your SQL query to export the data from the multiple tables into a
single table. For your development and testing, base your report data on the
single table.
Using Query Analyzer, do:
SELECT * INTO MyTable FROM (rest of complex query)
Now base your test report on the data in MyTable. (You can also use DTS to
create the single table, and can even export the data into a local Access
table).
Charles Kangai, MCT, MCDBA
"Jean-Marc Audrin" wrote:
> Hi all,
> I am designing and testing a report using an ODBC connection. The
> report is short (one or two pages) but the SQL request has many tables
> and takes several minutes. Being in development phase I don't need the
> datas to be refreshed from SQL database every time I click on
> "preview" to see the report, but this is what happens (I find no other
> reason for so long response time...). So I spend a lot of time waiting
> for response.
> Is there a kind of "offline" mode to see layout modifications without
> refreshing datas every time ?
> Thanks !
> Jean-Marc
>sql

Odd SQLTrigger behavior

I have three tables I am using, aspnet_Users, Stories,CustomizedStory. Stories andCustomizedStory are related via a foreign key StoryID. I've setup the tables so that when I delete aStory row it cascade deletes the corresponding row from CustomizedStory. Each CustomizedStory row has a reference to UserID from aspnet_Users.Since, I didn't want to mess with the table definition by adding a cascadedelete option on aspnet_Users, I decide to use a trigger, essentially delete all customized stories and associated stories if a user is deleted:

ALTERTRIGGER[dbo].[DeleteCustomizedStories]

ON[dbo].[aspnet_Users]
FOR DELETE
AS
BEGIN
DELETE FROMdbo.Story
WHEREStoryID=
(SELECTStoryIDFROMdbo.CustomizedStoryWHEREUserID =
(SELECTUserIDFROMdeleted))
END

The problem I am having is that it deletes all of theCustomizedStory rows as specified by the cascading option, but doesn't deletethe Story rows. I can't seem to understandwhy this is happening, especially when Iexplicitly told it to delete story rows.

The behavior is a bit weird, I must admit. Your DELETE-query is wrong though, which may cause problems.

DELETE FROMdbo.Story
WHEREStoryIDIN
(SELECTStoryIDFROMdbo.CustomizedStoryWHEREUserIDIN
(SELECTUserIDFROMdeleted))

or

DELETE s FROMdbo.Storys
INNER JOINdbo.CustomizedStory c ON s.StoryID=c.StoryID
INNER JOINdeleted d ON c.UserID=d.UserID

Odd Snapshot Behavior

I'm encountering some behavior in a Snapshot publication I just can't figure
out.
I have a publication consisting of 124 tables. These tables are all lookup
tables so the data isstatic and so are the respcetive table schemas.
I dropped all subscriptions to this publication, dropped the publication,
and disabled replication. I then configured the server for replication. I
recreated the publication with the required articles. I created a new
subscriotion and chose to create the snapshot immediately.
The snapshot gets created just fine, but when the distribution agent
attempts to apply the snapshot, I get the following error:
Cannot drop the table 'dbo.ReportTypeLookup' because it is being used for
replication
The odd thing is that I'm not trying to drop that table at all!! As soon as
I remove that particular command from MSrepl_commands, another error occurs,
only this time it's trying to drop another table.
I am 100% sure no one is trying to drop any tables in production...
If anyone has any insight into this I would truly appreciate it.
Thank You!!
It sounds like the subscriber has been previously set up as a publisher of
these tables. Try running sp_removedbreplication on the subscriber which
should reset the replication flags at the article level and then
synchronize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

odd server behavior

Hello everyone,
One of our applications was timing out while executing a certain
stored procedure that inserts information in a group of tables.
When I checked the situation via Profiler, that particular SP has
indeed an almost consistent duration of roughly 30 seconds (which was
the timeout setting at the frontend).
I copied one of the textdata entry captured in Profiler and executed
this in QA. This executed for almost 2 minutes after which all
subsequent executions of this stored procedure via the application
returned to normal with a duration of about 5 to 10ms.
Has anyone had a similar experience? I am at a loss at what happened,
and I don't know where to begin looking for the cause as it has
resolved itself when I was just attempting to look into it.
Thanks.
AramidIt could be that the auto update stats kicked in, or that the data was
readily available in cache. Do check in Profiler to see if there are any
recompilations. Also capture the execution plan in Profiler, and compare
with the one you are getting in Query Analyzer. It would also help if you
had posted the stored procedure code, so that we can point out any issues.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"aramid" <aramid@.hotmail.com> wrote in message
news:v5s371p46il11k82jn9tene1701fmtad2j@.
4ax.com...
Hello everyone,
One of our applications was timing out while executing a certain
stored procedure that inserts information in a group of tables.
When I checked the situation via Profiler, that particular SP has
indeed an almost consistent duration of roughly 30 seconds (which was
the timeout setting at the frontend).
I copied one of the textdata entry captured in Profiler and executed
this in QA. This executed for almost 2 minutes after which all
subsequent executions of this stored procedure via the application
returned to normal with a duration of about 5 to 10ms.
Has anyone had a similar experience? I am at a loss at what happened,
and I don't know where to begin looking for the cause as it has
resolved itself when I was just attempting to look into it.
Thanks.
Aramidsql

Odd behavior in partitioning in SQL 2000

Problem: We've performed distributed partitioning on one of our
largest and most hit tables, over 25 million rows Most of the 15
underlying tables called by the partition view have 1 distinct value
in it clustered index, while others have multiple values (ids). It
works well for most queries ran against it, however there is the
occasional query that seems to run long and create locking on the
underlying partition table.
Resolution 1: Called Microsoft in search of resolution and after
months of back and forth with them their original suggestion was
create an Indexed view. This was shot down in the blue sky phase
because it would undo most of the performance benefits we are seeing.
Their next suggestion was a complete table redesign and that too did
not sit well with management as we had followed the structure
suggestions given in the SBO.
Resolution 2: This was discovered by accident in testing, but, we
found that executing a query as dynamic SQL instead of inline (from a
stored proc). Despite the recompile cost it began using different
indexes and would show us a boost in performance.
Question: Has anyone else seen this behavior before in either
partitioning or with gaining performance increase by converting the
query in a stored proc to dynamic SQL? Microsoft rationalized that
since a view does not have access to a histogram that it may be
creating some of our performance problems and explain that why they
are sporadic instead of constant. Any insight would be greatly
appreciated.
This seems to suggest parameter sniffing:
http://groups.google.ca/groups/search?q=%22parameter+sniffing%22&hl=en&ie=UTF-8&oe=UTF-8&
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181744183.369244.18310@.i38g2000prf.googlegro ups.com...
Problem: We've performed distributed partitioning on one of our
largest and most hit tables, over 25 million rows Most of the 15
underlying tables called by the partition view have 1 distinct value
in it clustered index, while others have multiple values (ids). It
works well for most queries ran against it, however there is the
occasional query that seems to run long and create locking on the
underlying partition table.
Resolution 1: Called Microsoft in search of resolution and after
months of back and forth with them their original suggestion was
create an Indexed view. This was shot down in the blue sky phase
because it would undo most of the performance benefits we are seeing.
Their next suggestion was a complete table redesign and that too did
not sit well with management as we had followed the structure
suggestions given in the SBO.
Resolution 2: This was discovered by accident in testing, but, we
found that executing a query as dynamic SQL instead of inline (from a
stored proc). Despite the recompile cost it began using different
indexes and would show us a boost in performance.
Question: Has anyone else seen this behavior before in either
partitioning or with gaining performance increase by converting the
query in a stored proc to dynamic SQL? Microsoft rationalized that
since a view does not have access to a histogram that it may be
creating some of our performance problems and explain that why they
are sporadic instead of constant. Any insight would be greatly
appreciated.
|||Thanks for the input Tom. I took one of our procedures and tried a
workaround suggested: http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
The end result was that the execution time was actually higher than
the original. The estimated row count was less (from 332 to 44) and
executions jumped from 4 to over 200k. I think this may work for more
simple queries but, dynamic still gives us the best performance.
Any other ideas or insight about this kind of performance problem/fix
seen with SQL 2000 distributed partitioning
On Jun 13, 10:36 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> This seems to suggest parameter sniffing:
> http://groups.google.ca/groups/search?q=%22parameter+sniffing%22&hl=e...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>
|||Without seeing all of your DDL and knowing your data distribution, it would
be hard to tell. Since you have a workaround via dynamic SQL, you may very
well have to live with that until you can upgrade to SQL 2005. Just make
sure you put in some defensive coding to prevent SQL injection attacks.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181763906.605010.12270@.d30g2000prg.googlegro ups.com...
Thanks for the input Tom. I took one of our procedures and tried a
workaround suggested:
http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
The end result was that the execution time was actually higher than
the original. The estimated row count was less (from 332 to 44) and
executions jumped from 4 to over 200k. I think this may work for more
simple queries but, dynamic still gives us the best performance.
Any other ideas or insight about this kind of performance problem/fix
seen with SQL 2000 distributed partitioning
On Jun 13, 10:36 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> This seems to suggest parameter sniffing:
> http://groups.google.ca/groups/search?q=%22parameter+sniffing%22&hl=e...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>
|||Interesting that you should mention upgrading to 2005. What
enhancement would that grant us or problems would that address. We're
looking for a target date for going to 2005 and any information you
have on it pertaining to this topic may help me make the case that we
should upgrade sooner than later.
|||Some resources:
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=en-US&EventID=1032276813&CountryCode=US
[url]http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032270016&EventC ategory=3&culture=en-US&CountryCode=US[/url]
http://download.microsoft.com/download/4/1/f/41f09116-19b3-40fe-9d54-d1e9b7af9e82/REAL_Lifecycle_Partitioning.doc
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181931792.613927.65580@.c77g2000hse.googlegro ups.com...
Interesting that you should mention upgrading to 2005. What
enhancement would that grant us or problems would that address. We're
looking for a target date for going to 2005 and any information you
have on it pertaining to this topic may help me make the case that we
should upgrade sooner than later.

Odd behavior in partitioning in SQL 2000

Problem: We've performed distributed partitioning on one of our
largest and most hit tables, over 25 million rows Most of the 15
underlying tables called by the partition view have 1 distinct value
in it clustered index, while others have multiple values (ids). It
works well for most queries ran against it, however there is the
occasional query that seems to run long and create locking on the
underlying partition table.
Resolution 1: Called Microsoft in search of resolution and after
months of back and forth with them their original suggestion was
create an Indexed view. This was shot down in the blue sky phase
because it would undo most of the performance benefits we are seeing.
Their next suggestion was a complete table redesign and that too did
not sit well with management as we had followed the structure
suggestions given in the SBO.
Resolution 2: This was discovered by accident in testing, but, we
found that executing a query as dynamic SQL instead of inline (from a
stored proc). Despite the recompile cost it began using different
indexes and would show us a boost in performance.
Question: Has anyone else seen this behavior before in either
partitioning or with gaining performance increase by converting the
query in a stored proc to dynamic SQL? Microsoft rationalized that
since a view does not have access to a histogram that it may be
creating some of our performance problems and explain that why they
are sporadic instead of constant. Any insight would be greatly
appreciated.This seems to suggest parameter sniffing:
http://groups.google.ca/groups/sear...
=UTF-8&
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181744183.369244.18310@.i38g2000prf.googlegroups.com...
Problem: We've performed distributed partitioning on one of our
largest and most hit tables, over 25 million rows Most of the 15
underlying tables called by the partition view have 1 distinct value
in it clustered index, while others have multiple values (ids). It
works well for most queries ran against it, however there is the
occasional query that seems to run long and create locking on the
underlying partition table.
Resolution 1: Called Microsoft in search of resolution and after
months of back and forth with them their original suggestion was
create an Indexed view. This was shot down in the blue sky phase
because it would undo most of the performance benefits we are seeing.
Their next suggestion was a complete table redesign and that too did
not sit well with management as we had followed the structure
suggestions given in the SBO.
Resolution 2: This was discovered by accident in testing, but, we
found that executing a query as dynamic SQL instead of inline (from a
stored proc). Despite the recompile cost it began using different
indexes and would show us a boost in performance.
Question: Has anyone else seen this behavior before in either
partitioning or with gaining performance increase by converting the
query in a stored proc to dynamic SQL? Microsoft rationalized that
since a view does not have access to a histogram that it may be
creating some of our performance problems and explain that why they
are sporadic instead of constant. Any insight would be greatly
appreciated.|||Thanks for the input Tom. I took one of our procedures and tried a
workaround suggested: http://omnibuzz-sql.blogspot.com/20...procedures.html
The end result was that the execution time was actually higher than
the original. The estimated row count was less (from 332 to 44) and
executions jumped from 4 to over 200k. I think this may work for more
simple queries but, dynamic still gives us the best performance.
Any other ideas or insight about this kind of performance problem/fix
seen with SQL 2000 distributed partitioning
On Jun 13, 10:36 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> This seems to suggest parameter sniffing:
> http://groups.google.ca/groups/sear...fing%22&hl=e...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>|||Without seeing all of your DDL and knowing your data distribution, it would
be hard to tell. Since you have a workaround via dynamic SQL, you may very
well have to live with that until you can upgrade to SQL 2005. Just make
sure you put in some defensive coding to prevent SQL injection attacks.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181763906.605010.12270@.d30g2000prg.googlegroups.com...
Thanks for the input Tom. I took one of our procedures and tried a
workaround suggested:
http://omnibuzz-sql.blogspot.com/20...es.html

The end result was that the execution time was actually higher than
the original. The estimated row count was less (from 332 to 44) and
executions jumped from 4 to over 200k. I think this may work for more
simple queries but, dynamic still gives us the best performance.
Any other ideas or insight about this kind of performance problem/fix
seen with SQL 2000 distributed partitioning
On Jun 13, 10:36 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> This seems to suggest parameter sniffing:
> http://groups.google.ca/groups/sear...fing%22&hl=e...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>|||Interesting that you should mention upgrading to 2005. What
enhancement would that grant us or problems would that address. We're
looking for a target date for going to 2005 and any information you
have on it pertaining to this topic may help me make the case that we
should upgrade sooner than later.|||Some resources:
http://msevents.microsoft.com/CUI/W...&CountryCode=US
http://msevents.microsoft.com/CUI/W...&CountryCode=US
http://download.microsoft.com/downl...artitioning.doc
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181931792.613927.65580@.c77g2000hse.googlegroups.com...
Interesting that you should mention upgrading to 2005. What
enhancement would that grant us or problems would that address. We're
looking for a target date for going to 2005 and any information you
have on it pertaining to this topic may help me make the case that we
should upgrade sooner than later.

Odd behavior in partitioning in SQL 2000

Problem: We've performed distributed partitioning on one of our
largest and most hit tables, over 25 million rows Most of the 15
underlying tables called by the partition view have 1 distinct value
in it clustered index, while others have multiple values (ids). It
works well for most queries ran against it, however there is the
occasional query that seems to run long and create locking on the
underlying partition table.
Resolution 1: Called Microsoft in search of resolution and after
months of back and forth with them their original suggestion was
create an Indexed view. This was shot down in the blue sky phase
because it would undo most of the performance benefits we are seeing.
Their next suggestion was a complete table redesign and that too did
not sit well with management as we had followed the structure
suggestions given in the SBO.
Resolution 2: This was discovered by accident in testing, but, we
found that executing a query as dynamic SQL instead of inline (from a
stored proc). Despite the recompile cost it began using different
indexes and would show us a boost in performance.
Question: Has anyone else seen this behavior before in either
partitioning or with gaining performance increase by converting the
query in a stored proc to dynamic SQL? Microsoft rationalized that
since a view does not have access to a histogram that it may be
creating some of our performance problems and explain that why they
are sporadic instead of constant. Any insight would be greatly
appreciated.This seems to suggest parameter sniffing:
http://groups.google.ca/groups/search?q=%22parameter+sniffing%22&hl=en&ie=UTF-8&oe=UTF-8&
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181744183.369244.18310@.i38g2000prf.googlegroups.com...
Problem: We've performed distributed partitioning on one of our
largest and most hit tables, over 25 million rows Most of the 15
underlying tables called by the partition view have 1 distinct value
in it clustered index, while others have multiple values (ids). It
works well for most queries ran against it, however there is the
occasional query that seems to run long and create locking on the
underlying partition table.
Resolution 1: Called Microsoft in search of resolution and after
months of back and forth with them their original suggestion was
create an Indexed view. This was shot down in the blue sky phase
because it would undo most of the performance benefits we are seeing.
Their next suggestion was a complete table redesign and that too did
not sit well with management as we had followed the structure
suggestions given in the SBO.
Resolution 2: This was discovered by accident in testing, but, we
found that executing a query as dynamic SQL instead of inline (from a
stored proc). Despite the recompile cost it began using different
indexes and would show us a boost in performance.
Question: Has anyone else seen this behavior before in either
partitioning or with gaining performance increase by converting the
query in a stored proc to dynamic SQL? Microsoft rationalized that
since a view does not have access to a histogram that it may be
creating some of our performance problems and explain that why they
are sporadic instead of constant. Any insight would be greatly
appreciated.|||Thanks for the input Tom. I took one of our procedures and tried a
workaround suggested: http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
The end result was that the execution time was actually higher than
the original. The estimated row count was less (from 332 to 44) and
executions jumped from 4 to over 200k. I think this may work for more
simple queries but, dynamic still gives us the best performance.
Any other ideas or insight about this kind of performance problem/fix
seen with SQL 2000 distributed partitioning
On Jun 13, 10:36 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> This seems to suggest parameter sniffing:
> http://groups.google.ca/groups/search?q=%22parameter+sniffing%22&hl=e...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>|||Without seeing all of your DDL and knowing your data distribution, it would
be hard to tell. Since you have a workaround via dynamic SQL, you may very
well have to live with that until you can upgrade to SQL 2005. Just make
sure you put in some defensive coding to prevent SQL injection attacks.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181763906.605010.12270@.d30g2000prg.googlegroups.com...
Thanks for the input Tom. I took one of our procedures and tried a
workaround suggested:
http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
The end result was that the execution time was actually higher than
the original. The estimated row count was less (from 332 to 44) and
executions jumped from 4 to over 200k. I think this may work for more
simple queries but, dynamic still gives us the best performance.
Any other ideas or insight about this kind of performance problem/fix
seen with SQL 2000 distributed partitioning
On Jun 13, 10:36 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> This seems to suggest parameter sniffing:
> http://groups.google.ca/groups/search?q=%22parameter+sniffing%22&hl=e...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>|||Interesting that you should mention upgrading to 2005. What
enhancement would that grant us or problems would that address. We're
looking for a target date for going to 2005 and any information you
have on it pertaining to this topic may help me make the case that we
should upgrade sooner than later.|||Some resources:
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=en-US&EventID=1032276813&CountryCode=US
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032270016&EventCategory=3&culture=en-US&CountryCode=US
http://download.microsoft.com/download/4/1/f/41f09116-19b3-40fe-9d54-d1e9b7af9e82/REAL_Lifecycle_Partitioning.doc
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181931792.613927.65580@.c77g2000hse.googlegroups.com...
Interesting that you should mention upgrading to 2005. What
enhancement would that grant us or problems would that address. We're
looking for a target date for going to 2005 and any information you
have on it pertaining to this topic may help me make the case that we
should upgrade sooner than later.

odbc to progress db

I have users that thru and ODBC connection to my Sql2000 db attach tables
and query different information. Now they need to read data from a Progress
database on Unix to pull some values from a table. I have the ODBC driver
that allows me to connect to the Progress DB. Could I create a DB in SQL
that is nothing but views to the Progress tables?
I don't want to replicate the data down to Sql.
Any help or suggestions would be much appreciated.Hi Andy
"Andy" wrote:
> I have users that thru and ODBC connection to my Sql2000 db attach tables
> and query different information. Now they need to read data from a Progress
> database on Unix to pull some values from a table. I have the ODBC driver
> that allows me to connect to the Progress DB. Could I create a DB in SQL
> that is nothing but views to the Progress tables?
> I don't want to replicate the data down to Sql.
>
> Any help or suggestions would be much appreciated.
>
If you have an odbc driver then you may be able to create a linked server
that will allow you users to query the remote database. See
http://msdn2.microsoft.com/ru-ru/library/aa213778(SQL.80).aspx and
http://msdn2.microsoft.com/ru-ru/library/aa259589(SQL.80).aspx for more.
John|||Do you still create a DB in SQL.
After you create the Link-Server how do the users run queries agisnt the
Progress(Unix) tables?
What does ther syntax look like?
Thanks..
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1F399B30-FDAD-41CF-B05D-B56B48FFFBEE@.microsoft.com...
> Hi Andy
> "Andy" wrote:
>> I have users that thru and ODBC connection to my Sql2000 db attach tables
>> and query different information. Now they need to read data from a
>> Progress
>> database on Unix to pull some values from a table. I have the ODBC driver
>> that allows me to connect to the Progress DB. Could I create a DB in SQL
>> that is nothing but views to the Progress tables?
>> I don't want to replicate the data down to Sql.
>>
>> Any help or suggestions would be much appreciated.
> If you have an odbc driver then you may be able to create a linked server
> that will allow you users to query the remote database. See
> http://msdn2.microsoft.com/ru-ru/library/aa213778(SQL.80).aspx and
> http://msdn2.microsoft.com/ru-ru/library/aa259589(SQL.80).aspx for more.
>
> John|||Hi
You don't need to create a separate database unless you want to keep access
away from your existing databases.
You can access the linked server using four part names such as:
SELECT col1, col2
FROM RemoteServer.RemoteDB..RemoteTable
See http://msdn2.microsoft.com/en-us/library/aa172676(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/ms187879.aspx
You can also use OPENQUERY see
http://msdn2.microsoft.com/en-us/library/aa276848(SQL.80).aspx
If you use stored procedures or views
http://msdn2.microsoft.com/en-us/library/aa258253(SQL.80).aspx to access the
remote database the users do not need to know where the data is.
John
"Andy" wrote:
> Do you still create a DB in SQL.
> After you create the Link-Server how do the users run queries agisnt the
> Progress(Unix) tables?
> What does ther syntax look like?
> Thanks..
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:1F399B30-FDAD-41CF-B05D-B56B48FFFBEE@.microsoft.com...
> > Hi Andy
> >
> > "Andy" wrote:
> >
> >> I have users that thru and ODBC connection to my Sql2000 db attach tables
> >> and query different information. Now they need to read data from a
> >> Progress
> >> database on Unix to pull some values from a table. I have the ODBC driver
> >> that allows me to connect to the Progress DB. Could I create a DB in SQL
> >> that is nothing but views to the Progress tables?
> >>
> >> I don't want to replicate the data down to Sql.
> >>
> >>
> >> Any help or suggestions would be much appreciated.
> >>
> > If you have an odbc driver then you may be able to create a linked server
> > that will allow you users to query the remote database. See
> > http://msdn2.microsoft.com/ru-ru/library/aa213778(SQL.80).aspx and
> > http://msdn2.microsoft.com/ru-ru/library/aa259589(SQL.80).aspx for more.
> >
> >
> > John
>
>

ODBC to MSDE

I need to write a VB.Net application in VS.net 2003 that copies several tables from an ODBC data source to MSDE tables. I do not have Enterprise Manager or DTS to use. Is there an example of this in VS? I was hoping VS has some built in tools for this. If not, bulk Insert?

Help appreciated.
thanks,
FrankI would consider using MS Access for this, as it accesses both ODBC and MSDE data sources quite well (presuming this is not something that needs to run like a service).|||I ruled out Access because of several reasons. My next step is to schedule the scripts thru the SQL agent that MSDE comes with.

Help appreciated.
Thanks,
Frank

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

Hi, I've a Access 2003 program with linked tables to a sqlexpress configured
with mixed mode security.
From clients XP no problem.
From clients 2000 MSACCESS don't get the right configuration from ODBC
source, because try to conect with Windows Authentication Mode security, but
the source is configurated with mixed mode. If I refresh the linked tables
and change the password, works fine, but when I close MSACCESS and open
again, fail.
Anybody knows where's the bug? In ODBC driver? MSACCESS?You could be hitting an MDAC bug where the authentication
mode setting is ignored and windows authentication is always
used. Refer to:
FIX: SQL Server ODBC Driver Ignores Authentication Setting
http://support.microsoft.com/?id=279526
You can use component checker to verify the version and MDAC
installation on any of the clients. You can download
component checker and MDAC versions from this site:
http://msdn.microsoft.com/data/ref/mdac/downloads/
-Sue
On Fri, 18 Aug 2006 11:27:01 -0700, Negri
<Negri@.discussions.microsoft.com> wrote:

>Hi, I've a Access 2003 program with linked tables to a sqlexpress configure
d
>with mixed mode security.
>From clients XP no problem.
>From clients 2000 MSACCESS don't get the right configuration from ODBC
>source, because try to conect with Windows Authentication Mode security, bu
t
>the source is configurated with mixed mode. If I refresh the linked tables
>and change the password, works fine, but when I close MSACCESS and open
>again, fail.
>Anybody knows where's the bug? In ODBC driver? MSACCESS?|||Thanks Sue, I'll try it next monday and tell you.
"Sue Hoegemeier" wrote:

> You could be hitting an MDAC bug where the authentication
> mode setting is ignored and windows authentication is always
> used. Refer to:
> FIX: SQL Server ODBC Driver Ignores Authentication Setting
> http://support.microsoft.com/?id=279526
> You can use component checker to verify the version and MDAC
> installation on any of the clients. You can download
> component checker and MDAC versions from this site:
> http://msdn.microsoft.com/data/ref/mdac/downloads/
> -Sue
> On Fri, 18 Aug 2006 11:27:01 -0700, Negri
> <Negri@.discussions.microsoft.com> wrote:
>
>

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 MS Access - SQL Server

Although it appears my ODBC connection between my SQL Server tables and MS
Access was successful; I cannot modify any of them through MS Access. I do
have full rights to the SQL Tables and the connection was built appropiatly.
Other users are able to read and write using the same connection settings.
Please help!!
Are these linked tables? it sounds like you are using linked
tables and didn't indicate the unique identifier for the
tables when you linked them.
-Sue
On Mon, 23 Apr 2007 06:18:03 -0700, Leo Ruiz <Leo
Ruiz@.discussions.microsoft.com> wrote:

>Although it appears my ODBC connection between my SQL Server tables and MS
>Access was successful; I cannot modify any of them through MS Access. I do
>have full rights to the SQL Tables and the connection was built appropiatly.
>Other users are able to read and write using the same connection settings.
>Please help!!
|||That certainly did the trick! You rock Sue!
HOWEVER - now that I'm able to update my tables thru Access, the changes I
make do not refresh in SQL Analyser immediatly. As a matter of fact, I can
only see the changes once I close SQL and re-open it.
I've seen this interaction before so I know it can happen. The only
difference is that my peers have their MS Access interface set up as a
project (.adp) file and their tables do not seem to be linked. (Globe icon
next to each table)
You've been a great help! Thanks in advance for your time.
Leo
"Sue Hoegemeier" wrote:

> Are these linked tables? it sounds like you are using linked
> tables and didn't indicate the unique identifier for the
> tables when you linked them.
> -Sue
> On Mon, 23 Apr 2007 06:18:03 -0700, Leo Ruiz <Leo
> Ruiz@.discussions.microsoft.com> wrote:
>
>
|||Sue, never mind my statement below. Got it to work.
You still rock! Thanks again
"Leo Ruiz" wrote:
[vbcol=seagreen]
> That certainly did the trick! You rock Sue!
> HOWEVER - now that I'm able to update my tables thru Access, the changes I
> make do not refresh in SQL Analyser immediatly. As a matter of fact, I can
> only see the changes once I close SQL and re-open it.
> I've seen this interaction before so I know it can happen. The only
> difference is that my peers have their MS Access interface set up as a
> project (.adp) file and their tables do not seem to be linked. (Globe icon
> next to each table)
> You've been a great help! Thanks in advance for your time.
> Leo
> "Sue Hoegemeier" wrote:
|||MDB is obsolete; you should just use Access Data Projects
"Leo Ruiz" <Leo Ruiz@.discussions.microsoft.com> wrote in message
news:C1370BA6-9EB4-4A5E-A4CB-B8179D22D3C5@.microsoft.com...
> Although it appears my ODBC connection between my SQL Server tables and MS
> Access was successful; I cannot modify any of them through MS Access. I do
> have full rights to the SQL Tables and the connection was built
appropiatly.
> Other users are able to read and write using the same connection settings.
> Please help!!

ODBC MS Access - SQL Server

Although it appears my ODBC connection between my SQL Server tables and MS
Access was successful; I cannot modify any of them through MS Access. I do
have full rights to the SQL Tables and the connection was built appropiatly.
Other users are able to read and write using the same connection settings.
Please help!!Are these linked tables? it sounds like you are using linked
tables and didn't indicate the unique identifier for the
tables when you linked them.
-Sue
On Mon, 23 Apr 2007 06:18:03 -0700, Leo Ruiz <Leo
Ruiz@.discussions.microsoft.com> wrote:

>Although it appears my ODBC connection between my SQL Server tables and MS
>Access was successful; I cannot modify any of them through MS Access. I do
>have full rights to the SQL Tables and the connection was built appropiatly
.
>Other users are able to read and write using the same connection settings.
>Please help!!|||That certainly did the trick! You rock Sue!
HOWEVER - now that I'm able to update my tables thru Access, the changes I
make do not refresh in SQL Analyser immediatly. As a matter of fact, I can
only see the changes once I close SQL and re-open it.
I've seen this interaction before so I know it can happen. The only
difference is that my peers have their MS Access interface set up as a
project (.adp) file and their tables do not seem to be linked. (Globe icon
next to each table)
You've been a great help! Thanks in advance for your time.
Leo
"Sue Hoegemeier" wrote:

> Are these linked tables? it sounds like you are using linked
> tables and didn't indicate the unique identifier for the
> tables when you linked them.
> -Sue
> On Mon, 23 Apr 2007 06:18:03 -0700, Leo Ruiz <Leo
> Ruiz@.discussions.microsoft.com> wrote:
>
>|||Sue, never mind my statement below. Got it to work.
You still rock! Thanks again
"Leo Ruiz" wrote:
[vbcol=seagreen]
> That certainly did the trick! You rock Sue!
> HOWEVER - now that I'm able to update my tables thru Access, the changes I
> make do not refresh in SQL Analyser immediatly. As a matter of fact, I can
> only see the changes once I close SQL and re-open it.
> I've seen this interaction before so I know it can happen. The only
> difference is that my peers have their MS Access interface set up as a
> project (.adp) file and their tables do not seem to be linked. (Globe icon
> next to each table)
> You've been a great help! Thanks in advance for your time.
> Leo
> "Sue Hoegemeier" wrote:
>|||MDB is obsolete; you should just use Access Data Projects
"Leo Ruiz" <Leo Ruiz@.discussions.microsoft.com> wrote in message
news:C1370BA6-9EB4-4A5E-A4CB-B8179D22D3C5@.microsoft.com...
> Although it appears my ODBC connection between my SQL Server tables and MS
> Access was successful; I cannot modify any of them through MS Access. I do
> have full rights to the SQL Tables and the connection was built
appropiatly.
> Other users are able to read and write using the same connection settings.
> Please help!!

ODBC linked table support in SQL SERVER 2005

Hi,

We still have some legacy applications that use ODBC linked tables to access SQL SERVER 2000.

Questions: Will these legacy applications still function with SQL SERVER 2005 / SQL SERVER 2005 Express? In order to relink the tables within the Access front-end do we need to install the new SQL Native Client that contains the new ODBC driver for SQL SERVER 2005?

Any input would be appreciated.

ThanksThe existing SQL Server 2000 ODBC driver can be used with SQL Server 2005, there is no need to update existing clients just because you upgraded the server. In general you only have to upgrade to SQL Native Client when you change application code to take advantage of new features in SQL Server 2005.|||What version of the SQL Server 2000 ODBC Driver is supported for accessing SQL Server 2005 databases? Thanks.|||

2.5 and later are supported with SQL Server 2005.

In general, since MDAC is now part of Windows, if you're up to date on Windows service packs you should have no problems. For Windows 2000 and later this means you'll have 2.81 installed.

ODBC linked table support in SQL SERVER 2005

Hi,

We still have some legacy applications that use ODBC linked tables to access SQL SERVER 2000.

Questions: Will these legacy applications still function with SQL SERVER 2005 / SQL SERVER 2005 Express? In order to relink the tables within the Access front-end do we need to install the new SQL Native Client that contains the new ODBC driver for SQL SERVER 2005?

Any input would be appreciated.

ThanksThe existing SQL Server 2000 ODBC driver can be used with SQL Server 2005, there is no need to update existing clients just because you upgraded the server. In general you only have to upgrade to SQL Native Client when you change application code to take advantage of new features in SQL Server 2005.

|||What version of the SQL Server 2000 ODBC Driver is supported for accessing SQL Server 2005 databases? Thanks.|||

2.5 and later are supported with SQL Server 2005.

In general, since MDAC is now part of Windows, if you're up to date on Windows service packs you should have no problems. For Windows 2000 and later this means you'll have 2.81 installed.

ODBC Link to SQL 2005

I am trying to link table to Access 2003 from SQL 2005
Some tables come across ok but others just have the word deleted in all the
fields
Any ideas?
Maybe these table have a missing primary key or you using a type of field -
such as BIGINT - which is incompatible with Access. Nullable bit value (Bit
field without a default value and that can be set to Null) and some out of
range precision for decimal/currency type can also be troublesome.
You should make a copy of these tables and start removing the fields until
you find the offensive one(s).
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Newbie" <nospam@.nowhere.com> wrote in message
news:eJ1Vr58ZHHA.4856@.TK2MSFTNGP03.phx.gbl...
>I am trying to link table to Access 2003 from SQL 2005
> Some tables come across ok but others just have the word deleted in all
> the fields
> Any ideas?
>
|||Hi, I needed to figure this out too. Evidently, use of bigint (or other
floating) used as a key will not produce an updateable link. In access, you
probably don't want to anyway, but it would be nice to see the data instead
of #Delete splattered all over the screen. My solution: Set up a query
(simple select *), and change the Recordset Type property to Snapshot. Use
this snapshot query as a source to any subsequent work.
"Newbie" wrote:

> I am trying to link table to Access 2003 from SQL 2005
> Some tables come across ok but others just have the word deleted in all the
> fields
> Any ideas?
>
>
|||you just need to use Access DAta Projects
all this linking crap is called 'unnecessary complexity'
"phillips001" <phillips001@.discussions.microsoft.com> wrote in message
news:F1BF9A8D-412E-476A-B164-2AE3CDE0B95B@.microsoft.com...[vbcol=seagreen]
> Hi, I needed to figure this out too. Evidently, use of bigint (or other
> floating) used as a key will not produce an updateable link. In access,
> you
> probably don't want to anyway, but it would be nice to see the data
> instead
> of #Delete splattered all over the screen. My solution: Set up a query
> (simple select *), and change the Recordset Type property to Snapshot.
> Use
> this snapshot query as a source to any subsequent work.
> "Newbie" wrote:
|||Everyone please note that Aaron Kem.pf is attempting to impersonate one of
our regular posters again. Tom would never post such a message.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
"Tom Wickerath" <tom_wickerath@.hotmail.com> wrote in message
news:uVxngNdnHHA.1240@.TK2MSFTNGP04.phx.gbl...
> you just need to use Access DAta Projects
> all this linking crap is called 'unnecessary complexity'
>
> "phillips001" <phillips001@.discussions.microsoft.com> wrote in message
> news:F1BF9A8D-412E-476A-B164-2AE3CDE0B95B@.microsoft.com...
>

ODBC Link to SQL 2005

I am trying to link table to Access 2003 from SQL 2005
Some tables come across ok but others just have the word deleted in all the
fields
Any ideas?Maybe these table have a missing primary key or you using a type of field -
such as BIGINT - which is incompatible with Access. Nullable bit value (Bit
field without a default value and that can be set to Null) and some out of
range precision for decimal/currency type can also be troublesome.
You should make a copy of these tables and start removing the fields until
you find the offensive one(s).
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Newbie" <nospam@.nowhere.com> wrote in message
news:eJ1Vr58ZHHA.4856@.TK2MSFTNGP03.phx.gbl...
>I am trying to link table to Access 2003 from SQL 2005
> Some tables come across ok but others just have the word deleted in all
> the fields
> Any ideas?
>|||Hi, I needed to figure this out too. Evidently, use of bigint (or other
floating) used as a key will not produce an updateable link. In access, you
probably don't want to anyway, but it would be nice to see the data instead
of #Delete splattered all over the screen. My solution: Set up a query
(simple select *), and change the Recordset Type property to Snapshot. Use
this snapshot query as a source to any subsequent work.
"Newbie" wrote:

> I am trying to link table to Access 2003 from SQL 2005
> Some tables come across ok but others just have the word deleted in all th
e
> fields
> Any ideas?
>
>|||you just need to use Access DAta Projects
all this linking crap is called 'unnecessary complexity'
"phillips001" <phillips001@.discussions.microsoft.com> wrote in message
news:F1BF9A8D-412E-476A-B164-2AE3CDE0B95B@.microsoft.com...[vbcol=seagreen]
> Hi, I needed to figure this out too. Evidently, use of bigint (or other
> floating) used as a key will not produce an updateable link. In access,
> you
> probably don't want to anyway, but it would be nice to see the data
> instead
> of #Delete splattered all over the screen. My solution: Set up a query
> (simple select *), and change the Recordset Type property to Snapshot.
> Use
> this snapshot query as a source to any subsequent work.
> "Newbie" wrote:
>|||Everyone please note that Aaron Kem.pf is attempting to impersonate one of
our regular posters again. Tom would never post such a message.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com,www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/e...tributors2.html for contact
info.
"Tom Wickerath" <tom_wickerath@.hotmail.com> wrote in message
news:uVxngNdnHHA.1240@.TK2MSFTNGP04.phx.gbl...
> you just need to use Access DAta Projects
> all this linking crap is called 'unnecessary complexity'
>
> "phillips001" <phillips001@.discussions.microsoft.com> wrote in message
> news:F1BF9A8D-412E-476A-B164-2AE3CDE0B95B@.microsoft.com...
>

ODBC Link to SQL 2005

I am trying to link table to Access 2003 from SQL 2005
Some tables come across ok but others just have the word deleted in all the
fields
Any ideas?
Maybe these table have a missing primary key or you using a type of field -
such as BIGINT - which is incompatible with Access. Nullable bit value (Bit
field without a default value and that can be set to Null) and some out of
range precision for decimal/currency type can also be troublesome.
You should make a copy of these tables and start removing the fields until
you find the offensive one(s).
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Newbie" <nospam@.nowhere.com> wrote in message
news:eJ1Vr58ZHHA.4856@.TK2MSFTNGP03.phx.gbl...
>I am trying to link table to Access 2003 from SQL 2005
> Some tables come across ok but others just have the word deleted in all
> the fields
> Any ideas?
>
|||Hi, I needed to figure this out too. Evidently, use of bigint (or other
floating) used as a key will not produce an updateable link. In access, you
probably don't want to anyway, but it would be nice to see the data instead
of #Delete splattered all over the screen. My solution: Set up a query
(simple select *), and change the Recordset Type property to Snapshot. Use
this snapshot query as a source to any subsequent work.
"Newbie" wrote:

> I am trying to link table to Access 2003 from SQL 2005
> Some tables come across ok but others just have the word deleted in all the
> fields
> Any ideas?
>
>
|||you just need to use Access DAta Projects
all this linking crap is called 'unnecessary complexity'
"phillips001" <phillips001@.discussions.microsoft.com> wrote in message
news:F1BF9A8D-412E-476A-B164-2AE3CDE0B95B@.microsoft.com...[vbcol=seagreen]
> Hi, I needed to figure this out too. Evidently, use of bigint (or other
> floating) used as a key will not produce an updateable link. In access,
> you
> probably don't want to anyway, but it would be nice to see the data
> instead
> of #Delete splattered all over the screen. My solution: Set up a query
> (simple select *), and change the Recordset Type property to Snapshot.
> Use
> this snapshot query as a source to any subsequent work.
> "Newbie" wrote:
|||Everyone please note that Aaron Kem.pf is attempting to impersonate one of
our regular posters again. Tom would never post such a message.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
"Tom Wickerath" <tom_wickerath@.hotmail.com> wrote in message
news:uVxngNdnHHA.1240@.TK2MSFTNGP04.phx.gbl...
> you just need to use Access DAta Projects
> all this linking crap is called 'unnecessary complexity'
>
> "phillips001" <phillips001@.discussions.microsoft.com> wrote in message
> news:F1BF9A8D-412E-476A-B164-2AE3CDE0B95B@.microsoft.com...
>

ODBC From Access to SQL Connection Wizard will not come up.

When I choose ODBC from the link tables wizard it just disapears. No error message at all just poof. Any one have any ideas?

Thanks

Preston,

What you has described looks like you can not open ODB Data source Administrator.

Can you try the following to see if it works.

http://msdn2.microsoft.com/en-us/library/ms188691(SQL.90).aspx.

If the above link does not work, can you describe more about what configurations you did to your machine recently, such as installations?

|||

Thanks Nan,

I can create and change data sources from the admin section. I can also access those sources from other programs such as DAP. For some reason only in access does this happen. When I choose ODBC from the file type drop down in access it just disapears no hour glass no error nothing. I haven't really installed any new programs other then updates to windows ect. I'm also not sure when it started. I had to add a new table in SQL and when I tried to access it from the app in access i found this.

I am wondering if Access needs to be re-installed or if a update from Microsoft is causing this strange behavior.

Preston

|||We are having the same problem. Multiple computers. Access will no longer link ODBC and we can't get to our SQL Servers. Something has changed recently but I am not sure when.|||

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=161918&SiteID=1

This guy solved the problem. Turned Norton off and the connection works. He says its the Office Plugin.

|||

I just tested this with Access 2003 and everything worked fine for me, I can link ODBC tables including SQL Server fine. Note my configuration is:

1. Microsoft Access 2003

2. Windows XP SP2 with latest security patches (including recent MDAC patch).

I would post to the Microsoft Access newsgroups to see if they have heard of anything recent.

|||

Matt, the problem was solved by Robert Beaubien in another forum as I noted above. The problem occurs when Turn Office Plug-in On is checked in Norton Antivirus 2006. I recently upgraded to Norton 2006. I turned that option off, restarted it and Access and the ODBC connections work fine. I don't know how Robert discovered this solution but he does good work.

Troy

|||

Thanks all,

Turning off the office plugin did it. go to proformance then to spyware then to configure then to misc and uncheck the box.

Preston

|||

Add ODBCAD32.exe to Norton Internet Security 2007 personal firewall list - program control.

It works.