addlogin into linked server

Hi,
I need to develop a stored procedure to add or drop SQL server logins into
SQL servers based on some logics users give to me. My stored procedure is on
one server. I need to add logins to other servers through linked server.
Does anybody know how to run sp_addlogin to add SQL logins into linked serve
r?
Thanks a lot.BF (BF@.discussions.microsoft.com) writes:
> I need to develop a stored procedure to add or drop SQL server logins
> into SQL servers based on some logics users give to me. My stored
> procedure is on one server. I need to add logins to other servers
> through linked server.
> Does anybody know how to run sp_addlogin to add SQL logins into linked
> server?
Did you try
EXEC SOMESERVER.master.dbo.sp_addlogin=
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I just tried
exec wsdev.master.dbo.sp_addlogin 'test', 'test'
After this, I checked and saw the new login has been created. But I got the
following error message:
New login created.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should
be discarded.
I cannot use this in production system with error message like this. Any
further suggestions?
"Erland Sommarskog" wrote:

> BF (BF@.discussions.microsoft.com) writes:
> Did you try
> EXEC SOMESERVER.master.dbo.sp_addlogin=
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||BF (BF@.discussions.microsoft.com) writes:
> I just tried
> exec wsdev.master.dbo.sp_addlogin 'test', 'test'
> After this, I checked and saw the new login has been created. But I got
> the following error message:
> New login created.
> Msg 0, Level 11, State 0, Line 0
> A severe error occurred on the current command. The results, if any,
> should be discarded.
> I cannot use this in production system with error message like this. Any
> further suggestions?
I was not able to repeat this. Exactly which versions of SQL Server do
you have on the two servers? Use serverproperty('ProductVersion') to
determine this.
Anyway, the error message looks like you are running SQL 2005. In such
case you can use EXEC() AT:
EXEC ('EXEC master.dbo.sp_addlogin ''test'', ''test''') AT wsdev
If the target server is also running SQL 2005, you should not use
sp_addlogin at all, but rather CREATE LOGIN:
EXEC ('CREATE LOGIN test WITH PASSWORD = ''test''') AT wsdev
Note however, that this command will fail, because SQL 2005 validates
the password according to Windows policy. This mainly happens on SQL 2003,
but SQL 2005 always frowns at username = password.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I am using SQL Server 2000. The exact version is 8.00.760.
I cannot use EXEC AT on my server. But it's good to know this new feature on
SQL 2005.
Now I am thinking open 2 database connections from my .NET application to
access 2 different servers.
Thanks.
"Erland Sommarskog" wrote:

> BF (BF@.discussions.microsoft.com) writes:
> I was not able to repeat this. Exactly which versions of SQL Server do
> you have on the two servers? Use serverproperty('ProductVersion') to
> determine this.
> Anyway, the error message looks like you are running SQL 2005. In such
> case you can use EXEC() AT:
> EXEC ('EXEC master.dbo.sp_addlogin ''test'', ''test''') AT wsdev
> If the target server is also running SQL 2005, you should not use
> sp_addlogin at all, but rather CREATE LOGIN:
> EXEC ('CREATE LOGIN test WITH PASSWORD = ''test''') AT wsdev
> Note however, that this command will fail, because SQL 2005 validates
> the password according to Windows policy. This mainly happens on SQL 2003,
> but SQL 2005 always frowns at username = password.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||BF (BF@.discussions.microsoft.com) writes:
> I am using SQL Server 2000. The exact version is 8.00.760.
> I cannot use EXEC AT on my server. But it's good to know this new
> feature on SQL 2005.
> Now I am thinking open 2 database connections from my .NET application to
> access 2 different servers.
I assumed that you were on SQL 2005, because I took for granted that
you were running this from a query tool, which in that case would
be Management Studio which uses SqlClient.
Moral: please always be specific with the environment you are using.
As for the solution, this what I have recommended in the first place,
if I had known that you were doing this from an application. Relying
on linked servers is fragile, since the definition of a linked server
could change or disappear completely.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

