Can''t Deploy AdventureWorks AS Database using Instances of SQL and AS

Hi all,

Have just installed June CTP on a new laptop build and am having problems getting the AdventureWorks AS database deployed. Using a named instance of SQL Server 2005 (which houses the AdventureWorksDW database) and a named instance of Analysis Services 2005 (which should be getting the deployed database).

After opening the sample solution in BIDS, I changed the data source definition to point to my SQL Server 2005 instance (laptop\sql2005). Tested fine. Then modified the project properties to use the Analysis Services 2005 instance as the deployment location (also laptop\sql2005). Everything looks fine, but when I try to deploy, I get the following three errors:

Error 1: Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the 'localhost' server.

Error 2: Errors in the OLE DB provider. An error occurred while named instance information was being retrieved from the SQLBrowser service on the 'localhost' server.

Error 3: Either a connection cannot be made to the localhost\SQL2005 server, or Analysis Services is not running on the computer specified.

So, it looks as if the deployment process is somehow trying to connect to "localhost" instead of my machine name and it also seems as if there's a problem with the SQLBrowser service. I've checked and the service is running.

Probably something silly that I'm either forgetting or overlooking. Anyone?

Thanks,
Dave Fackler
Just to close the loop, I was able to deploy after removing all of the data mining objects from the project (sorry DM team!) When I get a chance to review it more closely later on, I'll try to figure out what was causing the problem. But for now, a workaround exists in case anyone else runs into a similar issue.

Dave Fackler
|||Hi,

I have the a similar problem. I can't deploy an AdventureWorks cube in analysis services. I got the following error message:

"Error 1 The project could not be deployed to the 'Computer\SQL_SERVER_2005' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0"

There are no data mining objects in my project. I modified already the deployment properties in the project.

Best regards,
Wolfgang

Dave Fackler wrote:

Just to close the loop, I was able to deploy after removing all of the data mining objects from the project (sorry DM team!) When I get a chance to review it more closely later on, I'll try to figure out what was causing the problem. But for now, a workaround exists in case anyone else runs into a similar issue.

Dave Fackler

|||I was having the same problem yesterday and found that the Analysis Services service was stopped. No idea on how, but after starting it my connection-related deployment problem went away.|||

Wolfgang W. wrote:

Hi,

I have the a similar problem. I can't deploy an AdventureWorks cube in analysis services. I got the following error message:

"Error 1 The project could not be deployed to the 'Computer\SQL_SERVER_2005' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0"

There are no data mining objects in my project. I modified already the deployment properties in the project.

Best regards,
Wolfgang

Dave Fackler

|||

Just out of curiosity has anyone resolved this one as I have the same problem with NO MINING.

regards Steve

|||

Hi,

I also faced same issue while trying to deploying AdvantureWords Analysis service.

I changed the following setting and after that i am able to deploy successfully.

1) Change the SQL server Browser service running Account to Local System Account

2) Create My Login Account in SQL >Security->Login

3) Double click on AdvantureWork.ds from DataSource (From Solution Explorer). and in Impersonation Information Select the Use the Service Account.

after apply all these steps i am able to deployed analysis services.

thanks

AVNISH KUMAR SHARMA

|||

Hi

Thanks a million. I shall try it and let you know. very much appreciated.

regards Steve

|||

Hi

You were spot on.. Thanks ever so much for your help. It worked. :)

For others folks...re point 2 in Avnish's note. He is referring to the login id of the machine that hosts SQL Server.

In my case it was Adminstrator.

regards Steve

|||

Hi AVNISH

Thanks very much. That solved the problem. Very much appreciated.

regards Steve

|||

Still I am facing the same problem.

Create my login account mean ?

|||

Hi Stefaan,

Still i am facing the same problem. can you help me out.

i have done the following steps:

=======================

1) Change the SQL server Browser service running Account to Local System Account

2) Create My Login Account in SQL >Security->Login

I have server name : EZ\SQLExpress and i have created login for ramki i.e. EZ\Ramki and pointed to adventurework database too

3) Double click on AdvantureWork.ds from DataSource (From Solution Explorer). and in Impersonation Information Select the Use the Service Account.

Please can you help me out.

Regards

Ramki

|||

Ramki

Firstly go to Start, SQL Server 2005/Express and then goto 'configuration tools'

Click on the SQL Server Browser and then right click to bring up the context menu and select PROPERTIES.

Ensure that "Built-in Account" is set to local system.

Then

USE any Login with sysadmin rights for AdventureworksDW or (Create any SQL Server Login using MASTER).

Now if you are working standalone you may have another ID to use such as sa. I prefer NOT to use sa for obvious reasons.

If you choose to create a new Login ID, it too MUST have SYSADMIN rights.

Change databases to AdventureworksDW and now 'attach' i.e. add the login to the database.

I am running under windows 2003 server and therefore utilize windows authentication and have an ID in SQL Server 2005 called 'Administrator' which has been allocated sysadmin rights and has sysadmin rights on database AdventureworksDW.

The important thing is that the ID has sysadmin rights in the database that you are trying to connect to.

In this case AdventureworksDW.

THEN

Within your Visual Studio 2005 environment (where you created your project and where you created a data source to link to the ADVENTUREWORKSDW database),double click on the DataSource (in your Solution Explorer) and select the "Impersonation Tab". Select the "Use the Service Account".

This should be all that you need to do. It worked for me.

I hope that this helps.

|||

Hi All,

I tried all that is mentioned in this thread but still facing the problem.

The error

Error 1 The project could not be deployed to the '172.24.185.73\SQL2K5' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0

1. SQL Browser service is running under local system account and tried after restarting as well.

2. New Login created (with Sysadmin) on my database using Administrator win account

3. All the settings done mentioned above under impersonation tab

4. Built in Account is set to Local Account.

Any idea/suggestion would be of great help.

Thank

Atgos

|||

Hi Atgos

When I posted my solution I was working with SQL Server 2005 Standard Edition on Windows 2003 Server.

Are you by any chance using Vista?

This is what I did and did you remember the impersonation?

1) Change the SQL server Browser service fAccount to Local System Account

2) Login: sa is fine as long as it has sysadmin rights.

From within Visual Studio .NET 2005 ensure ....

3) Double click on "AdventureWork.ds" from DataSource (From Solution Explorer).
and in Impersonation Information Select the Use the Service Account.

Please let us know how you fare

Can''t Deploy AdventureWorks AS Database using Instances of SQL and AS

Hi all,

Have just installed June CTP on a new laptop build and am having problems getting the AdventureWorks AS database deployed. Using a named instance of SQL Server 2005 (which houses the AdventureWorksDW database) and a named instance of Analysis Services 2005 (which should be getting the deployed database).

After opening the sample solution in BIDS, I changed the data source definition to point to my SQL Server 2005 instance (laptop\sql2005). Tested fine. Then modified the project properties to use the Analysis Services 2005 instance as the deployment location (also laptop\sql2005). Everything looks fine, but when I try to deploy, I get the following three errors:

Error 1: Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the 'localhost' server.

Error 2: Errors in the OLE DB provider. An error occurred while named instance information was being retrieved from the SQLBrowser service on the 'localhost' server.

Error 3: Either a connection cannot be made to the localhost\SQL2005 server, or Analysis Services is not running on the computer specified.

So, it looks as if the deployment process is somehow trying to connect to "localhost" instead of my machine name and it also seems as if there's a problem with the SQLBrowser service. I've checked and the service is running.

Probably something silly that I'm either forgetting or overlooking. Anyone?

Thanks,
Dave Fackler
Just to close the loop, I was able to deploy after removing all of the data mining objects from the project (sorry DM team!) When I get a chance to review it more closely later on, I'll try to figure out what was causing the problem. But for now, a workaround exists in case anyone else runs into a similar issue.

Dave Fackler
|||Hi,

I have the a similar problem. I can't deploy an AdventureWorks cube in analysis services. I got the following error message:

"Error 1 The project could not be deployed to the 'Computer\SQL_SERVER_2005' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0"

There are no data mining objects in my project. I modified already the deployment properties in the project.

Best regards,
Wolfgang

Dave Fackler wrote:

Just to close the loop, I was able to deploy after removing all of the data mining objects from the project (sorry DM team!) When I get a chance to review it more closely later on, I'll try to figure out what was causing the problem. But for now, a workaround exists in case anyone else runs into a similar issue.

Dave Fackler

|||I was having the same problem yesterday and found that the Analysis Services service was stopped. No idea on how, but after starting it my connection-related deployment problem went away.|||

Wolfgang W. wrote:

Hi,

I have the a similar problem. I can't deploy an AdventureWorks cube in analysis services. I got the following error message:

"Error 1 The project could not be deployed to the 'Computer\SQL_SERVER_2005' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0"

There are no data mining objects in my project. I modified already the deployment properties in the project.

Best regards,
Wolfgang

Dave Fackler

|||

Just out of curiosity has anyone resolved this one as I have the same problem with NO MINING.

regards Steve

|||

Hi,

I also faced same issue while trying to deploying AdvantureWords Analysis service.

I changed the following setting and after that i am able to deploy successfully.

1) Change the SQL server Browser service running Account to Local System Account

2) Create My Login Account in SQL >Security->Login

3) Double click on AdvantureWork.ds from DataSource (From Solution Explorer). and in Impersonation Information Select the Use the Service Account.

after apply all these steps i am able to deployed analysis services.

thanks

AVNISH KUMAR SHARMA

|||

Hi

Thanks a million. I shall try it and let you know. very much appreciated.

regards Steve

|||

Hi

You were spot on.. Thanks ever so much for your help. It worked. :)

For others folks...re point 2 in Avnish's note. He is referring to the login id of the machine that hosts SQL Server.

In my case it was Adminstrator.

regards Steve

|||

Hi AVNISH

Thanks very much. That solved the problem. Very much appreciated.

regards Steve

|||

Still I am facing the same problem.

Create my login account mean ?

|||

Hi Stefaan,

Still i am facing the same problem. can you help me out.

i have done the following steps:

=======================

1) Change the SQL server Browser service running Account to Local System Account

2) Create My Login Account in SQL >Security->Login

I have server name : EZ\SQLExpress and i have created login for ramki i.e. EZ\Ramki and pointed to adventurework database too

3) Double click on AdvantureWork.ds from DataSource (From Solution Explorer). and in Impersonation Information Select the Use the Service Account.

Please can you help me out.

Regards

Ramki

|||

Ramki

Firstly go to Start, SQL Server 2005/Express and then goto 'configuration tools'

Click on the SQL Server Browser and then right click to bring up the context menu and select PROPERTIES.

Ensure that "Built-in Account" is set to local system.

Then

USE any Login with sysadmin rights for AdventureworksDW or (Create any SQL Server Login using MASTER).

Now if you are working standalone you may have another ID to use such as sa. I prefer NOT to use sa for obvious reasons.

If you choose to create a new Login ID, it too MUST have SYSADMIN rights.

Change databases to AdventureworksDW and now 'attach' i.e. add the login to the database.

I am running under windows 2003 server and therefore utilize windows authentication and have an ID in SQL Server 2005 called 'Administrator' which has been allocated sysadmin rights and has sysadmin rights on database AdventureworksDW.

The important thing is that the ID has sysadmin rights in the database that you are trying to connect to.

In this case AdventureworksDW.

THEN

Within your Visual Studio 2005 environment (where you created your project and where you created a data source to link to the ADVENTUREWORKSDW database),double click on the DataSource (in your Solution Explorer) and select the "Impersonation Tab". Select the "Use the Service Account".

This should be all that you need to do. It worked for me.

I hope that this helps.

|||

Hi All,

I tried all that is mentioned in this thread but still facing the problem.

The error

Error 1 The project could not be deployed to the '172.24.185.73\SQL2K5' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0

1. SQL Browser service is running under local system account and tried after restarting as well.

2. New Login created (with Sysadmin) on my database using Administrator win account

3. All the settings done mentioned above under impersonation tab

4. Built in Account is set to Local Account.

Any idea/suggestion would be of great help.

Thank

Atgos

|||

Hi Atgos

When I posted my solution I was working with SQL Server 2005 Standard Edition on Windows 2003 Server.

Are you by any chance using Vista?

This is what I did and did you remember the impersonation?

1) Change the SQL server Browser service fAccount to Local System Account

2) Login: sa is fine as long as it has sysadmin rights.

From within Visual Studio .NET 2005 ensure ....

3) Double click on "AdventureWork.ds" from DataSource (From Solution Explorer).
and in Impersonation Information Select the Use the Service Account.

Please let us know how you fare

Can''t Deploy AdventureWorks AS Database using Instances of SQL and AS

Hi all,

Have just installed June CTP on a new laptop build and am having problems getting the AdventureWorks AS database deployed. Using a named instance of SQL Server 2005 (which houses the AdventureWorksDW database) and a named instance of Analysis Services 2005 (which should be getting the deployed database).

After opening the sample solution in BIDS, I changed the data source definition to point to my SQL Server 2005 instance (laptop\sql2005). Tested fine. Then modified the project properties to use the Analysis Services 2005 instance as the deployment location (also laptop\sql2005). Everything looks fine, but when I try to deploy, I get the following three errors:

Error 1: Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the 'localhost' server.

Error 2: Errors in the OLE DB provider. An error occurred while named instance information was being retrieved from the SQLBrowser service on the 'localhost' server.

Error 3: Either a connection cannot be made to the localhost\SQL2005 server, or Analysis Services is not running on the computer specified.

So, it looks as if the deployment process is somehow trying to connect to "localhost" instead of my machine name and it also seems as if there's a problem with the SQLBrowser service. I've checked and the service is running.

Probably something silly that I'm either forgetting or overlooking. Anyone?

