Can't set Custom Conflict Resolver

Hello...
I have been trying to set and use a Custom Conflict Resolver for our Merge
Replication (SQL Server 2000) and when I access the Articles tab/page and
select the Resolver tab...all of the controls are disabled. Even when I
create a new publication, these controls are disabled.
I cannot find anything (so far) that helps me to get these Resolver controls
enabled so I can set the Conflict Resolver. Does anyone know how to
accomplish this?
Thanks
- will
Is this table(s) published in other merge publications? This will cause the
behavior you are seeing.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dw" <dw@.discussions.microsoft.com> wrote in message
news:256912E5-F012-4F57-B7E1-4184A523809F@.microsoft.com...
> Hello...
> I have been trying to set and use a Custom Conflict Resolver for our Merge
> Replication (SQL Server 2000) and when I access the Articles tab/page and
> select the Resolver tab...all of the controls are disabled. Even when I
> create a new publication, these controls are disabled.
> I cannot find anything (so far) that helps me to get these Resolver
controls
> enabled so I can set the Conflict Resolver. Does anyone know how to
> accomplish this?
> Thanks
> - will
|||Yes, those tables are in multiple merge publications. I guess I will delete
the publications, create a new one and then script it out (making changes as
necessary) for the other publications.
Thanks for your help.
- will
"Hilary Cotter" wrote:

> Is this table(s) published in other merge publications? This will cause the
> behavior you are seeing.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "dw" <dw@.discussions.microsoft.com> wrote in message
> news:256912E5-F012-4F57-B7E1-4184A523809F@.microsoft.com...
> controls
>
>

Can't see or access tables through linked server

Apparently I am doing something wrong and I don't know what!

I am needing to link a remote Oracle Server to SQL Server 2005.

I have (I think) followed the steps precisely to create the linked

server both by code or through the SQL Server Management Studio and I

get the same results eith way. I can see the linked server and there are no apparent errors but I can't see or access the tables from Oracle.

If I try a Select statement on a table from the linked server I get the

error "Msg 208, Level 16, Stat 1, Server {servername}, Line 1 Invalid

object name"

I am assuming this is a permissions issue but I don't see it. I

am an admin on both the SQL Server DB and the Oracle DB with full

access and I am using windows authentication.

I have mapped local logons to the remote logon and tried almost every

possible combination of security contexts and other users on the local

and linked server but I always get the same result. The Oracle server shows it is linked but I can't get to any table on the linked server.

Any help on this would be really appreciated. I have already spent two days on this.

Thanks in advance.

This kb should help.

support.microsoft.com/kb/280106

|||

I don't think this applies. This KB article only covers up to Windows 2000 and Oracle 8.i.

We have either 2003 server or XP (happening on two machines) and Oracle 10g.

Also, I am not getting any error messages as the article discusses, I just can't see the tables.

Thanks for the help.

sql

Cant See Network Server

Hi, I just installed SQL Server 2005 on Windows Server 2003 (Act as DNS and DHCP server for the network). When I tried to access it from my workstation (Windows XP Pro), using SQL Server Management Studio, theres no list of Network Server available. Anything that I should configure first?

Hi,

is SQL Server Browser started on the server ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Did you enable it for network listening? By default it is not in certain SKUs(IIRC developer and express do not listen by default).|||How to enable network listening? And.. Wheres SQL Server Browser?
Its Enterprise Edition..

|||Use the Surface Area Configuration Tool|||Yay!!! Thanks guys..

Cant run query today that ran yesterday

I converted an MS Access db to SQL Server 2005 Express yesterday. I used FullConvert Enterprise for the conversion and it worked great. I ran several queries and saved them, and they ran fine. Today, running the same queries, I get this error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tblTXMup'

I googled the message and found someone who had a similar problem and their answer was they were not the dbo. I checked the new database and it was owned by sa so I logged in as sa and got the same error.

Can anyone set me straight so I can get into this db?

Thanks,probably you are executing your query in the context of some other database, not the one you created yesterday.|||The error seems to suggest that the object that you referenced doesn't exist in the schema.

Regards,|||Thanks for looking at this. I only converted the one database so I know it is the right one. I can display the table I am trying to query, so I know I am logged in to the right one. The query was saved from yesterday and it got a result then but throws the error now.

Any other ideas?

Thanks,|||Sure,

Can you execute a query (or if you fancy, a DML statement for a change) against the table. It is important to differentiate between just seeing the table listed as a database object and the ability to execute a statement against it.

Regards,|||I can't execute a query because I get the error above, but I can not only see the table in the left panel of SQL Management Studio Express but can also display the table's data by right clicking on it and selecting "Open Table".|||OK. Something is not right here.

From what you say above, this is in no a security issue. When you right click on a table and click 'Open Table', the underlying event will just execute a SELECT statement against the table.

Therefore, you should be able to execute:

select * from tblTXMup

If you still receive the same message, try the following and let me know what the result is.

select
*
from
information_schema.tables
wehre
table_name = 'tblTXMup'

Regards,|||Robert,

Thanks again for helping. After running your query I got what looked like five column headers as follows: blank, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE

What does that tell us?|||What was the output returned for each query?

For the first, did you receive an error message, an empty result set, or a set of rows? And similarly for the second, did you receive a single row describing the table,or did you just receive the column headers, but no rows?

Regards,|||Thanks, Robert. From the first, that is the one that I started this thread with, and it still throws the same error. In the second, I got no error, did get headers, got no rows. The headers act like buttons that "depress" when clicked on.|||Could you please try to create the table under the same login account that from which you ran the above queries. At this stage, we wish to examine ownership configurations for the table and the databases that your account is accessing.

What may be happening is that you have two sessions open, each accessing a different database. Your first session may display the 'Welcome Page' to SQL Server 2005 management studio. When you browse the object tree, you select the appropriate database and then browse the object. Having selected 'DatabaseA' from the Object List, you see the results that you had expected.

On the other hand your second session (which appears as a new tab) has opened to an SQL Query interface, but the session is connected to a different database by default. This may well be the Master database. Consequently when you run the select query against the master database, SQL Server reports that your table does not exist.

You may just want to check which database your SQL Query interface session is connected to, and ensure that this is indeed the database that contains the table you wish to query. If this session appears to be accessing the correct database, then you can create a simple table from this session with the same name as the one you are trying to query, and note the outcome.

Once you have created this table, check to see that it appears in the database you expected it to, in the Object Browser. Once you have found it, compare this location to that of the original table that you mentioned in you first post, against which you were able to right click and select 'Open Table'.

The following code will create a simple one column table.

create table tblTXMup (id int);

Regards,|||Has it got anything to do with 3 part naming?
Objects that aren't given a 2nd part are assumed to be owned by "dbo"
i.e.

SELECT * FROM myTable
--equivalent to
SELECT * FROM dbo.myTable

Try running the following and let me know if you get any results

SELECT *
FROM sysobjects
WHERE type IN ('U', 'V')
AND name LIKE '%tblTXMup%'|||George, I ran your query and got a number of column headings but no rows returned. Headings started off name, id, xtype, uid, ...

Does that tell us anything?

Thanks for helping.|||Loyd,

Have you performed the steps that I mentioned in my most recent post? They are designed such that by the end, you should almost certainly know the answer to your problem.

The essence of the problem is this: We need to identify the a) where the table is, b) which account owns it, and most importantly, c) which account you are connected as when you are running these queries that we have supplied.|||OK, so you can open the table via enterprise manager, but can not get to it in Query Analyzer. That sums up the problem? From the results of the queries above, I suspect the two tools are looking at different databases. Run this query to see what database you are currently in in Query Analyzer:

select db_name()

Do you get the expected result?|||r123456 and MCrowley you are both on to something. When I created the table it appeared in the master system db. How can I move to or attach to the correct db?|||Exactly what I thought. You had two sessions open, each pointing to different databases.

