AAArgh! sp_addlinkedserver giving me fits

I seem to be getting conflicting error messages from sp_addlinkedserver.
Here is the problem:
Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
@.Datasrc=N'ServerName\InstanceName',@.srvproduct=N' SQL Server'
gives me this:
Msg 15426, Level 16, State 1, Procedure sp_addlinkedserver, Line 44
You must specify a provider name with this set of properties.
OK, I'll specify a provider name:
Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
@.Datasrc=N'ServerName\InstanceName',@.srvproduct=N' SQL Server',
@.provider=N'SQLNCLI'
gives me this:
Msg 15428, Level 16, State 1, Procedure sp_addlinkedserver, Line 67
You cannot specify a provider or any properties for product 'SQL
Server'.
So I *must* specify a provider, and yet I *cannot* specify a provider?
OK, I'll take out the product name:
Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
@.Datasrc=N'ServerName\InstanceName',@.provider=N'SQ LNCLI'
gives:
Msg 15429, Level 16, State 1, Procedure sp_addlinkedserver, Line 72
'(null)' is an invalid product name.
So null is an invalid product name if the provider is SQLNCLI. (I also
tried SQLOLEDB since the server is SQL 2000.) That goes against the
sp_addlinkedserver doc in BOL, which shows in the third "row" of the
table under "Remarks" that for a specific instance, the product name
doesn't need to be provided.
If the doc is right, then why the error message 15429?
What gives here?
The underlying problem is that I am trying to set up a kind of alias for
a linked server, so that from the production server, I can get some data
from another (linked) production server; from the test server, I can get
the data from the first production server. The test server can't see
the second production server.
Maybe there is a better way to do this... but I don't see it. (I don't
have access to set up a real alias in the client network utility on the
production server.)
The target databases are SQL 2000.
Thanks for any help.
David Walker
David,
Here is what I do.
EXEC master.dbo.sp_addlinkedserver @.server = N'RamaLama',
@.srvproduct=N'SrvrName', @.provider=N'SQLOLEDB', @.datasrc=N'SrvrName',
@.catalog=N'databasename'
Note that I have the @.srvproduct and the @.datasrc set to the same value.
Once this resolved my problem, I did not keep fooling with it to see if
there was another way.
RLF
"DWalker" <none@.none.com> wrote in message
news:uXkDw8BuHHA.4972@.TK2MSFTNGP05.phx.gbl...
>I seem to be getting conflicting error messages from sp_addlinkedserver.
> Here is the problem:
> Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
> @.Datasrc=N'ServerName\InstanceName',@.srvproduct=N' SQL Server'
> gives me this:
> Msg 15426, Level 16, State 1, Procedure sp_addlinkedserver, Line 44
> You must specify a provider name with this set of properties.
> OK, I'll specify a provider name:
> Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
> @.Datasrc=N'ServerName\InstanceName',@.srvproduct=N' SQL Server',
> @.provider=N'SQLNCLI'
> gives me this:
> Msg 15428, Level 16, State 1, Procedure sp_addlinkedserver, Line 67
> You cannot specify a provider or any properties for product 'SQL
> Server'.
> So I *must* specify a provider, and yet I *cannot* specify a provider?
> OK, I'll take out the product name:
> Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
> @.Datasrc=N'ServerName\InstanceName',@.provider=N'SQ LNCLI'
> gives:
> Msg 15429, Level 16, State 1, Procedure sp_addlinkedserver, Line 72
> '(null)' is an invalid product name.
> So null is an invalid product name if the provider is SQLNCLI. (I also
> tried SQLOLEDB since the server is SQL 2000.) That goes against the
> sp_addlinkedserver doc in BOL, which shows in the third "row" of the
> table under "Remarks" that for a specific instance, the product name
> doesn't need to be provided.
> If the doc is right, then why the error message 15429?
> What gives here?
> The underlying problem is that I am trying to set up a kind of alias for
> a linked server, so that from the production server, I can get some data
> from another (linked) production server; from the test server, I can get
> the data from the first production server. The test server can't see
> the second production server.
> Maybe there is a better way to do this... but I don't see it. (I don't
> have access to set up a real alias in the client network utility on the
> production server.)
> The target databases are SQL 2000.
> Thanks for any help.
> David Walker
|||"Russell Fields" <russellfields@.nomail.com> wrote in news:#dNp7kCuHHA.576
@.TK2MSFTNGP03.phx.gbl:

> David,
> Here is what I do.
> EXEC master.dbo.sp_addlinkedserver @.server = N'RamaLama',
> @.srvproduct=N'SrvrName', @.provider=N'SQLOLEDB', @.datasrc=N'SrvrName',
> @.catalog=N'databasename'
> Note that I have the @.srvproduct and the @.datasrc set to the same value.
> Once this resolved my problem, I did not keep fooling with it to see if
> there was another way.
> RLF
But I need for the data source to have an instance name in one server, and
none in another server. @.srvproduct is supposed to be "SQL Server" (or
"Any" for some provider names).
I'm not sure how you got away with giving a server NAME as the name of the
product.
David
|||David,
Did Peter Yang's answer resolve things for you? Blank is good, but
@.srvproduct is whatever you say it is.
If, however, you say it is 'SQL Server', you are predefining that the
@.server must be a physical name (or instance name) of the server that you
are trying to reach. Since you want @.server to be an alias (an excellent
idea for deploying from a development to a production environment) then you
will user a provider. Since you are SQL 2000, I used SQLOLEDB.
Out of curiousity, did you try it?
RLF
"DWalker" <none@.none.com> wrote in message
news:%231JXBLPuHHA.2752@.TK2MSFTNGP06.phx.gbl...
> "Russell Fields" <russellfields@.nomail.com> wrote in news:#dNp7kCuHHA.576
> @.TK2MSFTNGP03.phx.gbl:
>
> But I need for the data source to have an instance name in one server, and
> none in another server. @.srvproduct is supposed to be "SQL Server" (or
> "Any" for some provider names).
> I'm not sure how you got away with giving a server NAME as the name of the
> product.
> David
>
|||"Russell Fields" <russellfields@.nomail.com> wrote in
news:udbmMvYuHHA.4540@.TK2MSFTNGP05.phx.gbl:

> David,
> Did Peter Yang's answer resolve things for you? Blank is good, but
> @.srvproduct is whatever you say it is.
> If, however, you say it is 'SQL Server', you are predefining that the
> @.server must be a physical name (or instance name) of the server that
> you are trying to reach. Since you want @.server to be an alias (an
> excellent idea for deploying from a development to a production
> environment) then you will user a provider. Since you are SQL 2000, I
> used SQLOLEDB.
> Out of curiousity, did you try it?
> RLF
Sorry, I was out for a while.
@.srvproduct can be anything, but has to be supplied, and if it's "SQL
Server" then it has to be a real name.... hmmmm...
I find that the only way I can connect to a remote SQL 2000 server from
SQL 2005 management studio is to use Peter's example, and supply '' or
'test' or something like that for the server name. If I use 'SQL
Server', then I get a "logon failed for user xyz" (message 18456).
I'll use 'sql' for the product name.
Thanks to you both, including Peter.
David Walker

AAArgh! sp_addlinkedserver giving me fits