AddlinkServer Problem with SQL Server 2003

Hello,
I have a laboratory server with SBS 2003 (Sql Server 2003), and I have 3
PC's linked to that server.
PC 1 and PC 2 has Sql Server 7.0 Personal Edition with SP4, the query to
link is:
exec sp_dropserver 'Server2003'
go
exec sp_addlinkedserver 'Server2003', 'Sql server'
go
exec sp_serveroption 'Server2003', 'collation compatible', true
go
exec sp_serveroption 'Server2003', 'data access', true
go
Runs OK, but when I run a query to data access to the linked server, sample:
select top 1 no_impuesto from Server2003.Tienda.dbo.CRIMP
--
result:
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
Note: I don't have any problems to link and access data with SBS 2000.
PC 3 Have Sql Server 2000 Personal Edition wiht SP4, the query to link is
the same avove,
the sample query to access data to the linked server,:
select top 1 no_impuesto from Server2003.Tienda.dbo.CRIMP
--
result:
no_impuesto
--
0
(1 row(s) affected)
Works fine.
So, I need upgrade SBS 2000 Production Server to SBS 2003 Server, but the
app uses in some cases linked server to data access,
and all the Work Stations have W2000 Pro with SQL Server 7.0 SP4.
Any help would greatly be appreciated.
RobertoI'm sorry, the laboratory server with SBS 2003 have Sql Server 2000 sp3.
Any help would greatly be appreciated.
Roberto
"Roberto C. Hernandez Bernal" <rhb@.microsis.com.mx> wrote in message
news:uUyt3yHCFHA.1524@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a laboratory server with SBS 2003 (Sql Server 2003), and I have 3
> PC's linked to that server.
> PC 1 and PC 2 has Sql Server 7.0 Personal Edition with SP4, the query to
> link is:
> exec sp_dropserver 'Server2003'
> go
> exec sp_addlinkedserver 'Server2003', 'Sql server'
> go
> exec sp_serveroption 'Server2003', 'collation compatible', true
> go
> exec sp_serveroption 'Server2003', 'data access', true
> go
> Runs OK, but when I run a query to data access to the linked server,
sample:
> select top 1 no_impuesto from Server2003.Tienda.dbo.CRIMP
> --
> result:
> Server: Msg 17, Level 16, State 1, Line 1
> SQL Server does not exist or access denied.
> Note: I don't have any problems to link and access data with SBS 2000.
> PC 3 Have Sql Server 2000 Personal Edition wiht SP4, the query to link is
> the same avove,
> the sample query to access data to the linked server,:
> select top 1 no_impuesto from Server2003.Tienda.dbo.CRIMP
> --
> result:
> no_impuesto
> --
> 0
> (1 row(s) affected)
> Works fine.
> So, I need upgrade SBS 2000 Production Server to SBS 2003 Server, but the
> app uses in some cases linked server to data access,
> and all the Work Stations have W2000 Pro with SQL Server 7.0 SP4.
> Any help would greatly be appreciated.
> Roberto
>

AddlinkServer Problem with SQL Server 2003