Thanks,
Dave Fackler
Just to close the loop, I was able to deploy after removing all of the data mining objects from the project (sorry DM team!) When I get a chance to review it more closely later on, I'll try to figure out what was causing the problem. But for now, a workaround exists in case anyone else runs into a similar issue.

Dave Fackler
|||Hi,

I have the a similar problem. I can't deploy an AdventureWorks cube in analysis services. I got the following error message:

"Error 1 The project could not be deployed to the 'Computer\SQL_SERVER_2005' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0"

There are no data mining objects in my project. I modified already the deployment properties in the project.

Best regards,
Wolfgang

Dave Fackler wrote:

Just to close the loop, I was able to deploy after removing all of the data mining objects from the project (sorry DM team!) When I get a chance to review it more closely later on, I'll try to figure out what was causing the problem. But for now, a workaround exists in case anyone else runs into a similar issue.

Dave Fackler

|||I was having the same problem yesterday and found that the Analysis Services service was stopped. No idea on how, but after starting it my connection-related deployment problem went away.|||

Wolfgang W. wrote:

Hi,

I have the a similar problem. I can't deploy an AdventureWorks cube in analysis services. I got the following error message:

"Error 1 The project could not be deployed to the 'Computer\SQL_SERVER_2005' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0"

There are no data mining objects in my project. I modified already the deployment properties in the project.

Best regards,
Wolfgang

Dave Fackler

|||

Just out of curiosity has anyone resolved this one as I have the same problem with NO MINING.

regards Steve

|||

Hi,

I also faced same issue while trying to deploying AdvantureWords Analysis service.

I changed the following setting and after that i am able to deploy successfully.

1) Change the SQL server Browser service running Account to Local System Account

2) Create My Login Account in SQL >Security->Login

3) Double click on AdvantureWork.ds from DataSource (From Solution Explorer). and in Impersonation Information Select the Use the Service Account.

after apply all these steps i am able to deployed analysis services.

thanks

AVNISH KUMAR SHARMA

|||

Hi

Thanks a million. I shall try it and let you know. very much appreciated.

regards Steve

|||

Hi

You were spot on.. Thanks ever so much for your help. It worked. :)

For others folks...re point 2 in Avnish's note. He is referring to the login id of the machine that hosts SQL Server.

In my case it was Adminstrator.

regards Steve

|||

Hi AVNISH

Thanks very much. That solved the problem. Very much appreciated.

regards Steve

|||

Still I am facing the same problem.

Create my login account mean ?

|||

Hi Stefaan,

Still i am facing the same problem. can you help me out.

i have done the following steps:

=======================

1) Change the SQL server Browser service running Account to Local System Account

2) Create My Login Account in SQL >Security->Login

I have server name : EZ\SQLExpress and i have created login for ramki i.e. EZ\Ramki and pointed to adventurework database too

3) Double click on AdvantureWork.ds from DataSource (From Solution Explorer). and in Impersonation Information Select the Use the Service Account.

Please can you help me out.

Regards

Ramki

|||

Ramki

Firstly go to Start, SQL Server 2005/Express and then goto 'configuration tools'

Click on the SQL Server Browser and then right click to bring up the context menu and select PROPERTIES.

Ensure that "Built-in Account" is set to local system.

Then

USE any Login with sysadmin rights for AdventureworksDW or (Create any SQL Server Login using MASTER).

Now if you are working standalone you may have another ID to use such as sa. I prefer NOT to use sa for obvious reasons.

If you choose to create a new Login ID, it too MUST have SYSADMIN rights.

Change databases to AdventureworksDW and now 'attach' i.e. add the login to the database.

I am running under windows 2003 server and therefore utilize windows authentication and have an ID in SQL Server 2005 called 'Administrator' which has been allocated sysadmin rights and has sysadmin rights on database AdventureworksDW.

The important thing is that the ID has sysadmin rights in the database that you are trying to connect to.

In this case AdventureworksDW.

THEN

Within your Visual Studio 2005 environment (where you created your project and where you created a data source to link to the ADVENTUREWORKSDW database),double click on the DataSource (in your Solution Explorer) and select the "Impersonation Tab". Select the "Use the Service Account".

This should be all that you need to do. It worked for me.

I hope that this helps.

|||

Hi All,

I tried all that is mentioned in this thread but still facing the problem.

The error

Error 1 The project could not be deployed to the '172.24.185.73\SQL2K5' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0

1. SQL Browser service is running under local system account and tried after restarting as well.

2. New Login created (with Sysadmin) on my database using Administrator win account

3. All the settings done mentioned above under impersonation tab

4. Built in Account is set to Local Account.

Any idea/suggestion would be of great help.

Thank

Atgos

|||

Hi Atgos

When I posted my solution I was working with SQL Server 2005 Standard Edition on Windows 2003 Server.

Are you by any chance using Vista?

This is what I did and did you remember the impersonation?

1) Change the SQL server Browser service fAccount to Local System Account

2) Login: sa is fine as long as it has sysadmin rights.

From within Visual Studio .NET 2005 ensure ....

3) Double click on "AdventureWork.ds" from DataSource (From Solution Explorer).
and in Impersonation Information Select the Use the Service Account.

Please let us know how you fare

Can't Deploy AdventureWorks AS Database using Instances of SQL and AS

Hi all,

Have just installed June CTP on a new laptop build and am having problems getting the AdventureWorks AS database deployed. Using a named instance of SQL Server 2005 (which houses the AdventureWorksDW database) and a named instance of Analysis Services 2005 (which should be getting the deployed database).

After opening the sample solution in BIDS, I changed the data source definition to point to my SQL Server 2005 instance (laptop\sql2005). Tested fine. Then modified the project properties to use the Analysis Services 2005 instance as the deployment location (also laptop\sql2005). Everything looks fine, but when I try to deploy, I get the following three errors:

Error 1: Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the 'localhost' server.

Error 2: Errors in the OLE DB provider. An error occurred while named instance information was being retrieved from the SQLBrowser service on the 'localhost' server.

Error 3: Either a connection cannot be made to the localhost\SQL2005 server, or Analysis Services is not running on the computer specified.

So, it looks as if the deployment process is somehow trying to connect to "localhost" instead of my machine name and it also seems as if there's a problem with the SQLBrowser service. I've checked and the service is running.

Probably something silly that I'm either forgetting or overlooking. Anyone?

Thanks,
Dave Fackler
Just to close the loop, I was able to deploy after removing all of the data mining objects from the project (sorry DM team!) When I get a chance to review it more closely later on, I'll try to figure out what was causing the problem. But for now, a workaround exists in case anyone else runs into a similar issue.

Dave Fackler
|||Hi,

I have the a similar problem. I can't deploy an AdventureWorks cube in analysis services. I got the following error message:

"Error 1 The project could not be deployed to the 'Computer\SQL_SERVER_2005' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0"

There are no data mining objects in my project. I modified already the deployment properties in the project.

Best regards,
Wolfgang