I seem to be getting conflicting error messages from sp_addlinkedserver.
Here is the problem:
Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
@.Datasrc=N'ServerName\InstanceName',@.srv
product=N'SQL Server'
gives me this:
Msg 15426, Level 16, State 1, Procedure sp_addlinkedserver, Line 44
You must specify a provider name with this set of properties.
OK, I'll specify a provider name:
Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
@.Datasrc=N'ServerName\InstanceName',@.srv
product=N'SQL Server',
@.provider=N'SQLNCLI'
gives me this:
Msg 15428, Level 16, State 1, Procedure sp_addlinkedserver, Line 67
You cannot specify a provider or any properties for product 'SQL
Server'.
So I *must* specify a provider, and yet I *cannot* specify a provider?
OK, I'll take out the product name:
Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
@.Datasrc=N'ServerName\InstanceName',@.pro
vider=N'SQLNCLI'
gives:
Msg 15429, Level 16, State 1, Procedure sp_addlinkedserver, Line 72
'(null)' is an invalid product name.
So null is an invalid product name if the provider is SQLNCLI. (I also
tried SQLOLEDB since the server is SQL 2000.) That goes against the
sp_addlinkedserver doc in BOL, which shows in the third "row" of the
table under "Remarks" that for a specific instance, the product name
doesn't need to be provided.
If the doc is right, then why the error message 15429?
What gives here?
The underlying problem is that I am trying to set up a kind of alias for
a linked server, so that from the production server, I can get some data
from another (linked) production server; from the test server, I can get
the data from the first production server. The test server can't see
the second production server.
Maybe there is a better way to do this... but I don't see it. (I don't
have access to set up a real alias in the client network utility on the
production server.)
The target databases are SQL 2000.
Thanks for any help.
David WalkerDavid,
Here is what I do.
EXEC master.dbo.sp_addlinkedserver @.server = N'RamaLama',
@.srvproduct=N'SrvrName', @.provider=N'SQLOLEDB', @.datasrc=N'SrvrName',
@.catalog=N'databasename'
Note that I have the @.srvproduct and the @.datasrc set to the same value.
Once this resolved my problem, I did not keep fooling with it to see if
there was another way.
RLF
"DWalker" <none@.none.com> wrote in message
news:uXkDw8BuHHA.4972@.TK2MSFTNGP05.phx.gbl...
>I seem to be getting conflicting error messages from sp_addlinkedserver.
> Here is the problem:
> Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
> @.Datasrc=N'ServerName\InstanceName',@.srv
product=N'SQL Server'
> gives me this:
> Msg 15426, Level 16, State 1, Procedure sp_addlinkedserver, Line 44
> You must specify a provider name with this set of properties.
> OK, I'll specify a provider name:
> Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
> @.Datasrc=N'ServerName\InstanceName',@.srv
product=N'SQL Server',
> @.provider=N'SQLNCLI'
> gives me this:
> Msg 15428, Level 16, State 1, Procedure sp_addlinkedserver, Line 67
> You cannot specify a provider or any properties for product 'SQL
> Server'.
> So I *must* specify a provider, and yet I *cannot* specify a provider?
> OK, I'll take out the product name:
> Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
> @.Datasrc=N'ServerName\InstanceName',@.pro
vider=N'SQLNCLI'
> gives:
> Msg 15429, Level 16, State 1, Procedure sp_addlinkedserver, Line 72
> '(null)' is an invalid product name.
> So null is an invalid product name if the provider is SQLNCLI. (I also
> tried SQLOLEDB since the server is SQL 2000.) That goes against the
> sp_addlinkedserver doc in BOL, which shows in the third "row" of the
> table under "Remarks" that for a specific instance, the product name
> doesn't need to be provided.
> If the doc is right, then why the error message 15429?
> What gives here?
> The underlying problem is that I am trying to set up a kind of alias for
> a linked server, so that from the production server, I can get some data
> from another (linked) production server; from the test server, I can get
> the data from the first production server. The test server can't see
> the second production server.
> Maybe there is a better way to do this... but I don't see it. (I don't
> have access to set up a real alias in the client network utility on the
> production server.)
> The target databases are SQL 2000.
> Thanks for any help.
> David Walker|||"Russell Fields" <russellfields@.nomail.com> wrote in news:#dNp7kCuHHA.576
@.TK2MSFTNGP03.phx.gbl:

> David,
> Here is what I do.
> EXEC master.dbo.sp_addlinkedserver @.server = N'RamaLama',
> @.srvproduct=N'SrvrName', @.provider=N'SQLOLEDB', @.datasrc=N'SrvrName',
> @.catalog=N'databasename'
> Note that I have the @.srvproduct and the @.datasrc set to the same value.
> Once this resolved my problem, I did not keep fooling with it to see if
> there was another way.
> RLF
But I need for the data source to have an instance name in one server, and
none in another server. @.srvproduct is supposed to be "SQL Server" (or
"Any" for some provider names).
I'm not sure how you got away with giving a server NAME as the name of the
product.
David|||Hello David,
You may want to try the following statement to see if it works on your side:
EXEC sp_addlinkedserver
@.server='linkedservername',
@.srvproduct='',
@.provider='SQLNCLI',
@.datasrc='servername\instancename'
If you have any update, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||David,
Did Peter Yang's answer resolve things for you? Blank is good, but
@.srvproduct is whatever you say it is.
If, however, you say it is 'SQL Server', you are predefining that the
@.server must be a physical name (or instance name) of the server that you
are trying to reach. Since you want @.server to be an alias (an excellent
idea for deploying from a development to a production environment) then you
will user a provider. Since you are SQL 2000, I used SQLOLEDB.
Out of curiousity, did you try it?
RLF
"DWalker" <none@.none.com> wrote in message
news:%231JXBLPuHHA.2752@.TK2MSFTNGP06.phx.gbl...
> "Russell Fields" <russellfields@.nomail.com> wrote in news:#dNp7kCuHHA.576
> @.TK2MSFTNGP03.phx.gbl:
>
> But I need for the data source to have an instance name in one server, and
> none in another server. @.srvproduct is supposed to be "SQL Server" (or
> "Any" for some provider names).
> I'm not sure how you got away with giving a server NAME as the name of the
> product.
> David
>|||"Russell Fields" <russellfields@.nomail.com> wrote in
news:udbmMvYuHHA.4540@.TK2MSFTNGP05.phx.gbl:

> David,
> Did Peter Yang's answer resolve things for you? Blank is good, but
> @.srvproduct is whatever you say it is.
> If, however, you say it is 'SQL Server', you are predefining that the
> @.server must be a physical name (or instance name) of the server that
> you are trying to reach. Since you want @.server to be an alias (an
> excellent idea for deploying from a development to a production
> environment) then you will user a provider. Since you are SQL 2000, I
> used SQLOLEDB.
> Out of curiousity, did you try it?
> RLF
Sorry, I was out for a while.
@.srvproduct can be anything, but has to be supplied, and if it's "SQL
Server" then it has to be a real name.... hmmmm...
I find that the only way I can connect to a remote SQL 2000 server from
SQL 2005 management studio is to use Peter's example, and supply '' or
'test' or something like that for the server name. If I use 'SQL
Server', then I get a "logon failed for user xyz" (message 18456).
I'll use 'sql' for the product name.
Thanks to you both, including Peter.
David Walker

a user can't be deleted...

Hi,
I've just installed SQL Server 2005, and mistakenly add a user in
ServerName\Database\master; I added two kind of schema into that user.
But when I tried to delete the user, SQL Server 2005 tells me that database
user have schema assigned to it thus can't be remove.
Is there a way to do delete it?
PS: I have another serious problem regarding SQL Server 2005 database,
please take a look at it in "SQL Server Server newsgroup" under "failed to
connect to SQL Server's database!" thread. Any suggestions and help is
greatly appreciated.
Thank you.You have to first remove the schemas assigned to the user BEFORE you can
remove the user.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dhow" <Dhow@.discussions.microsoft.com> wrote in message
news:958F2A86-5F41-41B6-92E8-9AB888FCD441@.microsoft.com...
> Hi,
> I've just installed SQL Server 2005, and mistakenly add a user in
> ServerName\Database\master; I added two kind of schema into that user.
> But when I tried to delete the user, SQL Server 2005 tells me that
> database
> user have schema assigned to it thus can't be remove.
> Is there a way to do delete it?
> PS: I have another serious problem regarding SQL Server 2005 database,
> please take a look at it in "SQL Server Server newsgroup" under "failed to
> connect to SQL Server's database!" thread. Any suggestions and help is
> greatly appreciated.
> Thank you.

A Simple Query Help

Hi!
I have two tables (Master and Slave). So I want to write a query (using
SQL Query Analyser) which will display all the records in Master which
are not in Slave.
Can anyone help?
Cheers!!!If you mean a 1-n relation that should be somethin like this:
Select * from master m
Where not exists
(
Select * from slave s where s.joinedColumn = m.joinedcolumn
)
HTH, jens Suessmeyer.|||select m.* from masters m
left join
slave s
on m.col = s.col where s.col is null
Regards
Amish|||select m.* from masters m
left join
slave s
on m.col = s.col where s.col is null
Regards
Amish|||Amish,
note that not exists() approach may be up to 20% faster than your one:
select m.* from masters m
left join
slave s
on m.col = s.col where s.col is null|||Sasha
It depends .
I've seen many examples especially with large amount of data that JOIN
approach was faster than EXISTS (correlated subquery)
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1134572338.586427.228630@.g14g2000cwa.googlegroups.com...
> Amish,
> note that not exists() approach may be up to 20% faster than your one:
> select m.* from masters m
> left join
> slave s
> on m.col = s.col where s.col is null
>|||>I 've seen many examples especially with large amount of data that JOIN
> approach was faster than EXISTS (correlated subquery)
Uri,
It's very very interesting. Can you please elaborate?
I usually see 2 different situations
- a parent row has on average just several child ones, then there is no
much difference to speak about
- several years down the road, a parent row (a customer) has several
hundred child ones (customer orders), then
the OUTER JOIN approach is up to 20% slower|||In some situations OUTER JOIN query requires fewer that half the number of
logical I/O that EXISTS does
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1134576273.447850.245560@.z14g2000cwz.googlegroups.com...
> Uri,
> It's very very interesting. Can you please elaborate?
> I usually see 2 different situations
> - a parent row has on average just several child ones, then there is no
> much difference to speak about
> - several years down the road, a parent row (a customer) has several
> hundred child ones (customer orders), then
> the OUTER JOIN approach is up to 20% slower
>|||yes, well, it always depends, that's why I said MAY be, not WILL be