I would attach a screen shot to explain, but I don't have SQL Server Management Studio installed. There is a drop down list on the tool bar, just above the query windows, which contains the list of databases that you're account has access to. The database name that appears selected in this list, is the one that all statements in the current session will be executed against, unless of course you explicitly define the database name in the query, which evidently you have not done on this occasion.

Once you've selected the session in which you want to run the SQL Query (not the one you used to browse the Object Tree), ensure that the value in this drop down list is the one for which you want your query to be executed against.

Regards,|||One note, putting user tables in the master database is generally a bad thing. Recovering the master database from backup requires a series of steps that include stopping and restarting the whole server several times. This gets to be annoying to anyone else who happens to be using the server at the time. I would suggest either redoing the upgrade to a user database (you can create one through Enterprise Manager easily enough), or exporting the data from master to a user database. The choice will depend on how many foreign keys you have created in there.|||Ah yes. I will second that suggestion. The number of times I have gone to execute a query, only to notice that the database is pointing to Master.

Another suggestion is to create administrator accounts that are a step down in terms of access rights from the DBO account. This is common practice in most organisations as it achieves two major benefits. Firstly, you have accountability for all access to the database if you configure access to SQL Server to be through Windows NT accounts. Secondly, you can ensure that users only have access to the databases that is permitted by your firm's access policy.

Regards,|||Thanks once more for all the assistance. I am finding out what a newbie I am with SQL Server. I have been submitting SQL queries to SQL Server for years, but have not had a great deal of experience with administering it.

I can submit a query by specifying the database, but the dropdown where I could specify a database to connect to is grayed out and I can't access it. What can I do in SQL Server Management Studio Express to get access to that dropdown?

BTW, as far as permissions, I am working on SQL Server on my own development machine, trying to get all the queries working before uploading to the host development site.

Thanks again|||Thanks once more for all the assistance. I am finding out what a newbie I am with SQL Server. I have been submitting SQL queries to SQL Server for years, but have not had a great deal of experience with administering it.

I can submit a query by specifying the database, but the dropdown where I could specify a database to connect to is grayed out and I can't access it. What can I do in SQL Server Management Studio Express to get access to that dropdown?

BTW, as far as permissions, I am working on SQL Server on my own development machine, trying to get all the queries working before uploading to the host development site.

Thanks again

Loyd,

You will need to ensure that you have the set the focus on an SQL Server query session, for it is these sessions that the drop down list applies to. When you want to access the Database name drop down list, first click anywhere in the session window (example: the window where you type the SQL queries) and then the drop down list become available.

It is unlikely that there are other reasons for the drop down list not being available for you to access.

Regards,

Can't Run Query Analyzer

When trying to access Query Analyzer from Enterprise Manager, I receive the
error, "The specified file was not found". I don't know what happened, as it
was working fine yesterday, and I've made no changes to the system in the
interim. SQL Server 2000 Standard Edition running under Win 2003 Server, sp1.
Any immediate thoughts would be welcome.Hi
You may want to check out http://tinyurl.com/85xwc, you could re-add it as
an external tool or re-install the client tools should fix it.
John
"Mike Kelly" <Mike Kelly@.discussions.microsoft.com> wrote in message
news:D0FE4485-A324-44C3-B4EC-59E996B84987@.microsoft.com...
> When trying to access Query Analyzer from Enterprise Manager, I receive
> the
> error, "The specified file was not found". I don't know what happened, as
> it
> was working fine yesterday, and I've made no changes to the system in the
> interim. SQL Server 2000 Standard Edition running under Win 2003 Server,
> sp1.
> Any immediate thoughts would be welcome.

Can't Run Query Analyzer

When trying to access Query Analyzer from Enterprise Manager, I receive the
error, "The specified file was not found". I don't know what happened, as it
was working fine yesterday, and I've made no changes to the system in the
interim. SQL Server 2000 Standard Edition running under Win 2003 Server, sp1
.
Any immediate thoughts would be welcome.Hi
You may want to check out http://tinyurl.com/85xwc, you could re-add it as
an external tool or re-install the client tools should fix it.
John
"Mike Kelly" <Mike Kelly@.discussions.microsoft.com> wrote in message
news:D0FE4485-A324-44C3-B4EC-59E996B84987@.microsoft.com...
> When trying to access Query Analyzer from Enterprise Manager, I receive
> the
> error, "The specified file was not found". I don't know what happened, as
> it
> was working fine yesterday, and I've made no changes to the system in the
> interim. SQL Server 2000 Standard Edition running under Win 2003 Server,
> sp1.
> Any immediate thoughts would be welcome.

Can't Run Query Analyzer

When trying to access Query Analyzer from Enterprise Manager, I receive the
error, "The specified file was not found". I don't know what happened, as it
was working fine yesterday, and I've made no changes to the system in the
interim. SQL Server 2000 Standard Edition running under Win 2003 Server, sp1.
Any immediate thoughts would be welcome.
Hi
You may want to check out http://tinyurl.com/85xwc, you could re-add it as
an external tool or re-install the client tools should fix it.
John
"Mike Kelly" <Mike Kelly@.discussions.microsoft.com> wrote in message
news:D0FE4485-A324-44C3-B4EC-59E996B84987@.microsoft.com...
> When trying to access Query Analyzer from Enterprise Manager, I receive
> the
> error, "The specified file was not found". I don't know what happened, as
> it
> was working fine yesterday, and I've made no changes to the system in the
> interim. SQL Server 2000 Standard Edition running under Win 2003 Server,
> sp1.
> Any immediate thoughts would be welcome.

Cant restore the database backup. Exclusive access could not be obtained

Hi everyone,

Hope somebody can help me on this.

I did a full BACKUP for two SQL databases using SQL Server Managament Studio. When trying to RESTORE the DBs, I get the following error:

"System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)"

There are NO users using the application. These are DBs for the Portfolio Server 2007 application.

After exploring a little bit, I found that there are several "SLEEPING" processes with an "AWATING COMMAND" flag. The processes come from the Portfolio Server Application, maybe from previous sessions.

Can they be deleted? if so, would there be any serious consequences?

Thank you for your help on this.

Oscar E.

Hi Oscar,

Take a look at this article, I think it will help.

Killing ProcessIDs using SMO
http://sqlblogcasts.com/blogs/seanprice/archive/2007/07/11/Killing-ProcessIDs-using-SMO.aspx

Mark.
|||Forcibly terminate all user processes as step 1 of your scheduled task - here's a stored proc that I got from somewhere that does this, for a named db:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--Type procedure
--author mak mak_999@.yahoo.com
--date written 4/19/2000
--project maintenance
--objective procedure to kill process for a given database

ALTER procedure usp_killprocess @.dbname varchar(128) as
set nocount on
set quoted_identifier off
declare @.kill_id int
declare @.query varchar(320)
declare killprocess_cursor cursor for
select a.spid from sysprocesses a join
sysdatabases b on a.dbid=b.dbid where b.name=@.dbname

open killprocess_cursor
fetch next from killprocess_cursor into @.kill_id
while(@.@.fetch_status =0)
begin
set @.query = 'kill '+ convert(varchar,@.kill_id)
exec (@.query)
fetch next from killprocess_cursor into @.kill_id
end
close killprocess_cursor
deallocate killprocess_cursor

--usage
--exec usp_killprocess "mydatabasename"

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

I use this as part of a scheduled backup live/restore to development (kill users before performing the restore) task and it works fine.

Cant Restore SQL Server databases: Exclusive access could not be obtained

Hi everyone,

Hope somebody can help me on this.

I did a full BACKUP for two SQL databases using SQL Server Managament Studio. When trying to RESTORE the DBs, I get the following error:

"System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)"

There are NO users using the application. These are DBs for the Portfolio Server 2007 application.

After exploring a little bit, I found that there are several "SLEEPING" processes with an "AWATING COMMAND" flag. The processes come from the Portfolio Server Application, maybe from previous sessions.

Can they be deleted? if so, would there be any serious consequences?

Thank you for your help on this.

Oscar E.

Hi Oscar,