Dave Fackler wrote:

Just to close the loop, I was able to deploy after removing all of the data mining objects from the project (sorry DM team!) When I get a chance to review it more closely later on, I'll try to figure out what was causing the problem. But for now, a workaround exists in case anyone else runs into a similar issue.

Dave Fackler

|||I was having the same problem yesterday and found that the Analysis Services service was stopped. No idea on how, but after starting it my connection-related deployment problem went away.|||

Wolfgang W. wrote:

Hi,

I have the a similar problem. I can't deploy an AdventureWorks cube in analysis services. I got the following error message:

"Error 1 The project could not be deployed to the 'Computer\SQL_SERVER_2005' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0"

There are no data mining objects in my project. I modified already the deployment properties in the project.

Best regards,
Wolfgang

Dave Fackler

|||

Just out of curiosity has anyone resolved this one as I have the same problem with NO MINING.

regards Steve

|||

Hi,

I also faced same issue while trying to deploying AdvantureWords Analysis service.

I changed the following setting and after that i am able to deploy successfully.

1) Change the SQL server Browser service running Account to Local System Account

2) Create My Login Account in SQL >Security->Login

3) Double click on AdvantureWork.ds from DataSource (From Solution Explorer). and in Impersonation Information Select the Use the Service Account.

after apply all these steps i am able to deployed analysis services.

thanks

AVNISH KUMAR SHARMA

|||

Hi

Thanks a million. I shall try it and let you know. very much appreciated.

regards Steve

|||

Hi

You were spot on.. Thanks ever so much for your help. It worked. :)

For others folks...re point 2 in Avnish's note. He is referring to the login id of the machine that hosts SQL Server.

In my case it was Adminstrator.

regards Steve

|||

Hi AVNISH

Thanks very much. That solved the problem. Very much appreciated.

regards Steve

|||

Still I am facing the same problem.

Create my login account mean ?

|||

Hi Stefaan,

Still i am facing the same problem. can you help me out.

i have done the following steps:

=======================

1) Change the SQL server Browser service running Account to Local System Account

2) Create My Login Account in SQL >Security->Login

I have server name : EZ\SQLExpress and i have created login for ramki i.e. EZ\Ramki and pointed to adventurework database too

3) Double click on AdvantureWork.ds from DataSource (From Solution Explorer). and in Impersonation Information Select the Use the Service Account.

Please can you help me out.

Regards

Ramki

|||

Ramki

Firstly go to Start, SQL Server 2005/Express and then goto 'configuration tools'

Click on the SQL Server Browser and then right click to bring up the context menu and select PROPERTIES.

Ensure that "Built-in Account" is set to local system.

Then

USE any Login with sysadmin rights for AdventureworksDW or (Create any SQL Server Login using MASTER).

Now if you are working standalone you may have another ID to use such as sa. I prefer NOT to use sa for obvious reasons.

If you choose to create a new Login ID, it too MUST have SYSADMIN rights.

Change databases to AdventureworksDW and now 'attach' i.e. add the login to the database.

I am running under windows 2003 server and therefore utilize windows authentication and have an ID in SQL Server 2005 called 'Administrator' which has been allocated sysadmin rights and has sysadmin rights on database AdventureworksDW.

The important thing is that the ID has sysadmin rights in the database that you are trying to connect to.

In this case AdventureworksDW.

THEN

Within your Visual Studio 2005 environment (where you created your project and where you created a data source to link to the ADVENTUREWORKSDW database),double click on the DataSource (in your Solution Explorer) and select the "Impersonation Tab". Select the "Use the Service Account".

This should be all that you need to do. It worked for me.

I hope that this helps.

|||

Hi All,

I tried all that is mentioned in this thread but still facing the problem.

The error

Error 1 The project could not be deployed to the '172.24.185.73\SQL2K5' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0

1. SQL Browser service is running under local system account and tried after restarting as well.

2. New Login created (with Sysadmin) on my database using Administrator win account

3. All the settings done mentioned above under impersonation tab

4. Built in Account is set to Local Account.

Any idea/suggestion would be of great help.

Thank

Atgos

Can't Deploy AdventureWorks AS Database using Instances of SQL and AS

Hi all,

Have just installed June CTP on a new laptop build and am having problems getting the AdventureWorks AS database deployed. Using a named instance of SQL Server 2005 (which houses the AdventureWorksDW database) and a named instance of Analysis Services 2005 (which should be getting the deployed database).

After opening the sample solution in BIDS, I changed the data source definition to point to my SQL Server 2005 instance (laptop\sql2005). Tested fine. Then modified the project properties to use the Analysis Services 2005 instance as the deployment location (also laptop\sql2005). Everything looks fine, but when I try to deploy, I get the following three errors:

Error 1: Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the 'localhost' server.

Error 2: Errors in the OLE DB provider. An error occurred while named instance information was being retrieved from the SQLBrowser service on the 'localhost' server.

Error 3: Either a connection cannot be made to the localhost\SQL2005 server, or Analysis Services is not running on the computer specified.

So, it looks as if the deployment process is somehow trying to connect to "localhost" instead of my machine name and it also seems as if there's a problem with the SQLBrowser service. I've checked and the service is running.

Probably something silly that I'm either forgetting or overlooking. Anyone?

Thanks,
Dave FacklerJust to close the loop, I was able to deploy after removing all of the data mining objects from the project (sorry DM team!) When I get a chance to review it more closely later on, I'll try to figure out what was causing the problem. But for now, a workaround exists in case anyone else runs into a similar issue.

Dave Fackler|||Hi,

I have the a similar problem. I can't deploy an AdventureWorks cube in analysis services. I got the following error message:

"Error 1 The project could not be deployed to the 'Computer\SQL_SERVER_2005' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0"

There are no data mining objects in my project. I modified already the deployment properties in the project.

Best regards,
Wolfgang

Dave Fackler wrote:

Just to close the loop, I was able to deploy after removing all of the data mining objects from the project (sorry DM team!) When I get a chance to review it more closely later on, I'll try to figure out what was causing the problem. But for now, a workaround exists in case anyone else runs into a similar issue.

Dave Fackler

|||I was having the same problem yesterday and found that the Analysis Services service was stopped. No idea on how, but after starting it my connection-related deployment problem went away.|||

Wolfgang W. wrote:

Hi,

I have the a similar problem. I can't deploy an AdventureWorks cube in analysis services. I got the following error message:

"Error 1 The project could not be deployed to the 'Computer\SQL_SERVER_2005' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0"

There are no data mining objects in my project. I modified already the deployment properties in the project.

Best regards,
Wolfgang

Dave Fackler

|||

Just out of curiosity has anyone resolved this one as I have the same problem with NO MINING.

regards Steve

|||

Hi,

I also faced same issue while trying to deploying AdvantureWords Analysis service.

I changed the following setting and after that i am able to deploy successfully.

1) Change the SQL server Browser service running Account to Local System Account

2) Create My Login Account in SQL >Security->Login