Hello,
I have a laboratory server with SBS 2003 (Sql Server 2003), and I have 3
PC's linked to that server.
PC 1 and PC 2 has Sql Server 7.0 Personal Edition with SP4, the query to
link is:
exec sp_dropserver 'Server2003'
go
exec sp_addlinkedserver 'Server2003', 'Sql server'
go
exec sp_serveroption 'Server2003', 'collation compatible', true
go
exec sp_serveroption 'Server2003', 'data access', true
go
Runs OK, but when I run a query to data access to the linked server, sample:
select top 1 no_impuesto from Server2003.Tienda.dbo.CRIMP
result:
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
Note: I don't have any problems to link and access data with SBS 2000.
PC 3 Have Sql Server 2000 Personal Edition wiht SP4, the query to link is
the same avove,
the sample query to access data to the linked server,:
select top 1 no_impuesto from Server2003.Tienda.dbo.CRIMP
result:
no_impuesto
0
(1 row(s) affected)
Works fine.
So, I need upgrade SBS 2000 Production Server to SBS 2003 Server, but the
app uses in some cases linked server to data access,
and all the Work Stations have W2000 Pro with SQL Server 7.0 SP4.
Any help would greatly be appreciated.
Roberto
I'm sorry, the laboratory server with SBS 2003 have Sql Server 2000 sp3.
Any help would greatly be appreciated.
Roberto
"Roberto C. Hernandez Bernal" <rhb@.microsis.com.mx> wrote in message
news:uUyt3yHCFHA.1524@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a laboratory server with SBS 2003 (Sql Server 2003), and I have 3
> PC's linked to that server.
> PC 1 and PC 2 has Sql Server 7.0 Personal Edition with SP4, the query to
> link is:
> exec sp_dropserver 'Server2003'
> go
> exec sp_addlinkedserver 'Server2003', 'Sql server'
> go
> exec sp_serveroption 'Server2003', 'collation compatible', true
> go
> exec sp_serveroption 'Server2003', 'data access', true
> go
> Runs OK, but when I run a query to data access to the linked server,
sample:
> select top 1 no_impuesto from Server2003.Tienda.dbo.CRIMP
> --
> result:
> Server: Msg 17, Level 16, State 1, Line 1
> SQL Server does not exist or access denied.
> Note: I don't have any problems to link and access data with SBS 2000.
> PC 3 Have Sql Server 2000 Personal Edition wiht SP4, the query to link is
> the same avove,
> the sample query to access data to the linked server,:
> select top 1 no_impuesto from Server2003.Tienda.dbo.CRIMP
> --
> result:
> no_impuesto
> --
> 0
> (1 row(s) affected)
> Works fine.
> So, I need upgrade SBS 2000 Production Server to SBS 2003 Server, but the
> app uses in some cases linked server to data access,
> and all the Work Stations have W2000 Pro with SQL Server 7.0 SP4.
> Any help would greatly be appreciated.
> Roberto
>
sql

addling linked server

Hi ,
how do i actually created a linked server in Enterprise
Manager ?
go to the Linked Server , do i choose impersonation ,
no security context or the remote user
usin impersonation means the other server needs to set
up as allowing impersonation
using the remote user means i'll need to create a login
over at the other side ?
have tried but still cannot connect
i have used the Query Analyzer though it like this
sp_addlinkedserver 'dbABC' and it says added
when i run query analyzer to get records using
dbABC.server123.dbo.xxx it says "General network
error ..."
appreciate any advise
thks & rdgs
Hate to say this, but..
You need to do some more reading on Linked Servers.
There are a lot of little items that need to be checked when you create a
linked server.
The server name, provider name (MSDAORA for example), the data source,
provider string.. catalog)
The second step in the process is to determine your security settings.. Use
sp_addlinkedsrvlgoin.
Once you gotten through the readings on those and linking server in general,
you should have a lot better idea of how it is done.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:05f501c4917d$3a0c4a40$a401280a@.phx.gbl...
> Hi ,
> how do i actually created a linked server in Enterprise
> Manager ?
>
> go to the Linked Server , do i choose impersonation ,
> no security context or the remote user
> usin impersonation means the other server needs to set
> up as allowing impersonation
> using the remote user means i'll need to create a login
> over at the other side ?
> have tried but still cannot connect
>
> i have used the Query Analyzer though it like this
> sp_addlinkedserver 'dbABC' and it says added
> when i run query analyzer to get records using
> dbABC.server123.dbo.xxx it says "General network
> error ..."
> appreciate any advise
> thks & rdgs
|||What is the target for the linked server? Is it SQL Server? Depending on
what security context you need to have the queries run on your linked
server target, you can select 1 of the 4 options on the security tab. Click
the Help button (in SQL Enterprise Manager -> Configure Linked Server's
Security tab) to see what each of these options means.
The easiest 1 of the 4 options you can use is the last one "Be made using
this security context". This option requires you to setup a login on the
linked server target and provide the login name and password in the linked
server configuration dialog in Enterprise Manager. (Keep in mind that if
you're using SQL Server as the targer for your linked server, choosing this
option will mean that you have to configure your target SQL Server to be
configured for SQL and Windows Authentication).
Refer to the following topic for additional information :
http://msdn.microsoft.com/library/de...us/adminsql/ad
_1_server_24tv.asp
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
sql