you can go ahead and kill those process as they are no longer active.......those connections are inactive and shud not cause any harm to db ....

cool

|||

Thank you very much Deepak.

It worked.

Regards,

Oscar E.

Cant Restore SQL Server databases: Exclusive access could not be obtained

Hi everyone,

Hope somebody can help me on this.

I did a full BACKUP for two SQL databases using SQL Server Managament Studio. When trying to RESTORE the DBs, I get the following error:

"System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)"

There are NO users using the application. These are DBs for the Portfolio Server 2007 application.

After exploring a little bit, I found that there are several "SLEEPING" processes with an "AWATING COMMAND" flag. The processes come from the Portfolio Server Application, maybe from previous sessions.

Can they be deleted? if so, would there be any serious consequences?

Thank you for your help on this.

Oscar E.

Hi Oscar,

you can go ahead and kill those process as they are no longer active.......those connections are inactive and shud not cause any harm to db ....

cool

|||

Thank you very much Deepak.

It worked.

Regards,

Oscar E.

Cant Restore SQL Server databases: Exclusive access could not be obtained

Hi everyone,

Hope somebody can help me on this.

I did a full BACKUP for two SQL databases using SQL Server Managament Studio. When trying to RESTORE the DBs, I get the following error:

"System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)"

There are NO users using the application. These are DBs for the Portfolio Server 2007 application.

After exploring a little bit, I found that there are several "SLEEPING" processes with an "AWATING COMMAND" flag. The processes come from the Portfolio Server Application, maybe from previous sessions.

Can they be deleted? if so, would there be any serious consequences?

Thank you for your help on this.

Oscar E.

Hi Oscar,

you can go ahead and kill those process as they are no longer active.......those connections are inactive and shud not cause any harm to db ....

cool

|||

Thank you very much Deepak.

It worked.

Regards,

Oscar E.

Can't restore database

I'm trying to restore a I keep getting this message:
"exclusive access could not be obtained because the
database is in use. RESTORE DATABASE is terminating
abnormally."
How can I force a restore. I don't mind if I have to
drop the database and restore the data, but even if I get
the same message when I try to drop the database. Please
help.Try executing sp_who2 to identify the database connection(s) using the
database. You can kill these if needed. Also, check the database context
of your restore command to ensure it is not in the target database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Vic" <vduran@.specpro-inc.com> wrote in message
news:1373e01c3c1ed$e621a380$a601280a@.phx.gbl...
> I'm trying to restore a I keep getting this message:
> "exclusive access could not be obtained because the
> database is in use. RESTORE DATABASE is terminating
> abnormally."
> How can I force a restore. I don't mind if I have to
> drop the database and restore the data, but even if I get
> the same message when I try to drop the database. Please
> help.|||Hi ,
Please schedule the below commands in your job , so as all the process
connetced to your database will be killed and then restore starts.
use master
go
declare @.x varchar(255)
set @.x=''
select @.x = @.x + ' kill ' + convert(varchar(5), spid)
from master.dbo.sysprocesses
where db_name(dbid) = 'pubs'
exec (@.x)
go
restore database <dbname> from disk='c:\mssql\backup\dbname.bak'
(Once the restore starts no user is allowed to access the database.)
otherwise
1. Use the ALter Database command to start the database in single user mode
(Alter database dbname set SINGLE_USER)
2. Try Restoring
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ujsoKFfwDHA.3116@.tk2msftngp13.phx.gbl...
> Try executing sp_who2 to identify the database connection(s) using the
> database. You can kill these if needed. Also, check the database context
> of your restore command to ensure it is not in the target database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
> "Vic" <vduran@.specpro-inc.com> wrote in message
> news:1373e01c3c1ed$e621a380$a601280a@.phx.gbl...
> > I'm trying to restore a I keep getting this message:
> >
> > "exclusive access could not be obtained because the
> > database is in use. RESTORE DATABASE is terminating
> > abnormally."
> >
> > How can I force a restore. I don't mind if I have to
> > drop the database and restore the data, but even if I get
> > the same message when I try to drop the database. Please
> > help.
>

Can't remotely access SQL 2005 named instance

SuHi,
I just installed SQL 2005 Developer Edition on a brand new W2K3 w/ SP1
server. I installed a default instance, and then a named instance called
"DEV".
Initially, I could not access either instance from a remote machine. I went
into SQL Server Surface Area Configuration and enabled TCP/IP and Named Pipes
under the Remote Access portion of the dialog for BOTH instances.
I can access the default instance from a remote machine. I still cannot
access the named instance from a remote machine. I have triple checked all
my settings. They are exactly the same for both instances.
The error message I receive is:
26 - Error locating server/instance specified
I can't see the DEV instance in the SQL Server browse list either.
Can anyone help?
Is the SQL Server Browser service started on the server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ant-nee" <Antnee@.discussions.microsoft.com> wrote in message
news:2B8ED4DB-E631-4C73-9C06-072557EC7FAA@.microsoft.com...
> SuHi,
> I just installed SQL 2005 Developer Edition on a brand new W2K3 w/ SP1
> server. I installed a default instance, and then a named instance called
> "DEV".
> Initially, I could not access either instance from a remote machine. I went
> into SQL Server Surface Area Configuration and enabled TCP/IP and Named Pipes
> under the Remote Access portion of the dialog for BOTH instances.
> I can access the default instance from a remote machine. I still cannot
> access the named instance from a remote machine. I have triple checked all
> my settings. They are exactly the same for both instances.
> The error message I receive is:
> 26 - Error locating server/instance specified
> I can't see the DEV instance in the SQL Server browse list either.
> Can anyone help?
|||Thanks! That fixed the issue. You have helped me before, Tibor, and I thank
you again for your expertise. I may be a MCDBA, but I'm still a 2005 novice

I read a blurb somewhere that enabling the Browser service opens security
holes in SQL Server. Is this true, and if it is how does it indeed open
security holes?
- Anthony
"Tibor Karaszi" wrote:

> Is the SQL Server Browser service started on the server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ant-nee" <Antnee@.discussions.microsoft.com> wrote in message
> news:2B8ED4DB-E631-4C73-9C06-072557EC7FAA@.microsoft.com...
>
|||You're welcome Anthony. :-)

> I read a blurb somewhere that enabling the Browser service opens security
> holes in SQL Server. Is this true, and if it is how does it indeed open
> security holes?
Every surface you open opens increases the threat area. I don't know of any particular issues with
the SQL Browser. The same functionality existed in 2000, but it was built-in to the database engine.
To be more specific I guess we would have to see that comment.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ant-nee" <Antnee@.discussions.microsoft.com> wrote in message
news:B18F79B4-C704-4903-A98C-FC3D5F25323D@.microsoft.com...[vbcol=seagreen]
> Thanks! That fixed the issue. You have helped me before, Tibor, and I thank
> you again for your expertise. I may be a MCDBA, but I'm still a 2005 novice
>
> I read a blurb somewhere that enabling the Browser service opens security
> holes in SQL Server. Is this true, and if it is how does it indeed open
> security holes?
> - Anthony
> "Tibor Karaszi" wrote:
|||"Ant-nee" <Antnee@.discussions.microsoft.com> wrote in message
news:2B8ED4DB-E631-4C73-9C06-072557EC7FAA@.microsoft.com...
> SuHi,
> I just installed SQL 2005 Developer Edition on a brand new W2K3 w/ SP1
> server. I installed a default instance, and then a named instance called
> "DEV".
> Initially, I could not access either instance from a remote machine. I
> went
> into SQL Server Surface Area Configuration and enabled TCP/IP and Named
> Pipes
> under the Remote Access portion of the dialog for BOTH instances.
> I can access the default instance from a remote machine. I still cannot
> access the named instance from a remote machine. I have triple checked
> all
> my settings. They are exactly the same for both instances.
Have you tried the SQL Server Configuration Manager on the W2K machine.
This iw wher you open up the IP/Named pipes.

Can't remotely access SQL 2005 named instance