3) Double click on AdvantureWork.ds from DataSource (From Solution Explorer). and in Impersonation Information Select the Use the Service Account.

after apply all these steps i am able to deployed analysis services.

thanks

AVNISH KUMAR SHARMA

|||

Hi

Thanks a million. I shall try it and let you know. very much appreciated.

regards Steve

|||

Hi

You were spot on.. Thanks ever so much for your help. It worked. :)

For others folks...re point 2 in Avnish's note. He is referring to the login id of the machine that hosts SQL Server.

In my case it was Adminstrator.

regards Steve

|||

Hi AVNISH

Thanks very much. That solved the problem. Very much appreciated.

regards Steve

|||

Still I am facing the same problem.

Create my login account mean ?

|||

Hi Stefaan,

Still i am facing the same problem. can you help me out.

i have done the following steps:

=======================

1) Change the SQL server Browser service running Account to Local System Account

2) Create My Login Account in SQL >Security->Login

I have server name : EZ\SQLExpress and i have created login for ramki i.e. EZ\Ramki and pointed to adventurework database too

3) Double click on AdvantureWork.ds from DataSource (From Solution Explorer). and in Impersonation Information Select the Use the Service Account.

Please can you help me out.

Regards

Ramki

|||

Ramki

Firstly go to Start, SQL Server 2005/Express and then goto 'configuration tools'

Click on the SQL Server Browser and then right click to bring up the context menu and select PROPERTIES.

Ensure that "Built-in Account" is set to local system.

Then

USE any Login with sysadmin rights for AdventureworksDW or (Create any SQL Server Login using MASTER).

Now if you are working standalone you may have another ID to use such as sa. I prefer NOT to use sa for obvious reasons.

If you choose to create a new Login ID, it too MUST have SYSADMIN rights.

Change databases to AdventureworksDW and now 'attach' i.e. add the login to the database.

I am running under windows 2003 server and therefore utilize windows authentication and have an ID in SQL Server 2005 called 'Administrator' which has been allocated sysadmin rights and has sysadmin rights on database AdventureworksDW.

The important thing is that the ID has sysadmin rights in the database that you are trying to connect to.

In this case AdventureworksDW.

THEN

Within your Visual Studio 2005 environment (where you created your project and where you created a data source to link to the ADVENTUREWORKSDW database),double click on the DataSource (in your Solution Explorer) and select the "Impersonation Tab". Select the "Use the Service Account".

This should be all that you need to do. It worked for me.

I hope that this helps.

Can't Deploy AdventureWorks AS Database using Instances of SQL and AS

Hi all,

Have just installed June CTP on a new laptop build and am having problems getting the AdventureWorks AS database deployed. Using a named instance of SQL Server 2005 (which houses the AdventureWorksDW database) and a named instance of Analysis Services 2005 (which should be getting the deployed database).

After opening the sample solution in BIDS, I changed the data source definition to point to my SQL Server 2005 instance (laptop\sql2005). Tested fine. Then modified the project properties to use the Analysis Services 2005 instance as the deployment location (also laptop\sql2005). Everything looks fine, but when I try to deploy, I get the following three errors:

Error 1: Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the 'localhost' server.

Error 2: Errors in the OLE DB provider. An error occurred while named instance information was being retrieved from the SQLBrowser service on the 'localhost' server.

Error 3: Either a connection cannot be made to the localhost\SQL2005 server, or Analysis Services is not running on the computer specified.

So, it looks as if the deployment process is somehow trying to connect to "localhost" instead of my machine name and it also seems as if there's a problem with the SQLBrowser service. I've checked and the service is running.

Probably something silly that I'm either forgetting or overlooking. Anyone?

Thanks,
Dave Fackler
Just to close the loop, I was able to deploy after removing all of the data mining objects from the project (sorry DM team!) When I get a chance to review it more closely later on, I'll try to figure out what was causing the problem. But for now, a workaround exists in case anyone else runs into a similar issue.

Dave Fackler
|||Hi,

I have the a similar problem. I can't deploy an AdventureWorks cube in analysis services. I got the following error message:

"Error 1 The project could not be deployed to the 'Computer\SQL_SERVER_2005' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0"

There are no data mining objects in my project. I modified already the deployment properties in the project.

Best regards,
Wolfgang

Dave Fackler wrote:

Just to close the loop, I was able to deploy after removing all of the data mining objects from the project (sorry DM team!) When I get a chance to review it more closely later on, I'll try to figure out what was causing the problem. But for now, a workaround exists in case anyone else runs into a similar issue.

Dave Fackler

|||I was having the same problem yesterday and found that the Analysis Services service was stopped. No idea on how, but after starting it my connection-related deployment problem went away.|||

Wolfgang W. wrote:

Hi,

I have the a similar problem. I can't deploy an AdventureWorks cube in analysis services. I got the following error message:

"Error 1 The project could not be deployed to the 'Computer\SQL_SERVER_2005' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0"

There are no data mining objects in my project. I modified already the deployment properties in the project.

Best regards,
Wolfgang

Dave Fackler

|||

Just out of curiosity has anyone resolved this one as I have the same problem with NO MINING.

regards Steve

|||

Hi,

I also faced same issue while trying to deploying AdvantureWords Analysis service.

I changed the following setting and after that i am able to deploy successfully.

1) Change the SQL server Browser service running Account to Local System Account

2) Create My Login Account in SQL >Security->Login

3) Double click on AdvantureWork.ds from DataSource (From Solution Explorer). and in Impersonation Information Select the Use the Service Account.

after apply all these steps i am able to deployed analysis services.

thanks

AVNISH KUMAR SHARMA

|||

Hi

Thanks a million. I shall try it and let you know. very much appreciated.

regards Steve

|||

Hi

You were spot on.. Thanks ever so much for your help. It worked. :)

For others folks...re point 2 in Avnish's note. He is referring to the login id of the machine that hosts SQL Server.

In my case it was Adminstrator.

regards Steve

|||

Hi AVNISH

Thanks very much. That solved the problem. Very much appreciated.

regards Steve

|||

Still I am facing the same problem.

Create my login account mean ?

|||

Hi Stefaan,

Still i am facing the same problem. can you help me out.

i have done the following steps:

=======================

1) Change the SQL server Browser service running Account to Local System Account

2) Create My Login Account in SQL >Security->Login

I have server name : EZ\SQLExpress and i have created login for ramki i.e. EZ\Ramki and pointed to adventurework database too

3) Double click on AdvantureWork.ds from DataSource (From Solution Explorer). and in Impersonation Information Select the Use the Service Account.

Please can you help me out.

Regards

Ramki

|||

Ramki

Firstly go to Start, SQL Server 2005/Express and then goto 'configuration tools'

Click on the SQL Server Browser and then right click to bring up the context menu and select PROPERTIES.

Ensure that "Built-in Account" is set to local system.

Then

USE any Login with sysadmin rights for AdventureworksDW or (Create any SQL Server Login using MASTER).

