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 Problem with Processing a datamining structure

Hello :

I have tried to process a datamining structure in SQL Server 2005 (Developer
Edition) but I receive these messages from Sql Server :

"Errors in the high-level relational engine. A connection could not be
made to the data source with the DataSourceID of 'Dbjao', Name of
'Dbjao'."

"Errors in the OLAP storage engine: An error occurred while the
dimension, with the ID of 'MCredito ~MC-Id', Name of 'MCredito ~MC-Id'
was being processed."

"Errors in the OLAP storage engine: An error occurred while the 'Class'
attribute of the 'MCredito ~MC-Id'
dimension from the 'CreditoAleman' database was being processed."

I generate this model from a relational table and can explore this
table
and build and deploy the project

But I receive these error message when I try to process the mining
structure

Do you have any idea which is the problem ?

Best Whish
Juan AlvaradoThis usually occurs when theres a type conversion issue. Check the datatype of the "Class" attribute both in your datasource, the DSV type, and the Mining Structure bindings - you'll have to dig into the properties of the columns for the DSV and the Mining Structure.|||Firstly, check the impersonation information tab on your data source object (Dbjao). You might have set it to use the credentials of the current user or a specific user/password. This info is used when you're exploring data using this data source in the designer and when you use the data source in a prediction query. However, during processing, the server uses the service account credentials to connect to the data source. Confirm that the service account that your Analysis Services instance is running under has access to the database specified in the data source. That's likely the reason you're seeing this error.

A Gripe about Error Messages

[OLE DB Destination [255]] Error: The "input "OLE DB Destination Input" (268)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (268)" specifies failure on error. An error occurred on the specified object of the specified component.

I've condensed the useful information in the statement down to the following:

"An error occured."

I'd like to also provide a plain english paraphrase.

An error occured somewhere to something. This means that something somewhere didn't work right. The cause of the thing not working right is an error of some sort. We'd like to provide you with the following piece of diagnostic information: we know that an error occured somewhere to something because the error row disposition tells us this. We hope that helps. Thank you, and have a nice day.

Now, could anyone translate this into Klingon? I think it would be easier to understand and just as useful.

I agree with JO. Most of the SSIS error messages seem to be Vague and Unhelpful. It takes lot of time for a SSIS Dev to decipher the error messsages. The SSIS team should throw more meaningful and error messages that we all can understand and take action appropriately. Errors of the kind mentioned do not help in resolving the issues.

Thanks

AK

|||

Rename your components and look to the first error thrown from a given task for diagnostic/debugging purposes.

Leaving pipeline components at their default names makes debugging far harder. Renaming inputs and outputs is not usually done, because most components, provided they aren't sources, have a single input.

Jamie Thompson has a very useful naming convention , which if followed, will make that error message, and really, the ones which preceeded it, more meaningful, because the component name will not be the default. That doesn't make 0xC0... hex code any more meaningful, but I can tell you it does help.

Those creating somewhat permanent to permanent table names rarely leave the table at dbo.Table_1 because the name doesn't convey intent/purpose. Same concept goes for pipeline components, give them a meaningful name.

Was that the first error received? The first error is almost invariably the most helpful one, and its doubtful that was the first error thrown by the dataflow. Some of the first errors are still not as meaningful as I would like, but often this has to do with the provider, such as an OLEDB provider, which produces the messages which SSIS relays. Before imagining these are the words of an apologist, perhaps note my prior gripes about error messages as well.

Subsequent errors, particuarly in dataflows, are not as useful in debugging/diagnostics, because they are further and futher removed from the specificity and purpose of the component, and relate to how the pipeline works. Look (generally speaking) to the first error for any given task invocation.