SuHi,
I just installed SQL 2005 Developer Edition on a brand new W2K3 w/ SP1
server. I installed a default instance, and then a named instance called
"DEV".
Initially, I could not access either instance from a remote machine. I went
into SQL Server Surface Area Configuration and enabled TCP/IP and Named Pipe
s
under the Remote Access portion of the dialog for BOTH instances.
I can access the default instance from a remote machine. I still cannot
access the named instance from a remote machine. I have triple checked all
my settings. They are exactly the same for both instances.
The error message I receive is:
26 - Error locating server/instance specified
I can't see the DEV instance in the SQL Server browse list either.
Can anyone help?Is the SQL Server Browser service started on the server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ant-nee" <Antnee@.discussions.microsoft.com> wrote in message
news:2B8ED4DB-E631-4C73-9C06-072557EC7FAA@.microsoft.com...
> SuHi,
> I just installed SQL 2005 Developer Edition on a brand new W2K3 w/ SP1
> server. I installed a default instance, and then a named instance called
> "DEV".
> Initially, I could not access either instance from a remote machine. I we
nt
> into SQL Server Surface Area Configuration and enabled TCP/IP and Named Pi
pes
> under the Remote Access portion of the dialog for BOTH instances.
> I can access the default instance from a remote machine. I still cannot
> access the named instance from a remote machine. I have triple checked al
l
> my settings. They are exactly the same for both instances.
> The error message I receive is:
> 26 - Error locating server/instance specified
> I can't see the DEV instance in the SQL Server browse list either.
> Can anyone help?|||Thanks! That fixed the issue. You have helped me before, Tibor, and I than
k
you again for your expertise. I may be a MCDBA, but I'm still a 2005 novice

I read a blurb somewhere that enabling the Browser service opens security
holes in SQL Server. Is this true, and if it is how does it indeed open
security holes?
- Anthony
"Tibor Karaszi" wrote:

> Is the SQL Server Browser service started on the server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ant-nee" <Antnee@.discussions.microsoft.com> wrote in message
> news:2B8ED4DB-E631-4C73-9C06-072557EC7FAA@.microsoft.com...
>|||You're welcome Anthony. :-)

> I read a blurb somewhere that enabling the Browser service opens security
> holes in SQL Server. Is this true, and if it is how does it indeed open
> security holes?
Every surface you open opens increases the threat area. I don't know of any
particular issues with
the SQL Browser. The same functionality existed in 2000, but it was built-in
to the database engine.
To be more specific I guess we would have to see that comment.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ant-nee" <Antnee@.discussions.microsoft.com> wrote in message
news:B18F79B4-C704-4903-A98C-FC3D5F25323D@.microsoft.com...[vbcol=seagreen]
> Thanks! That fixed the issue. You have helped me before, Tibor, and I th
ank
> you again for your expertise. I may be a MCDBA, but I'm still a 2005 novi
ce
>
> I read a blurb somewhere that enabling the Browser service opens security
> holes in SQL Server. Is this true, and if it is how does it indeed open
> security holes?
> - Anthony
> "Tibor Karaszi" wrote:
>|||"Ant-nee" <Antnee@.discussions.microsoft.com> wrote in message
news:2B8ED4DB-E631-4C73-9C06-072557EC7FAA@.microsoft.com...
> SuHi,
> I just installed SQL 2005 Developer Edition on a brand new W2K3 w/ SP1
> server. I installed a default instance, and then a named instance called
> "DEV".
> Initially, I could not access either instance from a remote machine. I
> went
> into SQL Server Surface Area Configuration and enabled TCP/IP and Named
> Pipes
> under the Remote Access portion of the dialog for BOTH instances.
> I can access the default instance from a remote machine. I still cannot
> access the named instance from a remote machine. I have triple checked
> all
> my settings. They are exactly the same for both instances.
Have you tried the SQL Server Configuration Manager on the W2K machine.
This iw wher you open up the IP/Named pipes.

Can't remotely access SQL 2005 named instance

SuHi,
I just installed SQL 2005 Developer Edition on a brand new W2K3 w/ SP1
server. I installed a default instance, and then a named instance called
"DEV".
Initially, I could not access either instance from a remote machine. I went
into SQL Server Surface Area Configuration and enabled TCP/IP and Named Pipes
under the Remote Access portion of the dialog for BOTH instances.
I can access the default instance from a remote machine. I still cannot
access the named instance from a remote machine. I have triple checked all
my settings. They are exactly the same for both instances.
The error message I receive is:
26 - Error locating server/instance specified
I can't see the DEV instance in the SQL Server browse list either.
Can anyone help?Is the SQL Server Browser service started on the server?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ant-nee" <Antnee@.discussions.microsoft.com> wrote in message
news:2B8ED4DB-E631-4C73-9C06-072557EC7FAA@.microsoft.com...
> SuHi,
> I just installed SQL 2005 Developer Edition on a brand new W2K3 w/ SP1
> server. I installed a default instance, and then a named instance called
> "DEV".
> Initially, I could not access either instance from a remote machine. I went
> into SQL Server Surface Area Configuration and enabled TCP/IP and Named Pipes
> under the Remote Access portion of the dialog for BOTH instances.
> I can access the default instance from a remote machine. I still cannot
> access the named instance from a remote machine. I have triple checked all
> my settings. They are exactly the same for both instances.
> The error message I receive is:
> 26 - Error locating server/instance specified
> I can't see the DEV instance in the SQL Server browse list either.
> Can anyone help?|||Thanks! That fixed the issue. You have helped me before, Tibor, and I thank
you again for your expertise. I may be a MCDBA, but I'm still a 2005 novice
:)
I read a blurb somewhere that enabling the Browser service opens security
holes in SQL Server. Is this true, and if it is how does it indeed open
security holes?
- Anthony
"Tibor Karaszi" wrote:
> Is the SQL Server Browser service started on the server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ant-nee" <Antnee@.discussions.microsoft.com> wrote in message
> news:2B8ED4DB-E631-4C73-9C06-072557EC7FAA@.microsoft.com...
> > SuHi,
> >
> > I just installed SQL 2005 Developer Edition on a brand new W2K3 w/ SP1
> > server. I installed a default instance, and then a named instance called
> > "DEV".
> >
> > Initially, I could not access either instance from a remote machine. I went
> > into SQL Server Surface Area Configuration and enabled TCP/IP and Named Pipes
> > under the Remote Access portion of the dialog for BOTH instances.
> >
> > I can access the default instance from a remote machine. I still cannot
> > access the named instance from a remote machine. I have triple checked all
> > my settings. They are exactly the same for both instances.
> >
> > The error message I receive is:
> >
> > 26 - Error locating server/instance specified
> >
> > I can't see the DEV instance in the SQL Server browse list either.
> >
> > Can anyone help?
>|||You're welcome Anthony. :-)
> I read a blurb somewhere that enabling the Browser service opens security
> holes in SQL Server. Is this true, and if it is how does it indeed open
> security holes?
Every surface you open opens increases the threat area. I don't know of any particular issues with
the SQL Browser. The same functionality existed in 2000, but it was built-in to the database engine.
To be more specific I guess we would have to see that comment.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ant-nee" <Antnee@.discussions.microsoft.com> wrote in message
news:B18F79B4-C704-4903-A98C-FC3D5F25323D@.microsoft.com...
> Thanks! That fixed the issue. You have helped me before, Tibor, and I thank
> you again for your expertise. I may be a MCDBA, but I'm still a 2005 novice
> :)
> I read a blurb somewhere that enabling the Browser service opens security
> holes in SQL Server. Is this true, and if it is how does it indeed open
> security holes?
> - Anthony
> "Tibor Karaszi" wrote:
>> Is the SQL Server Browser service started on the server?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ant-nee" <Antnee@.discussions.microsoft.com> wrote in message
>> news:2B8ED4DB-E631-4C73-9C06-072557EC7FAA@.microsoft.com...
>> > SuHi,
>> >
>> > I just installed SQL 2005 Developer Edition on a brand new W2K3 w/ SP1
>> > server. I installed a default instance, and then a named instance called
>> > "DEV".
>> >
>> > Initially, I could not access either instance from a remote machine. I went
>> > into SQL Server Surface Area Configuration and enabled TCP/IP and Named Pipes
>> > under the Remote Access portion of the dialog for BOTH instances.
>> >
>> > I can access the default instance from a remote machine. I still cannot
>> > access the named instance from a remote machine. I have triple checked all
>> > my settings. They are exactly the same for both instances.
>> >
>> > The error message I receive is:
>> >
>> > 26 - Error locating server/instance specified
>> >
>> > I can't see the DEV instance in the SQL Server browse list either.
>> >
>> > Can anyone help?
>>|||"Ant-nee" <Antnee@.discussions.microsoft.com> wrote in message
news:2B8ED4DB-E631-4C73-9C06-072557EC7FAA@.microsoft.com...
> SuHi,
> I just installed SQL 2005 Developer Edition on a brand new W2K3 w/ SP1
> server. I installed a default instance, and then a named instance called
> "DEV".
> Initially, I could not access either instance from a remote machine. I
> went
> into SQL Server Surface Area Configuration and enabled TCP/IP and Named
> Pipes
> under the Remote Access portion of the dialog for BOTH instances.
> I can access the default instance from a remote machine. I still cannot
> access the named instance from a remote machine. I have triple checked
> all
> my settings. They are exactly the same for both instances.
Have you tried the SQL Server Configuration Manager on the W2K machine.
This iw wher you open up the IP/Named pipes.