Now if you are working standalone you may have another ID to use such as sa. I prefer NOT to use sa for obvious reasons.

If you choose to create a new Login ID, it too MUST have SYSADMIN rights.

Change databases to AdventureworksDW and now 'attach' i.e. add the login to the database.

I am running under windows 2003 server and therefore utilize windows authentication and have an ID in SQL Server 2005 called 'Administrator' which has been allocated sysadmin rights and has sysadmin rights on database AdventureworksDW.

The important thing is that the ID has sysadmin rights in the database that you are trying to connect to.

In this case AdventureworksDW.

THEN

Within your Visual Studio 2005 environment (where you created your project and where you created a data source to link to the ADVENTUREWORKSDW database),double click on the DataSource (in your Solution Explorer) and select the "Impersonation Tab". Select the "Use the Service Account".

This should be all that you need to do. It worked for me.

I hope that this helps.

Can't Deploy AdventureWorks AS Database using Instances of SQL and AS

Hi all,

Have just installed June CTP on a new laptop build and am having problems getting the AdventureWorks AS database deployed. Using a named instance of SQL Server 2005 (which houses the AdventureWorksDW database) and a named instance of Analysis Services 2005 (which should be getting the deployed database).

After opening the sample solution in BIDS, I changed the data source definition to point to my SQL Server 2005 instance (laptop\sql2005). Tested fine. Then modified the project properties to use the Analysis Services 2005 instance as the deployment location (also laptop\sql2005). Everything looks fine, but when I try to deploy, I get the following three errors:

Error 1: Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the 'localhost' server.

Error 2: Errors in the OLE DB provider. An error occurred while named instance information was being retrieved from the SQLBrowser service on the 'localhost' server.

Error 3: Either a connection cannot be made to the localhost\SQL2005 server, or Analysis Services is not running on the computer specified.

So, it looks as if the deployment process is somehow trying to connect to "localhost" instead of my machine name and it also seems as if there's a problem with the SQLBrowser service. I've checked and the service is running.

Probably something silly that I'm either forgetting or overlooking. Anyone?

Thanks,
Dave FacklerJust to close the loop, I was able to deploy after removing all of the data mining objects from the project (sorry DM team!) When I get a chance to review it more closely later on, I'll try to figure out what was causing the problem. But for now, a workaround exists in case anyone else runs into a similar issue.

Dave Fackler|||Hi,

I have the a similar problem. I can't deploy an AdventureWorks cube in analysis services. I got the following error message:

"Error 1 The project could not be deployed to the 'Computer\SQL_SERVER_2005' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0"

There are no data mining objects in my project. I modified already the deployment properties in the project.

Best regards,
Wolfgang

Dave Fackler wrote:

Just to close the loop, I was able to deploy after removing all of the data mining objects from the project (sorry DM team!) When I get a chance to review it more closely later on, I'll try to figure out what was causing the problem. But for now, a workaround exists in case anyone else runs into a similar issue.

Dave Fackler

|||I was having the same problem yesterday and found that the Analysis Services service was stopped. No idea on how, but after starting it my connection-related deployment problem went away.|||

Wolfgang W. wrote:

Hi,

I have the a similar problem. I can't deploy an AdventureWorks cube in analysis services. I got the following error message:

"Error 1 The project could not be deployed to the 'Computer\SQL_SERVER_2005' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0"

There are no data mining objects in my project. I modified already the deployment properties in the project.

Best regards,
Wolfgang

Dave Fackler

|||

Just out of curiosity has anyone resolved this one as I have the same problem with NO MINING.

regards Steve

|||

Hi,

I also faced same issue while trying to deploying AdvantureWords Analysis service.

I changed the following setting and after that i am able to deploy successfully.

1) Change the SQL server Browser service running Account to Local System Account

2) Create My Login Account in SQL >Security->Login

3) Double click on AdvantureWork.ds from DataSource (From Solution Explorer). and in Impersonation Information Select the Use the Service Account.

after apply all these steps i am able to deployed analysis services.

thanks

AVNISH KUMAR SHARMA

|||

Hi

Thanks a million. I shall try it and let you know. very much appreciated.

regards Steve

|||

Hi

You were spot on.. Thanks ever so much for your help. It worked. :)

For others folks...re point 2 in Avnish's note. He is referring to the login id of the machine that hosts SQL Server.

In my case it was Adminstrator.

regards Steve

|||

Hi AVNISH

Thanks very much. That solved the problem. Very much appreciated.

regards Steve

|||

Still I am facing the same problem.

Create my login account mean ?

|||

Hi Stefaan,

Still i am facing the same problem. can you help me out.

i have done the following steps:

=======================

1) Change the SQL server Browser service running Account to Local System Account

2) Create My Login Account in SQL >Security->Login

I have server name : EZ\SQLExpress and i have created login for ramki i.e. EZ\Ramki and pointed to adventurework database too

3) Double click on AdvantureWork.ds from DataSource (From Solution Explorer). and in Impersonation Information Select the Use the Service Account.

Please can you help me out.

Regards

Ramki

|||

Ramki

Firstly go to Start, SQL Server 2005/Express and then goto 'configuration tools'

Click on the SQL Server Browser and then right click to bring up the context menu and select PROPERTIES.

Ensure that "Built-in Account" is set to local system.

Then

USE any Login with sysadmin rights for AdventureworksDW or (Create any SQL Server Login using MASTER).

Now if you are working standalone you may have another ID to use such as sa. I prefer NOT to use sa for obvious reasons.

If you choose to create a new Login ID, it too MUST have SYSADMIN rights.

Change databases to AdventureworksDW and now 'attach' i.e. add the login to the database.

I am running under windows 2003 server and therefore utilize windows authentication and have an ID in SQL Server 2005 called 'Administrator' which has been allocated sysadmin rights and has sysadmin rights on database AdventureworksDW.

The important thing is that the ID has sysadmin rights in the database that you are trying to connect to.

In this case AdventureworksDW.

THEN

Within your Visual Studio 2005 environment (where you created your project and where you created a data source to link to the ADVENTUREWORKSDW database),double click on the DataSource (in your Solution Explorer) and select the "Impersonation Tab". Select the "Use the Service Account".

This should be all that you need to do. It worked for me.

I hope that this helps.

Can't decompose a view on a view into one view

