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 report VIEWER control ! How about the report DESIGNER control ?

Hi all, our team is really excited about the new ReportViewer control.
Heck, it even supports custom data sources, which fits in nicely with our
overall application design.
The only thing that seems to be missing then is a control that allows our
users to design (or at least modify) reports themselves. In our mind,
the biggest advantage to working with a report engine and report files
is the ability to provide your users with customizable reports.
Moreover, we know this functionality exists since we (programmers)
use the report designer in VS2005. So it would be up to Microsoft to
package it into a control and ship it.
I'm sure somebody else has already asked about this by now but repeated
google queries have turned up nothing relevant.
Thanks for any light anyone can shed on this.
Sven
PS: I hope my cross-posting does notTake a look at the information about Report Builder in Reporting
Services 2005. That is intended to give end users the ability to
design their own reports.
But it's not a control though. Of course, until recently the Report
Viewer control didn't exist either. :)
Andrew Watt
MVP - InfoPath
On Wed, 12 Oct 2005 12:19:45 +0200, "Sven Devriese"
<desfen@.newsgroup.nospam> wrote:
>Hi all, our team is really excited about the new ReportViewer control.
>Heck, it even supports custom data sources, which fits in nicely with our
>overall application design.
>The only thing that seems to be missing then is a control that allows our
>users to design (or at least modify) reports themselves. In our mind,
>the biggest advantage to working with a report engine and report files
>is the ability to provide your users with customizable reports.
>Moreover, we know this functionality exists since we (programmers)
>use the report designer in VS2005. So it would be up to Microsoft to
>package it into a control and ship it.
>I'm sure somebody else has already asked about this by now but repeated
>google queries have turned up nothing relevant.
>Thanks for any light anyone can shed on this.
>Sven
>PS: I hope my cross-posting does not
>|||It is not as simple as that. The report designer is built to work with
Visual Studio and takes advantage of common functionality (for instance, all
the query design windows). As a matter of fact, the designer in 2005 now
ships with a special version of VS 2005. Plus you also have vb.net etc etc.
For that reason I don't expect to ever see this as a redistributable.
However, as Andrew mentioned, there is now Report Builder as well for end
users.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sven Devriese" <desfen@.newsgroup.nospam> wrote in message
news:usVb5ZxzFHA.612@.TK2MSFTNGP10.phx.gbl...
> Hi all, our team is really excited about the new ReportViewer control.
> Heck, it even supports custom data sources, which fits in nicely with our
> overall application design.
> The only thing that seems to be missing then is a control that allows our
> users to design (or at least modify) reports themselves. In our mind,
> the biggest advantage to working with a report engine and report files
> is the ability to provide your users with customizable reports.
> Moreover, we know this functionality exists since we (programmers)
> use the report designer in VS2005. So it would be up to Microsoft to
> package it into a control and ship it.
> I'm sure somebody else has already asked about this by now but repeated
> google queries have turned up nothing relevant.
> Thanks for any light anyone can shed on this.
> Sven
> PS: I hope my cross-posting does not
>|||Keep in mind that Report Builder generated RDL files cannot be used directly
with the report viewer controls in Local Mode. They can however be easily
used with the controls in Server Mode.
RDL is a documented and open standard, e.g. for the 2003/10 RDL of RS 2000
please check http://www.microsoft.com/sql/reporting/techinfo/rdlspec.mspx,
the 2005/01 RDL is very similar. You could write your own designer component
that generates RDL. In fact, there are ISVs who have implemented e.g.
web-based report authoring tools. Please check:
http://www.microsoft.com/sql/reporting/partners/default.mspx
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
news:g8upk1tmjfb3vasb96s71r36aadm4lohks@.4ax.com...
> Take a look at the information about Report Builder in Reporting
> Services 2005. That is intended to give end users the ability to
> design their own reports.
> But it's not a control though. Of course, until recently the Report
> Viewer control didn't exist either. :)
> Andrew Watt
> MVP - InfoPath
> On Wed, 12 Oct 2005 12:19:45 +0200, "Sven Devriese"
> <desfen@.newsgroup.nospam> wrote:
>>Hi all, our team is really excited about the new ReportViewer control.
>>Heck, it even supports custom data sources, which fits in nicely with our
>>overall application design.
>>The only thing that seems to be missing then is a control that allows our
>>users to design (or at least modify) reports themselves. In our mind,
>>the biggest advantage to working with a report engine and report files
>>is the ability to provide your users with customizable reports.
>>Moreover, we know this functionality exists since we (programmers)
>>use the report designer in VS2005. So it would be up to Microsoft to
>>package it into a control and ship it.
>>I'm sure somebody else has already asked about this by now but repeated
>>google queries have turned up nothing relevant.
>>Thanks for any light anyone can shed on this.
>>Sven
>>PS: I hope my cross-posting does not
>