addling linked server

Hi ,
how do i actually created a linked server in Enterprise
Manager ?
go to the Linked Server , do i choose impersonation ,
no security context or the remote user
usin impersonation means the other server needs to set
up as allowing impersonation
using the remote user means i'll need to create a login
over at the other side ?
have tried but still cannot connect
i have used the Query Analyzer though it like this
sp_addlinkedserver 'dbABC' and it says added
when i run query analyzer to get records using
dbABC.server123.dbo.xxx it says "General network
error ..."
appreciate any advise
thks & rdgsHate to say this, but..
You need to do some more reading on Linked Servers.
There are a lot of little items that need to be checked when you create a
linked server.
The server name, provider name (MSDAORA for example), the data source,
provider string.. catalog)
The second step in the process is to determine your security settings.. Use
sp_addlinkedsrvlgoin.
Once you gotten through the readings on those and linking server in general,
you should have a lot better idea of how it is done.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:05f501c4917d$3a0c4a40$a401280a@.phx.gbl...
> Hi ,
> how do i actually created a linked server in Enterprise
> Manager ?
>
> go to the Linked Server , do i choose impersonation ,
> no security context or the remote user
> usin impersonation means the other server needs to set
> up as allowing impersonation
> using the remote user means i'll need to create a login
> over at the other side ?
> have tried but still cannot connect
>
> i have used the Query Analyzer though it like this
> sp_addlinkedserver 'dbABC' and it says added
> when i run query analyzer to get records using
> dbABC.server123.dbo.xxx it says "General network
> error ..."
> appreciate any advise
> thks & rdgs|||What is the target for the linked server? Is it SQL Server? Depending on
what security context you need to have the queries run on your linked
server target, you can select 1 of the 4 options on the security tab. Click
the Help button (in SQL Enterprise Manager -> Configure Linked Server's
Security tab) to see what each of these options means.
The easiest 1 of the 4 options you can use is the last one "Be made using
this security context". This option requires you to setup a login on the
linked server target and provide the login name and password in the linked
server configuration dialog in Enterprise Manager. (Keep in mind that if
you're using SQL Server as the targer for your linked server, choosing this
option will mean that you have to configure your target SQL Server to be
configured for SQL and Windows Authentication).
Refer to the following topic for additional information :
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_1_server_24tv.asp
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

addling linked server

