Showing posts with label creating. Show all posts
Showing posts with label creating. Show all posts

Wednesday, March 7, 2012

Another 2005 x64 to 2000 x32 Linked Server Problem

Hello,

I have been searching the archives for information on problems that occur with creating a linked server to SQL Server 2000 from SQL Server 2005 x64, but the problem I am having seems to be slightly different.

I was able to create the linked server from the 2005 server this way;

Exec sp_addlinkedserver
@.Server = '2kServer',
@.srvproduct = 'SQL Server'

I was able to add the login this way;

Exec sp_addlinkedsrvlogin
@.rmtsrvname = '2kServer',
@.useself='False',
@.locallogin='domain\cdun2',
@.rmtuser='domain\cdun2',
@.rmtpassword='pswd'

I receive 'Command(s) completed successfully' for both. When I attempt to run either of the following queries from a connection to the 2005 server in Management Studio;

select top 100 * from 2kServer.database.dbo.table

select * from Openquery(2kServer,'select top 10 * from database.dbo.table')

I get the following error

OLE DB provider "SQLNCLI" for linked server "2kServer" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'domain\cdun2'.

I've seen a couple of ideas on this. One states the following;

***************************************

When running 4 part reference query like this:

select * from sql2000.mybase.dbo.mytable

SQL Server 2005 x64 runs the following query on remote SQL2000 server:

exec [mybase]..sp_tables_info_rowset_64 N'mytable', N'dbo', NULL

Unfortunately there is no such a proc on SQL2k. However, sp_tables_info_rowset exists and does the same thing. The solution is to create wrapper on master database like this:

create procedure sp_tables_info_rowset_64

@.table_name sysname,

@.table_schema sysname = null,

@.table_type nvarchar(255) = null

as

declare @.Result int set @.Result = 0

exec @.Result = sp_tables_info_rowset @.table_name, @.table_schema, @.table_type

And then everything works fine. If you don't want to create "Microsoft like" objects on master database, use openquery instead of 4 part reference.

**************************************

I put this proc on the 2kServer and tried again, but I got the same error. I've also seen this:

I went into the Sql Server Configuration Manager, Sql Native Client
Configuration -> Protocols and disabled Shared Memory and made the TCP/IP
protocol #1 in order. Then just restarted the Sql Service and it all started
working!

I don't have access to the server to do this, but I wonder if it would solve the problem. I am a sys admin on both servers. The servers are in two different domains. Could this be a firewall issue?

Thank you for your help!

cdun2

I am not a linked server expert but it looks like you need to switch the following parameter

@.rmtuser='domain\cdun2',

to one that is a valid user on the the second domain since the two servers are in different domains.

HTH,

-Steven Gott

SDE/T

SQL Server

|||

Sorry for the delayed response. I'll be able to check into this tomorrow.

cdun2

Another 2005 x64 to 2000 x32 Linked Server Problem

Hello,

I have been searching the archives for information on problems that occur with creating a linked server to SQL Server 2000 from SQL Server 2005 x64, but the problem I am having seems to be slightly different.

I was able to create the linked server from the 2005 server this way;

Exec sp_addlinkedserver
@.Server = '2kServer',
@.srvproduct = 'SQL Server'

I was able to add the login this way;

Exec sp_addlinkedsrvlogin
@.rmtsrvname = '2kServer',
@.useself='False',
@.locallogin='domain\cdun2',
@.rmtuser='domain\cdun2',
@.rmtpassword='pswd'

I receive 'Command(s) completed successfully' for both. When I attempt to run either of the following queries from a connection to the 2005 server in Management Studio;

select top 100 * from 2kServer.database.dbo.table

select * from Openquery(2kServer,'select top 10 * from database.dbo.table')

I get the following error

OLE DB provider "SQLNCLI" for linked server "2kServer" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'domain\cdun2'.

I've seen a couple of ideas on this. One states the following;

***************************************

When running 4 part reference query like this:

select * from sql2000.mybase.dbo.mytable

SQL Server 2005 x64 runs the following query on remote SQL2000 server:

exec [mybase]..sp_tables_info_rowset_64 N'mytable', N'dbo', NULL

Unfortunately there is no such a proc on SQL2k. However, sp_tables_info_rowset exists and does the same thing. The solution is to create wrapper on master database like this:

create procedure sp_tables_info_rowset_64

@.table_name sysname,

@.table_schema sysname = null,

@.table_type nvarchar(255) = null

as

declare @.Result int set @.Result = 0

exec @.Result = sp_tables_info_rowset @.table_name, @.table_schema, @.table_type

And then everything works fine. If you don't want to create "Microsoft like" objects on master database, use openquery instead of 4 part reference.