A rewrite of the sp_help_revlogin procedure (use at own risk)

Use the view master.sys.sql_logins (new in 2005) to get at the varbinary passwords like you did in your Sql Server 2000 scripts (instead of using passwords from master.dbo.sysxlogins).

I have altered the sp_help_revlogin (from Microsoft article # 246133 )

PLEASE TEST/FIX before you use this:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_help_revlogin_2005]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_help_revlogin_2005]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE sp_help_revlogin_2005 @.login_name sysname = NULL AS

DECLARE @.name sysname

DECLARE @.logintype char(1)

DECLARE @.logindisabled int

DECLARE @.binpwd varbinary (256)

DECLARE @.txtpwd sysname

DECLARE @.tmpstr varchar (256)

DECLARE @.SID_varbinary varbinary(85)

DECLARE @.SID_string varchar(256)

IF (@.login_name IS NULL)

DECLARE login_curs CURSOR FOR

SELECT sid, name, type, is_disabled FROM master.sys.server_principals

WHERE name <> 'sa' and type in ('S','U','G')

ELSE

DECLARE login_curs CURSOR FOR

SELECT sid, name, type, is_disabled FROM master.sys.server_principals

WHERE name = @.login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.logintype, @.logindisabled

IF (@.@.fetch_status = -1)

BEGIN

PRINT 'No login(s) found.'

CLOSE login_curs

DEALLOCATE login_curs

RETURN -1

END

SET @.tmpstr = '/* sp_help_revlogin_2005 script '

PRINT @.tmpstr

SET @.tmpstr = '** Generated '

+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'

PRINT @.tmpstr

PRINT ''

PRINT 'DECLARE @.pwd sysname'

WHILE (@.@.fetch_status <> -1)

BEGIN

IF (@.@.fetch_status <> -2)

BEGIN

PRINT ''

SET @.tmpstr = '-- Login: ' + @.name

PRINT @.tmpstr

IF (@.logintype = 'G' OR @.logintype = 'U')

BEGIN -- NT authenticated account/group

IF @.logindisabled = 1

BEGIN -- NT login is denied access

SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''

PRINT @.tmpstr

END

ELSE BEGIN -- NT login has access

SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''

PRINT @.tmpstr

END

END

ELSE IF (@.logintype = 'S')

BEGIN -- SQL Server authentication

SELECT @.binpwd = password_hash FROM master.sys.sql_logins WHERE SID = @.SID_varbinary

IF (@.binpwd IS NOT NULL)

BEGIN -- Non-null password

EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT

SET @.tmpstr = 'SET @.pwd = CONVERT (nvarchar(128), ' + @.txtpwd + ')'

PRINT @.tmpstr

EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT

SET @.tmpstr = 'EXEC master..sp_addlogin @.loginame = ''' + @.name

+ ''', @.passwd = @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = ''skip_encryption'''

END

ELSE BEGIN

-- Null password

EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT

SET @.tmpstr = 'EXEC master..sp_addlogin @.loginame = ''' + @.name

+ ''', @.passwd = NULL, @.sid = ' + @.SID_string

END

PRINT @.tmpstr

END

END

FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.logintype, @.logindisabled

END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

I've split this from the thread to which it was posted (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=169513&SiteID=1), as it does not help with decrypting passwords. I've added a more suggestive title as well.

Thanks for your contribution.

Laurentiu

|||

Here's another rewrite of the sp_help_revlogin, which uses the new DDL and also outputs the password policy options:

http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx

Thanks
Laurentiu