Hi ,
how do i actually created a linked server in Enterprise
Manager ?
go to the Linked Server , do i choose impersonation ,
no security context or the remote user
usin impersonation means the other server needs to set
up as allowing impersonation
using the remote user means i'll need to create a login
over at the other side ?
have tried but still cannot connect
i have used the Query Analyzer though it like this
sp_addlinkedserver 'dbABC' and it says added
when i run query analyzer to get records using
dbABC.server123.dbo.xxx it says "General network
error ..."
appreciate any advise
thks & rdgsHate to say this, but..
You need to do some more reading on Linked Servers.
There are a lot of little items that need to be checked when you create a
linked server.
The server name, provider name (MSDAORA for example), the data source,
provider string.. catalog)
The second step in the process is to determine your security settings.. Use
sp_addlinkedsrvlgoin.
Once you gotten through the readings on those and linking server in general,
you should have a lot better idea of how it is done.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:05f501c4917d$3a0c4a40$a401280a@.phx.gbl...
> Hi ,
> how do i actually created a linked server in Enterprise
> Manager ?
>
> go to the Linked Server , do i choose impersonation ,
> no security context or the remote user
> usin impersonation means the other server needs to set
> up as allowing impersonation
> using the remote user means i'll need to create a login
> over at the other side ?
> have tried but still cannot connect
>
> i have used the Query Analyzer though it like this
> sp_addlinkedserver 'dbABC' and it says added
> when i run query analyzer to get records using
> dbABC.server123.dbo.xxx it says "General network
> error ..."
> appreciate any advise
> thks & rdgs|||What is the target for the linked server? Is it SQL Server? Depending on
what security context you need to have the queries run on your linked
server target, you can select 1 of the 4 options on the security tab. Click
the Help button (in SQL Enterprise Manager -> Configure Linked Server's
Security tab) to see what each of these options means.
The easiest 1 of the 4 options you can use is the last one "Be made using
this security context". This option requires you to setup a login on the
linked server target and provide the login name and password in the linked
server configuration dialog in Enterprise Manager. (Keep in mind that if
you're using SQL Server as the targer for your linked server, choosing this
option will mean that you have to configure your target SQL Server to be
configured for SQL and Windows Authentication).
Refer to the following topic for additional information :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
_1_server_24tv.asp
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Adding users to linked server

Is there a certain way of adding users to access a linked server? I
linked to an Oracle server but somehow, I'm unable to add any users,
even after trying in EM. It'll allow me to select the local account,
enter a remote userid/password, click apply save, but doesn't seem
to hold the info.

Please help!

Thanks,
DannyHi Danny,

Might not be much help as I am not 100% sure I know what you want to
do but have you looked at sp_addlinkedsrvlogin?

I use this to create logins for accessing on Oracle instance.

Cheers,

Paulsql

adding security to a linked table

Can someone tell me how to go about adding security to a field in a linked table so that only certain people can access and modify the information? Thank you in advance to anybody who can help me out here!

Use a VIEW, actually 2 views. One for the hoi poloi, and the other for the chosen few. That is the 'best' option.

However, you can use column level permissions in the linked table if necessary. (Something about your question leads me to believe that you are using Access, in which case, my response may be totally 'full of beans'.)

|||I am using Access 2002 on the front end, sorry I thought I had mentioned that.|||Can someone please help explain how to go about doing this? I also need to modify field properties on the access end of a linked table. I applied the field property changes I wanted to the table in SQL server 2005, but am at a loss when changing them in access. I keep getting a message saying I can't save changes to a linked table. Is there a way for the changes made to the table in SQL to cascade down to the same table in access? Otherwise, how should I go about doing this? Thanks in advance!|||Can someone provide where to find more information about adding column level permissions in a linked table? I am using SQL server 2005 as a back end and Access 2002 as a front end. I added a new column to an existing table and now I want to add permissions to only this new column. Thank you much!|||

I still think that the best solution is to create an updatable VIEW (WITH VIEW_METADATA) that does not include the column in question. Those that should not access the column are provided permissions for the view, those that need access to the column are provided access to another VIEW that includes the column, or to the underlaying table.

For column level permissions, the specific Topic in Books Online is: GRANT Object Permissions (Transact-SQL). However, I recommend serious consideration of the VIEW option -it will be so much easier to maintain.

You might also find this thread useful. Or this thread.

adding security to a linked table

Can someone tell me how to go about adding security to a field in a linked table so that only certain people can access and modify the information? Thank you in advance to anybody who can help me out here!

Use a VIEW, actually 2 views. One for the hoi poloi, and the other for the chosen few. That is the 'best' option.

However, you can use column level permissions in the linked table if necessary. (Something about your question leads me to believe that you are using Access, in which case, my response may be totally 'full of beans'.)