**************************************

I put this proc on the 2kServer and tried again, but I got the same error. I've also seen this:

I went into the Sql Server Configuration Manager, Sql Native Client
Configuration -> Protocols and disabled Shared Memory and made the TCP/IP
protocol #1 in order. Then just restarted the Sql Service and it all started
working!

I don't have access to the server to do this, but I wonder if it would solve the problem. I am a sys admin on both servers. The servers are in two different domains. Could this be a firewall issue?

Thank you for your help!

cdun2

I am not a linked server expert but it looks like you need to switch the following parameter

@.rmtuser='domain\cdun2',

to one that is a valid user on the the second domain since the two servers are in different domains.

HTH,

-Steven Gott

SDE/T

SQL Server

|||

Sorry for the delayed response. I'll be able to check into this tomorrow.

cdun2

Thursday, February 16, 2012

Analysis Services: Deploy a cube

I'm making my first attempt at creating a cube using Analysis Services based on my exisiting datamart. Datasource, views, and dimensions have been defined. But comes deploying the cube, it's giving the error saying "A connection cannot be made. Ensure that the server is running." The Deploy Target server and database are the same where my datamart is. Or, maybe I don't know what I'm doing.

Would appreciate any suggestion for my enlightenment. ThanksJodJim,

I am assuming you properly specified target server name. Next steps:
1. Make sure that SQL Server Analysis Services service is running. It is not the same service as SQL service. You can do this in Adminitrator Tools ->Services. This is most likely cause why you cannot deploy
2. Make sure you are member of administrators local group on SSAS server. This will give you admin right required to deploy database.

If that does not work:
Start SQL Server Management Studio and connect to analysis services. Any problems? Any messages?

Vidas Matelis
http://www.ssas-info.com
http://www.ssas-info.com/VidasMatelisBlog

Analysis Services Role created via AMO has unknown problem.

Hi,

This is a wierd problem so I hope I can explain the situation clearly Smile

I'm creating a single role via AMO for an Analysis Services database and adding domain users to the role just fine. When our 3rd party reporting tool tries to create a report an error gets generated and they bubble back the error shown below.

Now here is the wierd part...if I simply view the Role in SQL Server Managment Management Studio and click OK the problem goes away. If I click Cancel when viewing the Role the problem persists. Obviously when I click "OK" in the Role dialog some sort of save routine is going on and fixes whatever problem is present.

I'm creating the role using standard MSDN examples and everything looks in order. Any suggestions on how I can better track down what the problem is?

System.Exception: Exception has been thrown by the target of an invocation. at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) Thanks

Could you post the code you use to generate the role? I'll compare it to some code we have that is working. My first guess is there is probably something not quite right in the permissions.

Bryan

|||

To see what is different (between the role create with AMO and the role fixed by the dialog), you can do this:

- re-create the role with AMO

- connect with 'SQL Management Studio' and script the Create or Alter operation of the role (right click on the role and there should be the 'Script' option)

- run the role dialog, press 'OK'

- script again and compare with the previous script

Adrian Dumitrascu

Sunday, February 12, 2012

Analysis Services Data Source Cube vs Dim

Hi group

I am creating a cube where the facr table is held in one sql database/data source and the dimension info in another sql database/data source.

When I try to include the dimension in the cube the dimension is not available.

How do I get around this?

Thanks in advance

ChrisHave you tried creating virtual dimensions over your existing dimensions?

Thursday, February 9, 2012

Analysis Services 2005 Derived Member

Hi,

I can't seem to find any information about creating a derived member in SSAS 2005. I have found a bunch of stuff for 2000. I have some Calculated members that are giving me some performance pains that I was hoping to solve by creating derived members.

Any info would be greatly appreciated.

S

WHat is "derived member" ? Neither AS2000 nor AS2005 have such a concept. Can you point out to the information you found, so it will be easier for us to understand what you mean. Or, perhaps, you can describe your scenario in detail, and then this forum might be able to help.|||

I apologize. It was Derived Member.

Here is a link: http://www.databasejournal.com/features/mssql/article.php/3394681

|||

In this case, the "derived member" discussion in that link is the equivalent of adding named calculations within the DSV of an SSAS 2005 solution.

A named calculation is added to a table (or view) that has been included within a DSV. The named calculation takes an expression (in the SQL dialect of the underlying RDBMS involved) and provides a name for the resulting value. This then becomes another available column within the table from the perspective of the rest of the SSAS solution. Thus, a measure could be built using the named calculation (if it is in a fact table) or an attribute could be created using it (if it is in a dimension table).

HTH,

Dave Fackler