cant register to server from client

When I register to SQL server 2000 on another machine, I
get message " SQL server doesn't exist or access is
denied. Connection open". The pinging of the two machines
is OK. And I checked the protocols of both machines were
the same. Tell me how to rectify this problem. ThanksAre you trying to register by IP or by Name? There is an entry for the
server in the Client Configuration Utility? There is an entry for the server
in the DNS or in your hosts file?
regards,
Srgio Monteiro - Brasil
www.sqlpass.org
"Lie" <anonymous@.discussions.microsoft.com> escreveu na mensagem
news:378201c3dcc8$bbe0d5f0$3101280a@.phx.gbl...

quote:

---

> When I register to SQL server 2000 on another machine, I
> get message " SQL server doesn't exist or access is
> denied. Connection open". The pinging of the two machines
> is OK. And I checked the protocols of both machines were
> the same. Tell me how to rectify this problem. Thanks

---

|||You need to retrieve the actual OS error to troubleshoot this problem.
Reply with the OS error returned while connecting using an ODBC test DSN.
Or test using ISQL.exe from a command prompt.
Example:
ISQL -Stcp:ServerNameHere -Usqluseridhere -Ppasswordhere -Q"select
@.@.version"
or
ISQL -Stcp:ServerNameHere -E -Q"select @.@.version"
Search the kb for the OS error returned.
Example:
10060 == Connection Timeout
10061 == Connection Refused. The server is not listening on TCP. This
could be caused if we fail to bind to the port.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Can't query linked Access database specified by UNC

Hello,
I link Access database to sql server 2000 with ole db 4 provider; tell it to
logon with username ADMIN, empty password.
If the database is on a local drive, everything works correctly, I see the
tables of the access database in EM, and I successfully run query like
select * from accessdb...table1
If the database is on a remote drive and is specified by UNC, I still see
the tables in EM when EM runs on the server; but when I run EM on another
machine, focusing the tables under the linked server returns error
0x80004005. Same when I try to run a query (even on the server):
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\server\share\accessdb.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
Sql server is running on domain administrative account. At the same time as
the message is issued, I can log on that account on the server, launch
Access, and successfully open that same remote database using the same UNC
path.
What gives?
thanks,
Vadim Rapp
Polyscience, Inc.The first is to do is ensure that the database is not being
opened exclusively by other users. If that's all fine then
the error is likely related to permissions.
The account accessing the database needs a minimum of
something similar to Create, Destroy, Read, Write (depending
on the OS) on the folder where the mdb and ldb lives so it
can work with the .ldb file. Those are the minimum
permissions needed in general but it depends upon what
version of Access, what version of MDAC, what OS.
If the permissions are correct, you need to drill down
further to the access control and make sure that the account
has change permissions on the folder where the ldb file
resides. Make sure these are correct by explicitly setting
the permissions for the account.
I've seen several places just use a specific folder for just
the mdb (and consequently the ldb) and give the account full
control over that folder to make things less convoluted in
terms of permissions and the requirement for the ldb file.
-Sue
On Mon, 17 Jan 2005 13:35:38 -0600, "Vadim Rapp"
<vrapp@.nospam.polyscience.com> wrote:
>Hello,
>I link Access database to sql server 2000 with ole db 4 provider; tell it to
>logon with username ADMIN, empty password.
>If the database is on a local drive, everything works correctly, I see the
>tables of the access database in EM, and I successfully run query like
>select * from accessdb...table1
>If the database is on a remote drive and is specified by UNC, I still see
>the tables in EM when EM runs on the server; but when I run EM on another
>machine, focusing the tables under the linked server returns error
>0x80004005. Same when I try to run a query (even on the server):
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: The Microsoft Jet database engine cannot
>open the file '\\server\share\accessdb.mdb'. It is already opened
>exclusively by another user, or you need permission to view its data.]
>OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80004005: ].
>Sql server is running on domain administrative account. At the same time as
>the message is issued, I can log on that account on the server, launch
>Access, and successfully open that same remote database using the same UNC
>path.
>What gives?
>
>thanks,
>Vadim Rapp
>Polyscience, Inc.|||Hello Sue:
You wrote on Tue, 18 Jan 2005 13:23:38 -0700:
SH> The first is to do is ensure that the database is not being
SH> opened exclusively by other users. If that's all fine then
SH> the error is likely related to permissions.
I thought that as well; that's why, as I wrote, I manually logged on the sql
server machine, with the same userid as sqlserver; started Access and opened
the same database successfully.
In other words, I manually did exactly what sql server couldn't do, with the
same login id on the same machine.
Vadim|||Yes...but you still need to check the permissions - the
whole ldb thing. And you need to make sure the permissions
are explicitly set. Logging on and starting access and
opening the database manually is not the same thing.
-Sue
On Tue, 18 Jan 2005 16:07:49 -0600, "Vadim Rapp"
<vrapp@.nospam.polyscience.com> wrote:
>Hello Sue:
>You wrote on Tue, 18 Jan 2005 13:23:38 -0700:
> SH> The first is to do is ensure that the database is not being
> SH> opened exclusively by other users. If that's all fine then
> SH> the error is likely related to permissions.
>I thought that as well; that's why, as I wrote, I manually logged on the sql
>server machine, with the same userid as sqlserver; started Access and opened
>the same database successfully.
>In other words, I manually did exactly what sql server couldn't do, with the
>same login id on the same machine.
>Vadim|||Not that it's to much help, but I've seen a similar issue with Excel files.
I never really found a solution other than having the files on the
SQLServer. I've also tried with various accounts that has full admin access
to the server, domain, local machine etc. but to no avail. I'd be happy to
know the solution/reason since it's a bit annoying that I have to put the
files on the SQLServer to make it work.
Regards
Steen
Vadim Rapp wrote:
> Hello Sue:
> You wrote on Tue, 18 Jan 2005 13:23:38 -0700:
>> The first is to do is ensure that the database is not being
>> opened exclusively by other users. If that's all fine then
>> the error is likely related to permissions.
> I thought that as well; that's why, as I wrote, I manually logged on
> the sql server machine, with the same userid as sqlserver; started
> Access and opened the same database successfully.
> In other words, I manually did exactly what sql server couldn't do,
> with the same login id on the same machine.
> Vadim|||Hello Steen:
You wrote in conference
microsoft.public.sqlserver.security,microsoft.public.sqlserver.server on
Fri, 21 Jan 2005 13:02:07 +0100:
SP> Not that it's to much help, but I've seen a similar issue with Excel
SP> files. I never really found a solution other than having the files on
SP> the SQLServer. I've also tried with various accounts that has full
SP> admin access to the server, domain, local machine etc. but to no avail.
SP> I'd be happy to know the solution/reason since it's a bit annoying that
SP> I have to put the files on the SQLServer to make it work.
the only good news is that, as I've found, it works correctly with SQL
Server 2005.
Vadim|||Hello Sue:
You wrote in conference
microsoft.public.sqlserver.security,microsoft.public.sqlserver.server on
Tue, 18 Jan 2005 16:11:38 -0700:
SH> Yes...but you still need to check the permissions - the
SH> whole ldb thing. And you need to make sure the permissions
SH> are explicitly set. Logging on and starting access and
SH> opening the database manually is not the same thing.
hmm... if I open the database manually, obviously, Access is creating the
ldb... thus, it can do it... How is it not the same thing?
And it looks like in sql server 2005 it works just fine.
Vadim|||SQL Server does not start up Access and then open up an mdb.
It accesses the mdb.
Just like it's different if you double click a file vs. open
an application and then do a File, Open to open the file.
-Sue
On Sat, 22 Jan 2005 23:24:46 -0600, "Vadim Rapp"
<vr@.myrealbox.nospam.com> wrote:
>Hello Sue:
>You wrote in conference
>microsoft.public.sqlserver.security,microsoft.public.sqlserver.server on
>Tue, 18 Jan 2005 16:11:38 -0700:
> SH> Yes...but you still need to check the permissions - the
> SH> whole ldb thing. And you need to make sure the permissions
> SH> are explicitly set. Logging on and starting access and
> SH> opening the database manually is not the same thing.
>hmm... if I open the database manually, obviously, Access is creating the
>ldb... thus, it can do it... How is it not the same thing?
>And it looks like in sql server 2005 it works just fine.
>Vadim|||Vadim,
That last example wasn't a good one. Nonetheless, SQL Server
doesn't start Access and then open a database.
Other than security issues, combinations of MDAC/Jet drivers
can also cause problems and you may want to check those. If
you feel certain that this is a bug in SQL Server, you can
open a case with product support. You will not be charged
for the incident if it is a bug.
-Sue
On Sat, 22 Jan 2005 23:24:46 -0600, "Vadim Rapp"
<vr@.myrealbox.nospam.com> wrote:
>Hello Sue:
>You wrote in conference
>microsoft.public.sqlserver.security,microsoft.public.sqlserver.server on
>Tue, 18 Jan 2005 16:11:38 -0700:
> SH> Yes...but you still need to check the permissions - the
> SH> whole ldb thing. And you need to make sure the permissions
> SH> are explicitly set. Logging on and starting access and
> SH> opening the database manually is not the same thing.
>hmm... if I open the database manually, obviously, Access is creating the
>ldb... thus, it can do it... How is it not the same thing?
>And it looks like in sql server 2005 it works just fine.
>Vadim|||one thing that worked for me on loading dumps from UNCs is that i had
to use an absolute UNC path and not the share name.
for example:
if i had a share named "share" on folder X on my C drive
rather than going to //myComputer/share
i had to go to //myComputer/C$/X
and everything worked fine...it is worth a shot.
hth,
hans
Sue Hoegemeier wrote:
> Vadim,
> That last example wasn't a good one. Nonetheless, SQL Server
> doesn't start Access and then open a database.
> Other than security issues, combinations of MDAC/Jet drivers
> can also cause problems and you may want to check those. If
> you feel certain that this is a bug in SQL Server, you can
> open a case with product support. You will not be charged
> for the incident if it is a bug.
> -Sue
> On Sat, 22 Jan 2005 23:24:46 -0600, "Vadim Rapp"
> <vr@.myrealbox.nospam.com> wrote:
> >Hello Sue:
> >You wrote in conference
>microsoft.public.sqlserver.security,microsoft.public.sqlserver.server
on
> >Tue, 18 Jan 2005 16:11:38 -0700:
> >
> > SH> Yes...but you still need to check the permissions - the
> > SH> whole ldb thing. And you need to make sure the permissions
> > SH> are explicitly set. Logging on and starting access and
> > SH> opening the database manually is not the same thing.
> >
> >hmm... if I open the database manually, obviously, Access is
creating the
> >ldb... thus, it can do it... How is it not the same thing?
> >
> >And it looks like in sql server 2005 it works just fine.
> >
> >Vadim