|||I am using Access 2002 on the front end, sorry I thought I had mentioned that.|||Can someone please help explain how to go about doing this? I also need to modify field properties on the access end of a linked table. I applied the field property changes I wanted to the table in SQL server 2005, but am at a loss when changing them in access. I keep getting a message saying I can't save changes to a linked table. Is there a way for the changes made to the table in SQL to cascade down to the same table in access? Otherwise, how should I go about doing this? Thanks in advance!|||Can someone provide where to find more information about adding column level permissions in a linked table? I am using SQL server 2005 as a back end and Access 2002 as a front end. I added a new column to an existing table and now I want to add permissions to only this new column. Thank you much!|||

I still think that the best solution is to create an updatable VIEW (WITH VIEW_METADATA) that does not include the column in question. Those that should not access the column are provided permissions for the view, those that need access to the column are provided access to another VIEW that includes the column, or to the underlaying table.

For column level permissions, the specific Topic in Books Online is:GRANT Object Permissions (Transact-SQL). However, I recommend serious consideration of the VIEW option -it will be so much easier to maintain.

You might also find this thread useful. Or this thread.

adding odbc to linked server

I have a odbc driver to a customized text file system (on aix), I am using
winsql to query from that sysem, does sql server allow to register it as a
linked server. I am guessing i should be able to register as i have odbc
driver to the system. Please suggest me if it is possible
When i try to do it, i am getting message like i mentioned here
Error 7399: OLE DB provider 'MSDASQL' reported an error.
Client unable to establish connection error:1408F0C6:SSL3_GET_RECORD: packet
length too long]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ],
Thanks,
Subbu.
Hi
ODBC datasource use the MSDASQL provider and require a System DSN. This
error can occur if it is a User DSN. See sp_addlinkedserver in books online
for an example. If you still have problems you may want to turn ODBC tracing
on to debug it.
John
"Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
news:%23XK7zvYzEHA.576@.TK2MSFTNGP14.phx.gbl...
>I have a odbc driver to a customized text file system (on aix), I am using
> winsql to query from that sysem, does sql server allow to register it as a
> linked server. I am guessing i should be able to register as i have odbc
> driver to the system. Please suggest me if it is possible
> When i try to do it, i am getting message like i mentioned here
> Error 7399: OLE DB provider 'MSDASQL' reported an error.
> Client unable to establish connection error:1408F0C6:SSL3_GET_RECORD:
> packet
> length too long]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
> returned 0x80004005: ],
>
> Thanks,
> Subbu.
>
|||There is a problem with odbc driver after i corrected it i am able to
register as linked server without any errors, and i can see the list of
tables in enterprise manager, but I am not able to run a query , see below
for error message i am getting for a query
query:
select CLI_ID, CLIENT_NAME
from IMPACT...CLI WHERE CLI_ID = '00001083'
error message:
Server: Msg 306, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator.
Can you please give me more details how to to turn ODBC tracing on to debug.
Thanks,
Subbu.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:enu1IB0zEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Hi
> ODBC datasource use the MSDASQL provider and require a System DSN. This
> error can occur if it is a User DSN. See sp_addlinkedserver in books
online
> for an example. If you still have problems you may want to turn ODBC
tracing[vbcol=seagreen]
> on to debug it.
> John
> "Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
> news:%23XK7zvYzEHA.576@.TK2MSFTNGP14.phx.gbl...
using[vbcol=seagreen]
a
>
|||Hi
In the ODBC Data Source Manager applet (either in control panel or on
the Administrators menu), you will see a trace tab, with a button to
start tracing. At a guess CLI_ID is being interpreted as text, ntext
or image and that you need to specify CLI_ID LIKE '00001083'.
John
"Subbaiahd" <subbaiahd@.hotmail.com> wrote in message news:<u8FDdVX0EHA.2624@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> There is a problem with odbc driver after i corrected it i am able to
> register as linked server without any errors, and i can see the list of
> tables in enterprise manager, but I am not able to run a query , see below
> for error message i am getting for a query
> query:
> select CLI_ID, CLIENT_NAME
> from IMPACT...CLI WHERE CLI_ID = '00001083'
> error message:
> Server: Msg 306, Level 16, State 1, Line 1
> The text, ntext, and image data types cannot be compared or sorted,
> except when using IS NULL or LIKE operator.
>
> Can you please give me more details how to to turn ODBC tracing on to debug.
> Thanks,
> Subbu.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:enu1IB0zEHA.1452@.TK2MSFTNGP11.phx.gbl...
> online
> tracing
> using
> a
|||I started odbc tracing , but could not understand the log. If you want to
see i can paste its contents but the log file size is 2MB. I dont have a
clue to proceed further, can you please help me.
Query:
select CLI_ID, CLIENT_NAME
from IMPACT...CLI WHERE CLI_ID like '00001083'
Error message:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column 'CLI_ID' (compile-time
ordinal 1) of object 'CLI' was reported to have a DBCOLUMNFLAGS_ISLONG of
128 at compile time and 0 at run time].
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3b81e6a.0411231254.6732697d@.posting.google.co m...
> Hi
>
> In the ODBC Data Source Manager applet (either in control panel or on
> the Administrators menu), you will see a trace tab, with a button to
> start tracing. At a guess CLI_ID is being interpreted as text, ntext
> or image and that you need to specify CLI_ID LIKE '00001083'.
> John
>
> "Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
news:<u8FDdVX0EHA.2624@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
below[vbcol=seagreen]
debug.[vbcol=seagreen]
This[vbcol=seagreen]
it as[vbcol=seagreen]
odbc[vbcol=seagreen]
error:1408F0C6:SSL3_GET_RECORD:[vbcol=seagreen]
IDBInitialize::Initialize[vbcol=seagreen]
|||Hi
Searching google for "OLE DB provider 'MSDASQL' supplied inconsistent
metadata for a column" turns up quite a few posts, so you can gain
solace in that you are not alone! If you ran the query without the
where clause does it still cause a problem? Suggestions from other
posts include using OPENQUERY or using the Oracle OLEDB driver
instead. This post points you to a KB article on the error and how you
may gain more information:
http://tinyurl.com/55x93
HTH
John
"Subbaiahd" <subbaiahd@.hotmail.com> wrote in message news:<#wvDYOk0EHA.3500@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> I started odbc tracing , but could not understand the log. If you want to
> see i can paste its contents but the log file size is 2MB. I dont have a
> clue to proceed further, can you please help me.
> Query:
> select CLI_ID, CLIENT_NAME
> from IMPACT...CLI WHERE CLI_ID like '00001083'
> Error message:
> Server: Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' supplied inconsistent metadata for a column.
> Metadata information was changed at execution time.
> OLE DB error trace [Non-interface error: Column 'CLI_ID' (compile-time
> ordinal 1) of object 'CLI' was reported to have a DBCOLUMNFLAGS_ISLONG of
> 128 at compile time and 0 at run time].
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3b81e6a.0411231254.6732697d@.posting.google.co m...
> news:<u8FDdVX0EHA.2624@.TK2MSFTNGP11.phx.gbl>...
> below
> debug.
> This
> online
> tracing
> using
> it as
> a
> odbc
> error:1408F0C6:SSL3_GET_RECORD:
> IDBInitialize::Initialize

