Cannot edit MS Access tables that are linked to SQL Server 2000

I am not able to edit tables through MS Access grid that is linked to sql
server 2000. I am able to edit some tables but not all.
Can you please tell me how I could change this so that I am able to edit all
the tables.
Thanks
> I am not able to edit tables through MS Access grid that is linked to sql
> server 2000. I am able to edit some tables but not all.
> Can you please tell me how I could change this so that I am able to edit
all
> the tables.
I guess you don't have Primary Key constraints on tables you cannot modify.
Check this first.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Cannot edit MS Access tables that are linked to SQL Server 2000

I am not able to edit tables through MS Access grid that is linked to sql
server 2000. I am able to edit some tables but not all.
Can you please tell me how I could change this so that I am able to edit all
the tables.
Thanks> I am not able to edit tables through MS Access grid that is linked to sql
> server 2000. I am able to edit some tables but not all.
> Can you please tell me how I could change this so that I am able to edit
all
> the tables.
I guess you don't have Primary Key constraints on tables you cannot modify.
Check this first.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Cannot edit MS Access tables that are linked to SQL Server 2000

I am not able to edit tables through MS Access grid that is linked to sql
server 2000. I am able to edit some tables but not all.
Can you please tell me how I could change this so that I am able to edit all
the tables.
Thanks> I am not able to edit tables through MS Access grid that is linked to sql
> server 2000. I am able to edit some tables but not all.
> Can you please tell me how I could change this so that I am able to edit
all
> the tables.
I guess you don't have Primary Key constraints on tables you cannot modify.
Check this first.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Cannot create snapshot

I'm trying to get a successful subscription push going on my database
publisher after having to alter a couple of tables. After making my
changes, I tried to start the snapshot agent. Normally, I see the
snapshot being created, which takes a few moments. In this case, all
I saw was the old snapshot, created successfully, which was probably
the one I initially did a month ago. I tried to go ahead and push my
subscription, but the status shows as "Deactivated". My question is,
how do I re-push my subscription to my backup database
machine/database?
You can reinitialize the subscription and then it'll pick up the new
snapshot.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Cannot create or modify tables in any database ("The parameter is incorrect.")

Using SQL Server 2005 and Microsoft SQL Server Management Studio, I connect using windows authentication. On any database I have created or attached (from a previous version of SQL Server), if I try to add a new table or modify an existing table, I get an error. A dialog pops up that says, "The parameter is incorrect." And I realy can't go any further.

I tried to remedy the problem by applying SP1 (x64) to the installation but I still get the same error.

Has anybody got a clue as to why I get this error? This is not SQL Server Express but the standard version of SQL Server. Fabulous software, I just can't get this to work on this new server. SQL Server Express is wonderful on our test machines but we need to get past this issue to fully utilize the server version.

Thanks for any help somebody can give me.

Please file a defect report on http://connect.microsoft.com/sqlserver. Defects reported on the Connect site go directly into our internal issue tracking system.

The error message pop-up should have a button/icon at bottom to show technical details. This includes the detailed exception message and the call stack. This can help us diagnose the problem. Be sure to include the version number of management studio and the engine (use menu Help | About to see the management studio version, and use this T-SQL to get the engine version: select @.@.version) and the call stack from the error dialog in the defect report.

One thing I would check is whether the database you've attached has a valid owner in the new server. If the database doesn't have an owner, you can give it one using the database properties dialog in management studio (connect to the server, right click on the database in Object Explorer, and select Properties. The owner can be set on the Files page.) or by issuing the "ALTER AUTHORIZATION ON DATABASE::{your db} TO {server principal}" statement in sqlcmd or a management studio query window.

You also need fairly high privileges to modify tables. Can you modify tables if you log in as a database owner?

Thanks,
Steve

Cannot create DSN to SQL Express

I installed SQL Express and was able to create a new DB using Management Studio. I was able to copy over tables from another DB using DTS, but strangely the Native Client would not work I had to use OLE for SQL Server.

But I am unable to create a DSN to the database using ODBC Administrator. I keep getting Login Timeout expired error message, using both Native Client and SQL Server drivers. What am I missing?

Thanks

Mark

WHat is the exact error message you are getting ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

This is the error message when I try using Native Client:

Attempting connection
[Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
[Microsoft][SQL Native Client]Login timeout expired
[Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

This when I try SQL Server driver:

Attempting connection
[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.

BTW this is a local connection, I'm trying to get it all working on my laptop. I also tried playing with the SQLEXPRESS and CLient Protocols. I enabled Shared Memory, Named Pipes and TCP/IP, to no avail.

Thanks

Mark

|||

Finallly I managed to get it to work, when I realized the ODBC Administrator does not display the full name of the server. It displayed MARK_LAP, which is the name of my computer. I realized I needed to type in MARK_LAP\SQLEXPRESS which is the name of the server, and it works now. Connecting to [local] does not work either.

So, I thought I would post this in case someone else has the same problem.

Mark

|||The statement "MARK_LAP\SQLEXPRESS which is the name of the server" is a bit misleading as this is the name of the named instance which the service uses. YOu always have to name the instance with the "\instanceName" snippet if its not a default instance. Otherwise the client tries to connect to a default instance which is probably not installed in all cases.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Cannot create DSN to SQL Express

I installed SQL Express and was able to create a new DB using Management Studio. I was able to copy over tables from another DB using DTS, but strangely the Native Client would not work I had to use OLE for SQL Server.

But I am unable to create a DSN to the database using ODBC Administrator. I keep getting Login Timeout expired error message, using both Native Client and SQL Server drivers. What am I missing?

Thanks

Mark

WHat is the exact error message you are getting ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

This is the error message when I try using Native Client:

Attempting connection
[Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
[Microsoft][SQL Native Client]Login timeout expired
[Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

This when I try SQL Server driver:

Attempting connection
[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.

BTW this is a local connection, I'm trying to get it all working on my laptop. I also tried playing with the SQLEXPRESS and CLient Protocols. I enabled Shared Memory, Named Pipes and TCP/IP, to no avail.

Thanks

Mark

|||

Finallly I managed to get it to work, when I realized the ODBC Administrator does not display the full name of the server. It displayed MARK_LAP, which is the name of my computer. I realized I needed to type in MARK_LAP\SQLEXPRESS which is the name of the server, and it works now. Connecting to [local] does not work either.

So, I thought I would post this in case someone else has the same problem.

Mark

|||The statement "MARK_LAP\SQLEXPRESS which is the name of the server" is a bit misleading as this is the name of the named instance which the service uses. YOu always have to name the instance with the "\instanceName" snippet if its not a default instance. Otherwise the client tries to connect to a default instance which is probably not installed in all cases.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Cannot create ADO connection to DB anymore...

I rebuilt my machine a few weeks back - re-installed the developer version...

The app in question can see all it's tables in the SQL back end via ODBC and
everything looks normal via Enterprise Manager, but I can no longer create
an ADO Connection.

Source code is unchanged, what's changed is the PC rebuild and consequent
re-installatin of SQL Server developer version.

I suspect it's something to do with the provider (at least that's the only thing
I can see in the .Connect string that's not verifiably correct...)

The error looks like this after I trap and format it:

--------------------
12/30/03 21:34:30v0.71 Userid: UPQC on SAG
Proc: basADO: ADO_ConnectionCreate
-2147467259: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
not exist or access denied.
Errors encountered when trying to connect:
'''[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied.

----Connect String:
Provider=SQLOLEDB.1;SERVER=SAG;DATABASE=TRETS;UID= Trets;PWD=trets
--------------------

Can anybody see anything obvious to look for?

I'm thinking I need to know how to verify/validate the presence of provider
"SQLOLEDB.1"... But how?
--
PeteCresswellHi

Maybe it's a SID type problem! Have you tried a trusted connection?

John

"(Pete Cresswell)" <x@.y.z> wrote in message
news:lkd4vv89m3cdb5fnlt4rt661c47sb56jdv@.4ax.com...
> I rebuilt my machine a few weeks back - re-installed the developer
version...
> The app in question can see all it's tables in the SQL back end via ODBC
and
> everything looks normal via Enterprise Manager, but I can no longer create
> an ADO Connection.
> Source code is unchanged, what's changed is the PC rebuild and consequent
> re-installatin of SQL Server developer version.
> I suspect it's something to do with the provider (at least that's the only
thing
> I can see in the .Connect string that's not verifiably correct...)
> The error looks like this after I trap and format it:
> --------------------
> 12/30/03 21:34:30v0.71 Userid: UPQC on SAG
> Proc: basADO: ADO_ConnectionCreate
> -2147467259: [DBNETLIB][ConnectionOpen (Connect()).]SQL
Server does
> not exist or access denied.
> Errors encountered when trying to connect:
> '''[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist
or
> access denied.
> ----Connect String:
> Provider=SQLOLEDB.1;SERVER=SAG;DATABASE=TRETS;UID= Trets;PWD=trets
> --------------------
>
> Can anybody see anything obvious to look for?
> I'm thinking I need to know how to verify/validate the presence of
provider
> "SQLOLEDB.1"... But how?
> --
> PeteCresswell

Cannot Create a Relationship

I have three tables imported from Access that had relationships with
eachother and had Cascading Deletes set to TRUE. The tables related in a
simple manner.
tblPackingList.PackingListID to tblPackingListPart.PackingListID
tblPackingListPart.PackingListPartID to
tblPackingListPart.PackingListPartSerialID.
For some reason, I cannot establish the relationship between
tblPackingList.PackingListID and tblPackingListPart.PackingListID. I get the
following error.
tblPackingListPart' table
- Unable to create relationship 'FK_tblPackingListPart_tblPackingList'.
Introducing FOREIGN KEY constraint 'FK_tblPackingListPart_tblPackingList' on
table 'tblPackingListPart' may cause cycles or multiple cascade paths.
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN
KEY constraints.
Could not create constraint. See previous errors.
What I did was delete the relationships from each table. The primary key for
each table is set to be Unique. The following keys are primary.
tblPackingList.PackingListID
tblPackingListPart.PackingListPartID
tblPackingListPartSerial.PackingListPartSerialID
I need the relationship between tblPackingList and tblPackingListPart to
Cascade Delete in the event the master record in tblPackingList is deleted,
but no matter what I do I cannot get he relationship to save. I keep getting
the above error no matter what I do. What must I do to resolve what appears
to me to be a bug. Or, am I missing something.
Thanks.
P.S. My apologies if this is posted under the wrong section, but I have no
idea what would be the appropriate area to post a question such as this.
Thanks.
Hello,
To understand the issue better, will you try to create three new tables
with similar schema to see if you could reproduce the issue. If so, will
you provide the exact script so that I may reproduce the issue on my side.
I tried the following script but I was not able to reproduce the problem.
=====
create table tblPackingList (PackingListID int primary key, packinglistname
varchar(20))
create table tblPackingListPart (PackingListPartID int primary key,
PackingListID int)
create table tblPackingListPartSerial (PackingListPartSerialID int primary
key, PackingListPartID int)
alter table tblpackingListPart add constraint fk_1 Foreign key
(PackingListID) references tblPackingList(PackingListID) on delete cascade
alter table tblpackingListPartSerial add constraint fk_2 Foreign key
(PackingListPartID) references tblPackingListPart(PackingListPartID) on
delete cascade
=====
Also, you may want to refer to the following article for more details:
321843Error message 1785 occurs when you create a FOREIGN KEY constraint
that may cause multiple cascade paths
http://support.microsoft.com/default.aspx?scid=kb;EN-US;321843
Please let's know if you have other related information. I'm looking
forward to your reply. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Cannot copy indexes w 2005 Import/Export Wizard

In SQL 2000, the 'Copy SQL Server Objects' task allows 'copy indexes' under
'Advanced Copy Options' when transferring tables. But I cannot see where the
SQL 2005 Import and Export Wizard allows this option. Is there an easy way
to do this (I hope) ?
Hello Mark,
You could check "Transfer SQL server objects" task in SSIS project in
VS.net 2005. DTS is ugpraded to SSIS in SQL 2005.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Cannot copy indexes w 2005 Import/Export Wizard
>thread-index: AcYnif7U0qKGdOuRRFuiyisljvKhWg==
>X-WBNR-Posting-Host: 67.96.43.11
>From: "=?Utf-8?B?TWFya0dzY2g=?=" <markgsch@.noemail.noemail>
>Subject: Cannot copy indexes w 2005 Import/Export Wizard
>Date: Wed, 1 Feb 2006 15:48:26 -0800
>Lines: 4
>Message-ID: <D1072BA0-E4FF-4E06-8F4C-018F334730F9@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:419786
>X-Tomcat-NG: microsoft.public.sqlserver.server
>In SQL 2000, the 'Copy SQL Server Objects' task allows 'copy indexes'
under
>'Advanced Copy Options' when transferring tables. But I cannot see where
the
>SQL 2005 Import and Export Wizard allows this option. Is there an easy
way
>to do this (I hope) ?
>

cannot connect to sql server 2005 from xp client when trying to create ODBC dsn

Newbie here,

I am trying to link tables from an access 2003 frontend to sql server 2005 backend.

I am doing this in a vmware test environment. I am using vmware server and running sbs 2003 and xp sp2 client. Both virtaul machine can talk to each other(using local host connection).

I have tried to create a dsn to the sql server but i cannot connect. I can ping the sbs server through the command interface but the error i get when i try to connect is :

Connection failed:
Sql state: hyt00
sqlserver error: 0
microsoft odbc sql server driver timeout expired.

spent a day trying various combinations - still no joy.

Any help would be much appreciatedvirtual machines are nothing but trouble. open a query window and execute SELECT @.@.SERVERNAME|||Thank you for your reply,

I would agree vmware is not the best scenario - but as i am new to sql server - and i am about to upsize an access mdb backend to sql server 2005 so I thought some sort of test environment was required. I didn't fancy multiple installs on our production sbs until i got it right.

So from your post - is it possible to create a linked table between access 2003 and sql server 2005 from an access 2003 query window? If so, what would the syntax be if the sbs server was called 'sbs1' and the database on the sql server's default instance was named 'db1'? Or was your code to simply test the connectivity of the sql server?

The ultimate goal is a distributed access mde residing on xp sp2 clients created from an access mdb stored on a restricted network share that has linked tables to sql server 2005.

I do not even wish to go down the dsn road - would prefer the dsn-less road if possible - but i cannot proceed much further with that until i learn more about server logins, user id's and application roles etc and how passwords and user id's will be transferred accross network or stored on local machines. Hence I'm starting with dsn's and testing how to link the tables.

Any advice/guidance would be appreciated.|||I was eventually able to connect using named pipes - syntax had eluded me until i got lucky - server name 'sbs1' and pipe name was 'sbs1'.

So thats one hurdle - the next is which dsn settings to use - which server logins to use and which server roles to use.

The ultimate goal is a distributed access mde residing on xp sp2 clients created from an access mdb stored on a restricted network share that has linked tables to sql server 2005. Approx 4 users will have the abilty to link and import tables from sql server - all other users will be using an mde.

Any advice?

Cannot connect to server

Hi there.

I've been doing this SQLEXPRESS stuff peacefully for many months now and just yesterday I saved some tables in it. This morning my computer acts just weird. We have thunderstorm in the area and the sky is dark-maybe it is the reason but I do have a UPS that is supposed to smooth things out.

Anyhow, when I tried to connect I got rejected. The connection is local, on the same computer, same had disk. I haven't done anything to change the parameters. I have been saving the whole Server comprehensively but neglected to do it for the past 3-4 days perhaps.

What can I do? I do not even know how I can restore the databases if I cannot get in?

Shared Memory Provider, error 40 - Could not open a connection to SQL Server (Microsoft SQL Server, Error 2).

Thanks.

I had experienced a problem and here is my story. Our network admin in Sweden changed the network settings. Basically it shouldn't affect our daily process, however the SQL server doesn't work. At that time, I also uses the local connection and very standard settings but just cannot connect to SQL server. Then I reinstalled SQL server Express and attach my database files and it worked. No data was lost luckily. I guess when the thunderstorm came, you network have been changed (for example: reset). Please try to reinstall and attach database, hope it helps.

Br,

L

|||

Thank you L,

Actually the problem resolved itself a few minutes after I did posting. I could not delete the post on time and this is why. It turns out when I turned my computer on this morning MS started downloading 9 updates into my XP. I was not aware of it and was angry that my applications could not really start. CPU was 100% busy.

After the SQL Server fiasco I decided to turn the computer off and that was when it began installing them all. It took almost an hour and my computer is very fast. After it finished I restarted it and SQL Server responded immediately.

Apparently when the updates were put in but not installed yet it created a conflict. I was really scared.

- Alex