Can't query linked Access database specified by UNC

Hello,
I link Access database to sql server 2000 with ole db 4 provider; tell it to
logon with username ADMIN, empty password.
If the database is on a local drive, everything works correctly, I see the
tables of the access database in EM, and I successfully run query like
select * from accessdb...table1
If the database is on a remote drive and is specified by UNC, I still see
the tables in EM when EM runs on the server; but when I run EM on another
machine, focusing the tables under the linked server returns error
0x80004005. Same when I try to run a query (even on the server):
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\server\share\accessdb.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
Sql server is running on domain administrative account. At the same time as
the message is issued, I can log on that account on the server, launch
Access, and successfully open that same remote database using the same UNC
path.
What gives?
thanks,
Vadim Rapp
Polyscience, Inc.
The first is to do is ensure that the database is not being
opened exclusively by other users. If that's all fine then
the error is likely related to permissions.
The account accessing the database needs a minimum of
something similar to Create, Destroy, Read, Write (depending
on the OS) on the folder where the mdb and ldb lives so it
can work with the .ldb file. Those are the minimum
permissions needed in general but it depends upon what
version of Access, what version of MDAC, what OS.
If the permissions are correct, you need to drill down
further to the access control and make sure that the account
has change permissions on the folder where the ldb file
resides. Make sure these are correct by explicitly setting
the permissions for the account.
I've seen several places just use a specific folder for just
the mdb (and consequently the ldb) and give the account full
control over that folder to make things less convoluted in
terms of permissions and the requirement for the ldb file.
-Sue
On Mon, 17 Jan 2005 13:35:38 -0600, "Vadim Rapp"
<vrapp@.nospam.polyscience.com> wrote:

>Hello,
>I link Access database to sql server 2000 with ole db 4 provider; tell it to
>logon with username ADMIN, empty password.
>If the database is on a local drive, everything works correctly, I see the
>tables of the access database in EM, and I successfully run query like
>select * from accessdb...table1
>If the database is on a remote drive and is specified by UNC, I still see
>the tables in EM when EM runs on the server; but when I run EM on another
>machine, focusing the tables under the linked server returns error
>0x80004005. Same when I try to run a query (even on the server):
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: The Microsoft Jet database engine cannot
>open the file '\\server\share\accessdb.mdb'. It is already opened
>exclusively by another user, or you need permission to view its data.]
>OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80004005: ].
>Sql server is running on domain administrative account. At the same time as
>the message is issued, I can log on that account on the server, launch
>Access, and successfully open that same remote database using the same UNC
>path.
>What gives?
>
>thanks,
>Vadim Rapp
>Polyscience, Inc.
|||Hello Sue:
You wrote on Tue, 18 Jan 2005 13:23:38 -0700:
SH> The first is to do is ensure that the database is not being
SH> opened exclusively by other users. If that's all fine then
SH> the error is likely related to permissions.
I thought that as well; that's why, as I wrote, I manually logged on the sql
server machine, with the same userid as sqlserver; started Access and opened
the same database successfully.
In other words, I manually did exactly what sql server couldn't do, with the
same login id on the same machine.
Vadim
|||Yes...but you still need to check the permissions - the
whole ldb thing. And you need to make sure the permissions
are explicitly set. Logging on and starting access and
opening the database manually is not the same thing.
-Sue
On Tue, 18 Jan 2005 16:07:49 -0600, "Vadim Rapp"
<vrapp@.nospam.polyscience.com> wrote:

>Hello Sue:
>You wrote on Tue, 18 Jan 2005 13:23:38 -0700:
> SH> The first is to do is ensure that the database is not being
> SH> opened exclusively by other users. If that's all fine then
> SH> the error is likely related to permissions.
>I thought that as well; that's why, as I wrote, I manually logged on the sql
>server machine, with the same userid as sqlserver; started Access and opened
>the same database successfully.
>In other words, I manually did exactly what sql server couldn't do, with the
>same login id on the same machine.
>Vadim
|||Not that it's to much help, but I've seen a similar issue with Excel files.
I never really found a solution other than having the files on the
SQLServer. I've also tried with various accounts that has full admin access
to the server, domain, local machine etc. but to no avail. I'd be happy to
know the solution/reason since it's a bit annoying that I have to put the
files on the SQLServer to make it work.
Regards
Steen
Vadim Rapp wrote:
> Hello Sue:
> You wrote on Tue, 18 Jan 2005 13:23:38 -0700:
>
> I thought that as well; that's why, as I wrote, I manually logged on
> the sql server machine, with the same userid as sqlserver; started
> Access and opened the same database successfully.
> In other words, I manually did exactly what sql server couldn't do,
> with the same login id on the same machine.
> Vadim
|||Hello Steen:
You wrote in conference
microsoft.public.sqlserver.security,microsoft.publ ic.sqlserver.server on
Fri, 21 Jan 2005 13:02:07 +0100:
SP> Not that it's to much help, but I've seen a similar issue with Excel
SP> files. I never really found a solution other than having the files on
SP> the SQLServer. I've also tried with various accounts that has full
SP> admin access to the server, domain, local machine etc. but to no avail.
SP> I'd be happy to know the solution/reason since it's a bit annoying that
SP> I have to put the files on the SQLServer to make it work.
the only good news is that, as I've found, it works correctly with SQL
Server 2005.
Vadim
|||Hello Sue:
You wrote in conference
microsoft.public.sqlserver.security,microsoft.publ ic.sqlserver.server on
Tue, 18 Jan 2005 16:11:38 -0700:
SH> Yes...but you still need to check the permissions - the
SH> whole ldb thing. And you need to make sure the permissions
SH> are explicitly set. Logging on and starting access and
SH> opening the database manually is not the same thing.
hmm... if I open the database manually, obviously, Access is creating the
ldb... thus, it can do it... How is it not the same thing?
And it looks like in sql server 2005 it works just fine.
Vadim
|||SQL Server does not start up Access and then open up an mdb.
It accesses the mdb.
Just like it's different if you double click a file vs. open
an application and then do a File, Open to open the file.
-Sue
On Sat, 22 Jan 2005 23:24:46 -0600, "Vadim Rapp"
<vr@.myrealbox.nospam.com> wrote:

>Hello Sue:
>You wrote in conference
>microsoft.public.sqlserver.security,microsoft.pub lic.sqlserver.server on
>Tue, 18 Jan 2005 16:11:38 -0700:
> SH> Yes...but you still need to check the permissions - the
> SH> whole ldb thing. And you need to make sure the permissions
> SH> are explicitly set. Logging on and starting access and
> SH> opening the database manually is not the same thing.
>hmm... if I open the database manually, obviously, Access is creating the
>ldb... thus, it can do it... How is it not the same thing?
>And it looks like in sql server 2005 it works just fine.
>Vadim
|||Vadim,
That last example wasn't a good one. Nonetheless, SQL Server
doesn't start Access and then open a database.
Other than security issues, combinations of MDAC/Jet drivers
can also cause problems and you may want to check those. If
you feel certain that this is a bug in SQL Server, you can
open a case with product support. You will not be charged
for the incident if it is a bug.
-Sue
On Sat, 22 Jan 2005 23:24:46 -0600, "Vadim Rapp"
<vr@.myrealbox.nospam.com> wrote:

>Hello Sue:
>You wrote in conference
>microsoft.public.sqlserver.security,microsoft.pub lic.sqlserver.server on
>Tue, 18 Jan 2005 16:11:38 -0700:
> SH> Yes...but you still need to check the permissions - the
> SH> whole ldb thing. And you need to make sure the permissions
> SH> are explicitly set. Logging on and starting access and
> SH> opening the database manually is not the same thing.
>hmm... if I open the database manually, obviously, Access is creating the
>ldb... thus, it can do it... How is it not the same thing?
>And it looks like in sql server 2005 it works just fine.
>Vadim
|||one thing that worked for me on loading dumps from UNCs is that i had
to use an absolute UNC path and not the share name.
for example:
if i had a share named "share" on folder X on my C drive
rather than going to //myComputer/share
i had to go to //myComputer/C$/X
and everything worked fine...it is worth a shot.
hth,
hans
Sue Hoegemeier wrote:
> Vadim,
> That last example wasn't a good one. Nonetheless, SQL Server
> doesn't start Access and then open a database.
> Other than security issues, combinations of MDAC/Jet drivers
> can also cause problems and you may want to check those. If
> you feel certain that this is a bug in SQL Server, you can
> open a case with product support. You will not be charged
> for the incident if it is a bug.
> -Sue
> On Sat, 22 Jan 2005 23:24:46 -0600, "Vadim Rapp"
> <vr@.myrealbox.nospam.com> wrote:
>
>microsoft.public.sqlserver.security,microsoft.pub lic.sqlserver.server
on[vbcol=seagreen]
creating the[vbcol=seagreen]

Can't query linked Access database specified by UNC

Hello,
I link Access database to sql server 2000 with ole db 4 provider; tell it to
logon with username ADMIN, empty password.
If the database is on a local drive, everything works correctly, I see the
tables of the access database in EM, and I successfully run query like
select * from accessdb...table1
If the database is on a remote drive and is specified by UNC, I still see
the tables in EM when EM runs on the server; but when I run EM on another
machine, focusing the tables under the linked server returns error
0x80004005. Same when I try to run a query (even on the server):
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine can
not
open the file '\\server\share\accessdb.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
Sql server is running on domain administrative account. At the same time as
the message is issued, I can log on that account on the server, launch
Access, and successfully open that same remote database using the same UNC
path.
What gives?
thanks,
Vadim Rapp
Polyscience, Inc.The first is to do is ensure that the database is not being
opened exclusively by other users. If that's all fine then
the error is likely related to permissions.
The account accessing the database needs a minimum of
something similar to Create, Destroy, Read, Write (depending
on the OS) on the folder where the mdb and ldb lives so it
can work with the .ldb file. Those are the minimum
permissions needed in general but it depends upon what
version of Access, what version of MDAC, what OS.
If the permissions are correct, you need to drill down
further to the access control and make sure that the account
has change permissions on the folder where the ldb file
resides. Make sure these are correct by explicitly setting
the permissions for the account.
I've seen several places just use a specific folder for just
the mdb (and consequently the ldb) and give the account full
control over that folder to make things less convoluted in
terms of permissions and the requirement for the ldb file.
-Sue
On Mon, 17 Jan 2005 13:35:38 -0600, "Vadim Rapp"
<vrapp@.nospam.polyscience.com> wrote:

>Hello,
>I link Access database to sql server 2000 with ole db 4 provider; tell it t
o
>logon with username ADMIN, empty password.
>If the database is on a local drive, everything works correctly, I see the
>tables of the access database in EM, and I successfully run query like
>select * from accessdb...table1
>If the database is on a remote drive and is specified by UNC, I still see
>the tables in EM when EM runs on the server; but when I run EM on another
>machine, focusing the tables under the linked server returns error
>0x80004005. Same when I try to run a query (even on the server):
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: The Microsoft Jet database engine ca
nnot
>open the file '\\server\share\accessdb.mdb'. It is already opened
>exclusively by another user, or you need permission to view its data.]
>OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80004005: ].
>Sql server is running on domain administrative account. At the same time as
>the message is issued, I can log on that account on the server, launch
>Access, and successfully open that same remote database using the same UNC
>path.
>What gives?
>
>thanks,
>Vadim Rapp
>Polyscience, Inc.|||Hello Sue:
You wrote on Tue, 18 Jan 2005 13:23:38 -0700:
SH> The first is to do is ensure that the database is not being
SH> opened exclusively by other users. If that's all fine then
SH> the error is likely related to permissions.
I thought that as well; that's why, as I wrote, I manually logged on the sql
server machine, with the same userid as sqlserver; started Access and opened
the same database successfully.
In other words, I manually did exactly what sql server couldn't do, with the
same login id on the same machine.
Vadim|||Yes...but you still need to check the permissions - the
whole ldb thing. And you need to make sure the permissions
are explicitly set. Logging on and starting access and
opening the database manually is not the same thing.
-Sue
On Tue, 18 Jan 2005 16:07:49 -0600, "Vadim Rapp"
<vrapp@.nospam.polyscience.com> wrote:

>Hello Sue:
>You wrote on Tue, 18 Jan 2005 13:23:38 -0700:
> SH> The first is to do is ensure that the database is not being
> SH> opened exclusively by other users. If that's all fine then
> SH> the error is likely related to permissions.
>I thought that as well; that's why, as I wrote, I manually logged on the sq
l
>server machine, with the same userid as sqlserver; started Access and opene
d
>the same database successfully.
>In other words, I manually did exactly what sql server couldn't do, with th
e
>same login id on the same machine.
>Vadim|||Not that it's to much help, but I've seen a similar issue with Excel files.
I never really found a solution other than having the files on the
SQLServer. I've also tried with various accounts that has full admin access
to the server, domain, local machine etc. but to no avail. I'd be happy to
know the solution/reason since it's a bit annoying that I have to put the
files on the SQLServer to make it work.
Regards
Steen
Vadim Rapp wrote:
> Hello Sue:
> You wrote on Tue, 18 Jan 2005 13:23:38 -0700:
>
> I thought that as well; that's why, as I wrote, I manually logged on
> the sql server machine, with the same userid as sqlserver; started
> Access and opened the same database successfully.
> In other words, I manually did exactly what sql server couldn't do,
> with the same login id on the same machine.
> Vadim|||Hello Steen:
You wrote in conference
microsoft.public.sqlserver.security,microsoft.public.sqlserver.server on
Fri, 21 Jan 2005 13:02:07 +0100:
SP> Not that it's to much help, but I've seen a similar issue with Excel
SP> files. I never really found a solution other than having the files on
SP> the SQLServer. I've also tried with various accounts that has full
SP> admin access to the server, domain, local machine etc. but to no avail.
SP> I'd be happy to know the solution/reason since it's a bit annoying that
SP> I have to put the files on the SQLServer to make it work.
the only good news is that, as I've found, it works correctly with SQL
Server 2005.
Vadim|||Hello Sue:
You wrote in conference
microsoft.public.sqlserver.security,microsoft.public.sqlserver.server on
Tue, 18 Jan 2005 16:11:38 -0700:
SH> Yes...but you still need to check the permissions - the
SH> whole ldb thing. And you need to make sure the permissions
SH> are explicitly set. Logging on and starting access and
SH> opening the database manually is not the same thing.
hmm... if I open the database manually, obviously, Access is creating the
ldb... thus, it can do it... How is it not the same thing?
And it looks like in sql server 2005 it works just fine.
Vadim|||SQL Server does not start up Access and then open up an mdb.
It accesses the mdb.
Just like it's different if you double click a file vs. open
an application and then do a File, Open to open the file.
-Sue
On Sat, 22 Jan 2005 23:24:46 -0600, "Vadim Rapp"
<vr@.myrealbox.nospam.com> wrote:

>Hello Sue:
>You wrote in conference
>microsoft.public.sqlserver.security,microsoft.public.sqlserver.server on
>Tue, 18 Jan 2005 16:11:38 -0700:
> SH> Yes...but you still need to check the permissions - the
> SH> whole ldb thing. And you need to make sure the permissions
> SH> are explicitly set. Logging on and starting access and
> SH> opening the database manually is not the same thing.
>hmm... if I open the database manually, obviously, Access is creating the
>ldb... thus, it can do it... How is it not the same thing?
>And it looks like in sql server 2005 it works just fine.
>Vadim|||Vadim,
That last example wasn't a good one. Nonetheless, SQL Server
doesn't start Access and then open a database.
Other than security issues, combinations of MDAC/Jet drivers
can also cause problems and you may want to check those. If
you feel certain that this is a bug in SQL Server, you can
open a case with product support. You will not be charged
for the incident if it is a bug.
-Sue
On Sat, 22 Jan 2005 23:24:46 -0600, "Vadim Rapp"
<vr@.myrealbox.nospam.com> wrote:

>Hello Sue:
>You wrote in conference
>microsoft.public.sqlserver.security,microsoft.public.sqlserver.server on
>Tue, 18 Jan 2005 16:11:38 -0700:
> SH> Yes...but you still need to check the permissions - the
> SH> whole ldb thing. And you need to make sure the permissions
> SH> are explicitly set. Logging on and starting access and
> SH> opening the database manually is not the same thing.
>hmm... if I open the database manually, obviously, Access is creating the
>ldb... thus, it can do it... How is it not the same thing?
>And it looks like in sql server 2005 it works just fine.
>Vadim|||one thing that worked for me on loading dumps from UNCs is that i had
to use an absolute UNC path and not the share name.
for example:
if i had a share named "share" on folder X on my C drive
rather than going to //myComputer/share
i had to go to //myComputer/C$/X
and everything worked fine...it is worth a shot.
hth,
hans
Sue Hoegemeier wrote:
> Vadim,
> That last example wasn't a good one. Nonetheless, SQL Server
> doesn't start Access and then open a database.
> Other than security issues, combinations of MDAC/Jet drivers
> can also cause problems and you may want to check those. If
> you feel certain that this is a bug in SQL Server, you can
> open a case with product support. You will not be charged
> for the incident if it is a bug.
> -Sue
> On Sat, 22 Jan 2005 23:24:46 -0600, "Vadim Rapp"
> <vr@.myrealbox.nospam.com> wrote:
>
>microsoft.public.sqlserver.security,microsoft.public.sqlserver.server
on[vbcol=seagreen]
creating the[vbcol=seagreen]

Can't paste data

I am trying to migrate an Access database to SQL Express.I created the new database in SQL Express, and added the tables and columns.At this point, I have done so without defining any keys, indexes, or relationships.For most of the tables I was able to copy the data from Access and paste same into the SQL Express tables.

However, I am unable to create the last table, which is a link table to manage the many-to-many relationships.

The table has the following columns:

MediumCode (bigint, null)

ArtistCode (bigint, null)

SongCode (bigint, null)

TrackNumber (int, null)

Here is a sample of the data I am trying to paste:

1,180,204,1

2,2,45,1

3,3,80,1

4,4,30,1

5,5,22,1

6,6,108,1

When I perform the paste there is no error message.The data just doesn’t get added to the table.I don’t understand why the other tables worked and this one does not.I am trying to paste 85 rows. Any ideas?

Also I tried to do this as an INSERT query, but SQL Express does not like the following syntax:

INSERT INTO [Music].[dbo].[tblMediumDetails]

([MediumCode]

,[ArtistCode]

,[SongCode]

,[TrackNumber])

VALUES

(1, 180, 204, 1),

(2, 2, 45, 1),

(3, 3, 80, 1),

(4, 4, 30, 1),

(5, 5, 22, 1),

(6, 6, 108, 1)

Do I have to create an INSERT statement for each row? Is there a bulk load function available in SQL Express, or is that not available with the free version?

Thanks,

Robert

Hi,

you either have to create single insert statements or you define a Insert into statement with a following Select statement like:

INSERT INTO SomeTable
(
Columnlist
)
SELECT Columnlist
From Someothertable

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

Thanks for the quick reply. Now that I know what needs to be done, I can stop spinning my wheels.

Thanks,

Robert