Adding Linked server

Hi guys,
My linked server (from one SQL Server 2000 to another SQL Server 2000) is
not working. My settings are as follows:
Checked other data source
Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=TEST;
Under Security tab:
Be made using the logins current security context is checked.
Please help!
When you say it's not working , do you mean "insufficient permissions!? or
are you getting some othe rmessage?
Jack Vamvas
__________________________________________________ ________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"David" <David@.discussions.microsoft.com> wrote in message
news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
> Hi guys,
> My linked server (from one SQL Server 2000 to another SQL Server 2000) is
> not working. My settings are as follows:
> Checked other data source
> Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=TEST;
> Under Security tab:
> Be made using the logins current security context is checked.
>
> Please help!
>
|||It says, SQL Server does not exist or Access denied. Thanks
"Jack Vamvas" wrote:

> When you say it's not working , do you mean "insufficient permissions!? or
> are you getting some othe rmessage?
> --
> Jack Vamvas
> __________________________________________________ ________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "David" <David@.discussions.microsoft.com> wrote in message
> news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
>
>

Adding Linked server

Hi guys,
My linked server (from one SQL Server 2000 to another SQL Server 2000) is
not working. My settings are as follows:
Checked other data source
Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=
TE
ST;
Under Security tab:
Be made using the logins current security context is checked.
Please help!When you say it's not working , do you mean "insufficient permissions!? or
are you getting some othe rmessage?
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"David" <David@.discussions.microsoft.com> wrote in message
news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
> Hi guys,
> My linked server (from one SQL Server 2000 to another SQL Server 2000) is
> not working. My settings are as follows:
> Checked other data source
> Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=
TEST;
> Under Security tab:
> Be made using the logins current security context is checked.
>
> Please help!
>|||It says, SQL Server does not exist or Access denied. Thanks
"Jack Vamvas" wrote:

> When you say it's not working , do you mean "insufficient permissions!? or
> are you getting some othe rmessage?
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "David" <David@.discussions.microsoft.com> wrote in message
> news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
>
>

Adding Linked server

Hi guys,
My linked server (from one SQL Server 2000 to another SQL Server 2000) is
not working. My settings are as follows:
Checked other data source
Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=TEST;
Under Security tab:
Be made using the logins current security context is checked.
Please help!When you say it's not working , do you mean "insufficient permissions!? or
are you getting some othe rmessage?
--
Jack Vamvas
__________________________________________________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"David" <David@.discussions.microsoft.com> wrote in message
news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
> Hi guys,
> My linked server (from one SQL Server 2000 to another SQL Server 2000) is
> not working. My settings are as follows:
> Checked other data source
> Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=TEST;
> Under Security tab:
> Be made using the logins current security context is checked.
>
> Please help!
>|||It says, SQL Server does not exist or Access denied. Thanks
"Jack Vamvas" wrote:
> When you say it's not working , do you mean "insufficient permissions!? or
> are you getting some othe rmessage?
> --
> Jack Vamvas
> __________________________________________________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "David" <David@.discussions.microsoft.com> wrote in message
> news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
> > Hi guys,
> >
> > My linked server (from one SQL Server 2000 to another SQL Server 2000) is
> > not working. My settings are as follows:
> >
> > Checked other data source
> > Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=TEST;
> >
> > Under Security tab:
> > Be made using the logins current security context is checked.
> >
> >
> > Please help!
> >
>
>

Adding an additional Subscription to Merge Replication

I have two servers in a merge replication setup (linked every night using an ISDN line). I wish to add a new subscriber to this but I am not sure if I need to do a new snapshot.
I was thinking of using the latest backup from the publisher and recover this on the new subscriber - if I do this will i need
to run the snapshot again ? or will they all simply carry on with the merge...
Thanks
Gerry
you have to create a no-sync subcription which will run the snapshot only
for necessary merge replication metadata.
"Gerry" <anonymous@.discussions.microsoft.com> wrote in message
news:3490FE65-DF3F-401D-A6EE-78236E508677@.microsoft.com...
> I have two servers in a merge replication setup (linked every night using
an ISDN line). I wish to add a new subscriber to this but I am not sure if
I need to do a new snapshot.
> I was thinking of using the latest backup from the publisher and recover
this on the new subscriber - if I do this will i need
> to run the snapshot again ? or will they all simply carry on with the
merge...
> Thanks
> Gerry
|||How do I create/define a no-sync subscription ? Does it get defined when I run the
Push Subscription Wizard in the usual way?
Thanks
Gerry
|||You have to select an option that no data needs to be synchronized
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Gerry" <anonymous@.discussions.microsoft.com> wrote in message
news:0709ECB3-8396-4593-A423-93761B4137EE@.microsoft.com...
> How do I create/define a no-sync subscription ? Does it get defined when I
run the
> Push Subscription Wizard in the usual way?
> Thanks
> Gerry
>