Sorry if that title is confusing.
Say I have a view on a table. And then say I build a view using that view.
Shouldn't I, theoretically, always be able to decompose a view built on
another view into a single view built on the underlying table(s)? I know
that SQL Server has to do this eventually. Obviously I'm doing something
wrong.
I'm trying to find the stock and fund "postions" (number of shares) for
client accounts as of a given date. We have all asset positions at the end
of each month, and then we get changed postitions whenever they change
during the month.
I know this is not a new problem, but I went ahead and wrote the SQL that I
thought would give the answer. Maybe I should have searched for the common
way of doing this. My real question is why can't I write this as one SQL
statement.
DDL is listed below. I started with a view that returns all end-of-January
positions plus all positions through the given date (which is only 3 days
later, February 3rd). The first view is here. I'm not gonna hard code
dates into my views in production; this is an experiment:
Create View Pos20050203 As
Select SSN_Tin, Acct_Number, Fund_ID, Position_Date, Quantity_ASD
From Positions Where Position_Date between '2005-01-31' and '2005-02-03'
Now that's simple enough. The next step is to find the latest position
date for each position:
SELECT SSN_Tin, Acct_Number, Fund_ID, MAX(Position_Date) AS LastPosDate
FROM Pos20050203
GROUP BY SSN_Tin, Acct_Number, Fund_ID)
The next step... Let me show you my final attempt at putting this into one
SQL statement and you can all flame me on what I'm doing wrong:
Select * From
(Select SSN_Tin, Acct_Number, Fund_ID, Position_Date, Quantity_ASD
From Positions
Where Position_Date between '2005-01-31' and '2005-02-03')
AS Pos20050203
Where Exists
(Select * From
(Select SSN_Tin, Acct_Number, Fund_ID, Max(Position_Date) AS LastPosDate
From Pos20050203
Group by SSN_Tin, Acct_Number, Fund_id) AS Derived
Where Derived.ssn_tin = Pos20050203.ssn_tin
And Derived.acct_number = Pos20050203.acct_number
And Derived.fund_id = Pos20050203.fund_id
And Derived.LastPosDate = Pos20050203.Position_Date)
I get "Invalid object name 'Pos20050203'." I wish QA would tell me WHICH
reference to this name is invalid; probably one in the last Where clause.
The table is certainly named a couple of steps earlier in the process.
How can this be fixed?
Oh, SSN_Tin is char(9), Acct_Number is varchar(20), Fund_ID is varchar(22),
Position_Date is datetime, and Quantity_asd is decimal.
Any help is appreciated.
David Walker...say I have a view on a table. And then say I build a view using
that view. Shouldn't I, theoretically, always be able to decompose a
view built on
another view into a single view built on the underlying table(s)?
...
that's not true. if you build and view then refer to that view in
another query, that's fine, because the first view has been complied.
if you just 'decompose' the first view in the 2nd query, it mgiht not
work. in ur case, the invalid object refers to this section...
Where Exists
(Select * From
(Select SSN_Tin, Acct_Number, Fund_ID, Max(Position_Date) AS
LastPosDate
>From Pos20050203
..
because Pos20050203 is in the same query, when sql executes the query,
it's not there yet.
if you use stored procedure and declare the temp table for the first
part of the query then it will work. (or you can create a view for the
first part)|||"DWalker" <none@.none.com> wrote in message
news:uImJFrRFFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Sorry if that title is confusing.
> Say I have a view on a table. And then say I build a view using that
view.
> Shouldn't I, theoretically, always be able to decompose a view built on
> another view into a single view built on the underlying table(s)?

> Create View Pos20050203 As
> Select SSN_Tin, Acct_Number, Fund_ID, Position_Date, Quantity_ASD
> From Positions Where Position_Date between '2005-01-31' and '2005-02-03'
> SELECT SSN_Tin, Acct_Number, Fund_ID, MAX(Position_Date) AS LastPosDate
> FROM Pos20050203
> GROUP BY SSN_Tin, Acct_Number, Fund_ID)
Generally, just replace the viewname with the select that the view
represents.
Note that you must use an alias on the subquery.
So:
SELECT SSN_Tin, Acct_Number, Fund_ID, MAX(Position_Date) AS LastPosDate
FROM ( SELECT Select SSN_Tin, Acct_Number, Fund_ID, Position_Date,
Quantity_ASD
FROM Positions
WHERE Position_Date between '2005-01-31' and '2005-02-03') t
GROUP BY SSN_Tin, Acct_Number, Fund_ID
Good Luck,
Jim|||"James Goodwin" <jim.goodwin@.midmichigan.org> wrote in news:b424$4214e40a
$432498ca$26444@.allthenewsgroups.com:

> Generally, just replace the viewname with the select that the view
> represents.
Yes, but I hate repeating clauses, and sometimes I get parentheses in the
wrong places.
I now have this, and it works. (Pay no attention to the Select *)
Select * from
(Select * From PositionView Where Position_Date Between '2005-01-31' and
'2005-02-03') As PositionSlice
Where Exists
(Select * From
(Select SSN_Tin, Acct_Number, Fund_ID, Max(Position_Date) as LastDate
From PositionView
Where Position_Date Between '2005-01-31' and '2005-02-03'
Group by ssn_tin, acct_number, fund_id) As LastPos
Where PositionSlice.SSN_Tin = LastPos.SSN_Tin
And PositionSlice.Acct_Number = LastPos.Acct_Number
And PositionSlice.Fund_ID = LastPos.Fund_ID
And PositionSlice.Position_Date = LastPos.LastDate)
************
Question: Is there any way with table aliases to avoid repeating the
"Position_Date Between" clause in the query? I know it might not be
faster, but it will be simpler. I can't figure out how to do that.
Thanks.
David Walker|||"DWalker" <none@.none.com> wrote in message
news:euOEULSFFHA.3312@.TK2MSFTNGP15.phx.gbl...

> Select * from
> (Select * From PositionView Where Position_Date Between '2005-01-31' and
> '2005-02-03') As PositionSlice
> Where Exists
> (Select * From
> (Select SSN_Tin, Acct_Number, Fund_ID, Max(Position_Date) as LastDate
> From PositionView
> Where Position_Date Between '2005-01-31' and '2005-02-03'
> Group by ssn_tin, acct_number, fund_id) As LastPos
> Where PositionSlice.SSN_Tin = LastPos.SSN_Tin
> And PositionSlice.Acct_Number = LastPos.Acct_Number
> And PositionSlice.Fund_ID = LastPos.Fund_ID
> And PositionSlice.Position_Date = LastPos.LastDate)
Maybe Try something like this:
Select * from PositionView p
inner join (Select SSN_Tin, Acct_number, Fund_id,
Max(Position_Date) as lastdate
From positionView where Position_date
Between '20050131' and '20050203') l
on p.SSN_Tin = l.SSN_Tin
and p.Acct_number = l.Acct_number
and p.Fund_Id = l.Fund_Id
and p.Position_Date = l.LastDate
Good Luck,
Jim|||That doesn't work. See below...
"James Goodwin" <jim.goodwin@.midmichigan.org> wrote in
news:88153$4214fc66$432498ca$7045@.allthe
newsgroups.com:

> "DWalker" <none@.none.com> wrote in message
> news:euOEULSFFHA.3312@.TK2MSFTNGP15.phx.gbl...
>
>
> Maybe Try something like this:
> Select * from PositionView p
> inner join (Select SSN_Tin, Acct_number, Fund_id,
> Max(Position_Date) as lastdate
> From positionView where Position_date
> Between '20050131' and '20050203') l
> on p.SSN_Tin = l.SSN_Tin
> and p.Acct_number = l.Acct_number
> and p.Fund_Id = l.Fund_Id
> and p.Position_Date = l.LastDate
> Good Luck,
> Jim
>
Your suggestion returns these errors:
Server: Msg 8118, Level 16, State 1, Line 1
Column 'positionView.SSN_TIN' is invalid in the select list because it
is not contained in an aggregate function and there is no GROUP BY
clause. Server: Msg 8118, Level 16, State 1, Line 1
Column 'positionView.ACCT_NUMBER' is invalid in the select list because
it is not contained in an aggregate function and there is no GROUP BY
clause. Server: Msg 8118, Level 16, State 1, Line 1
Column 'positionView.FUND_ID' is invalid in the select list because it
is not contained in an aggregate function and there is no GROUP BY
clause.
I'm wondering if it's even possible to do what I'm doing wihtout
spelling out the "Position_Date Between" clause twice...
DAvid|||On Thu, 17 Feb 2005 10:59:36 -0800, DWalker wrote:

>"James Goodwin" <jim.goodwin@.midmichigan.org> wrote in news:b424$4214e40a
>$432498ca$26444@.allthenewsgroups.com:
>
>Yes, but I hate repeating clauses, and sometimes I get parentheses in the
>wrong places.
>I now have this, and it works. (Pay no attention to the Select *)
(snip)
>Question: Is there any way with table aliases to avoid repeating the
>"Position_Date Between" clause in the query? I know it might not be
>faster, but it will be simpler. I can't figure out how to do that.
Hi David,
You can't always prevent having to repeat things. But in your case, I
think it is possible. Looking at just your query, without knowing your
tables and therefore unable to test it, I think you should get the same
results from
Select Column1, Column2, ..., ColumnN from
PositionView As PositionSlice
Where Exists
(Select * From
(Select SSN_Tin, Acct_Number, Fund_ID, Max(Position_Date) as LastDate
From PositionView
Where Position_Date Between '2005-01-31' and '2005-02-03'
Group by ssn_tin, acct_number, fund_id) As LastPos
Where PositionSlice.SSN_Tin = LastPos.SSN_Tin
And PositionSlice.Acct_Number = LastPos.Acct_Number
And PositionSlice.Fund_ID = LastPos.Fund_ID
And PositionSlice.Position_Date = LastPos.LastDate)
Or even
SELECT Column1, Column2, ..., ColumnN
FROM PositionView AS PositionSlice
WHERE Position_Date =
(SELECT MAX(Position_Date)
FROM Position_View AS LastPos
WHERE LastPos.Position_Date BETWEEN '2005-01-31' AND '2005-02-03'
AND PositionSlice.SSN_Tin = LastPos.SSN_Tin
AND PositionSlice.Acct_Number = LastPos.Acct_Number
AND PositionSlice.Fund_ID = LastPos.Fund_ID)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"DWalker" <none@.none.com> wrote in message
news:#28IjwTFFHA.1392@.tk2msftngp13.phx.gbl...
> That doesn't work. See below...
> Your suggestion returns these errors:
> Server: Msg 8118, Level 16, State 1, Line 1
> Column 'positionView.SSN_TIN' is invalid in the select list because it
> is not contained in an aggregate function and there is no GROUP BY
> clause. Server: Msg 8118, Level 16, State 1, Line 1
> Column 'positionView.ACCT_NUMBER' is invalid in the select list because
> it is not contained in an aggregate function and there is no GROUP BY
> clause. Server: Msg 8118, Level 16, State 1, Line 1
> Column 'positionView.FUND_ID' is invalid in the select list because it
> is not contained in an aggregate function and there is no GROUP BY
> clause.
So add the group by clause, keep in mind, I don't have your tables and can't
test these:
Select * from PositionView p
inner join (Select SSN_Tin, Acct_number, Fund_id, Max(Position_Date)
as lastdate
From positionView where Position_date Between
'20050131' and '20050203'
Group by SSN_Tin, Acct_number, Fund_id) t
on p.SSN_Tin = l.SSN_Tin
and p.Acct_number = l.Acct_number
and p.Fund_Id = l.Fund_Id
and p.Position_Date = l.LastDate|||Thanks, I'll look at that.
David
Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in
news:agdb11tunlg45j5o3bru281vc7dpjbc0k2@.
4ax.com:

> On Thu, 17 Feb 2005 10:59:36 -0800, DWalker wrote:
>
> (snip)
> Hi David,
> You can't always prevent having to repeat things. But in your case, I
> think it is possible. Looking at just your query, without knowing your
> tables and therefore unable to test it, I think you should get the
> same results from
> Select Column1, Column2, ..., ColumnN from
> PositionView As PositionSlice
> Where Exists
> (Select * From
> (Select SSN_Tin, Acct_Number, Fund_ID, Max(Position_Date) as LastDate
> From PositionView
> Where Position_Date Between '2005-01-31' and '2005-02-03'
> Group by ssn_tin, acct_number, fund_id) As LastPos
> Where PositionSlice.SSN_Tin = LastPos.SSN_Tin
> And PositionSlice.Acct_Number = LastPos.Acct_Number
> And PositionSlice.Fund_ID = LastPos.Fund_ID
> And PositionSlice.Position_Date = LastPos.LastDate)
> Or even
> SELECT Column1, Column2, ..., ColumnN
> FROM PositionView AS PositionSlice
> WHERE Position_Date =
> (SELECT MAX(Position_Date)
> FROM Position_View AS LastPos
> WHERE LastPos.Position_Date BETWEEN '2005-01-31' AND
> '2005-02-03' AND PositionSlice.SSN_Tin = LastPos.SSN_Tin
> AND PositionSlice.Acct_Number = LastPos.Acct_Number
> AND PositionSlice.Fund_ID = LastPos.Fund_ID)
> Best, Hugo|||Thanks, I'll look at that. You have an a alias t where I think you mean
l, but otherwise it looks OK. (I don't like one-letter aliases, but
that's just my preference. Something like LastPos is more meaningful
than l. And l looks like I anyway, depending on the font. But thanks
for the help!
David Walker
"James Goodwin" <jim.goodwin@.midmichigan.org> wrote in
news:beab9$4215f005$432498ca$15926@.allth
enewsgroups.com:

> "DWalker" <none@.none.com> wrote in message
> news:#28IjwTFFHA.1392@.tk2msftngp13.phx.gbl...
> So add the group by clause, keep in mind, I don't have your tables and
> can't test these:
> Select * from PositionView p
> inner join (Select SSN_Tin, Acct_number, Fund_id,
> Max(Position_Date)
> as lastdate
> From positionView where Position_date Between
> '20050131' and '20050203'
> Group by SSN_Tin, Acct_number, Fund_id) t
> on p.SSN_Tin = l.SSN_Tin
> and p.Acct_number = l.Acct_number
> and p.Fund_Id = l.Fund_Id
> and p.Position_Date = l.LastDate
>