Analysis Manager Error

I've been getting the following error message whenever I
try to connect to my remote server to work on some cubes.
"Cannot open connection to Analysis server 'WRPBI'
Error in data [possible data corruption]"
How can I fix it? I've reinstalled Analysis Manager but
it didn't help.
Thanks
Hi sacred,
You should apply SP3 for Analysis Services to the Analysis Services server.
If you are unable to upgrade the Analysis Services server to SP3, you can
also restore connectivity to the Analysis Server by replacing the
MSOLAP80.DLL,
MSOLAP80.RLL, MSMDGD80.DLL and MSMDCB80.DLL files on the client machine
with a
version equal to or less than the build number of your MSMDSRV.EXE
<><><><><><><><><><><><><><><><><><><><><><><><>
| Content-Class: urn:content-classes:message
| From: "sacred" <sacred21@.airpost.net>
| Sender: "sacred" <sacred21@.airpost.net>
| Subject: Analysis Manager Error
| Date: Wed, 29 Dec 2004 18:07:15 -0800
| Lines: 12
| Message-ID: <05dc01c4ee14$4852d080$a401280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcTuFEhSQNjxLD0AQgyujxk4ZUZu2w==
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.sqlserver.clients
| Path: cpmsftngxa10.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.clients:29328
| NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
| X-Tomcat-NG: microsoft.public.sqlserver.clients
|
| I've been getting the following error message whenever I
| try to connect to my remote server to work on some cubes.
|
| "Cannot open connection to Analysis server 'WRPBI'
| Error in data [possible data corruption]"
|
| How can I fix it? I've reinstalled Analysis Manager but
| it didn't help.
|
| Thanks
|
|
|
<><><><><><><><><><><><><><><><><><><><><><><><>
Yasemin Gunduz
Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.

Analysis manager cube processing error

I am trying to process a cube and during processing I get a message that " Data source provider error :[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified..." I set up my data connections and everything else as it appeared in the tutorial. I am running XP Professional and MS Server 2000.To validate your connection create a file on your desktop with UDL extension. Double-click on it and fill in the blanks. Once done, - rename the file to .TXT and open it in notepad. Modify as needed and use it.|||I did all this and still I don't get the cubes to process. Do I need to rename the file to UDL after everything? When I process now I get the message "Data Source Provider Error:[Microsoft][ODBC Driver Manager]Driver's SQLSetConnectAttr failed;IM006..." What should the provider name be in the UDL file? Mine says MSDASQL.1|||As an example, here is what I have in notepad after I create a connection:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="driver={sql server};server=l6064909;database=master;trusted_co nnection=true"|||I got the cube to process, now when I click on browse data I get a message that states "Unable to browse the cube 'Sales'. Unspecified error." Is this because of the service pack?

Analysis 2005 Cube Processing in 64-Bit Environment

I have problems with processing cubes on AS2005. I get this error message while processing:

Memory error: Allocation failure: Not enough storage is available to process this command. Error in the OLAP storage engine.

This machine has 6GB of memory and once it hits 3.5GB the processing stops. AWE is turned on.

I would appreciate, if anyone can help on this.

Thanks,

Gopal

Analysis Services is not AWE aware. So it can only use 2GB on a 32bit box (3GB if you have /3GB switch in boot.ini). Looks like the processing operation is running out of memory.

What are the number and sizes of dimension and partition tables? Do you have aggregations? Look at the processing log (in the process dialog) and find out what is the operation that causes out of memory - dimension processing, partition processing, building aggregations, etc.

This could also happen due to excessive parallelism. Try processing the dimensions and partitions individually.

|||

Thanks for your reply.

I managed to process the partitions and dimensions individually. That worked.

Gopal

|||

Hi.

Just a short question:

Is it really true that SSAS can use only 2GB RAM on a 32bit system? The technical reference for SQL Server 2005 Standard and Enterprise states that SQL Server can use the amount of RAM supported by the operating system which is 4GB for a Server 2003 Standard edition and 32GB for a Server 2003 Enterprise edition, both on 32bit systems. Does this apply only to the SQL Server Database Engine then and not the Analysis Services Server?

Regards

Kjetil

T.K. Anand wrote:

Analysis Services is not AWE aware. So it can only use 2GB on a 32bit box (3GB if you have /3GB switch in boot.ini). Looks like the processing operation is running out of memory.

What are the number and sizes of dimension and partition tables? Do you have aggregations? Look at the processing log (in the process dialog) and find out what is the operation that causes out of memory - dimension processing, partition processing, building aggregations, etc.

This could also happen due to excessive parallelism. Try processing the dimensions and partitions individually.

|||

In the 64-bit versions of Analysis Services...has this memory use limit been removed?

...cordell...

|||Yes - 64bit version doesn't have these limitations.

An unexplained error message for an Xquery

Hello,
When I issue this query:

select doc.query ('
for $b in /a/b,
$c in /a/c
return $b,$c
')
from T1
where id=6

I recieve this error message:
.Net SqlClient Data Provider: Msg 2227, Level 16, State 1, Line 5
XQuery [T1.doc.query()]: The variable '$c' was not found in the scope in which it was referenced.

Interestingly, when I reverse the order of the variables in the return clause (i.e. make it c$,$b ), the unidentified variable in the error message becomes $b instead of $c. i.e. the system always does not identify the second variable. The query always works fine if the return clause has only one variable, be it $b or $c.

Am I missing something here, or is it a bug?

thanks
-Arsany

The behavior is correct in this case. The problem here is with the precedence of the ',' operator with respect to the FLWOR statement. Basically, the ',' operator in the return statement is not binding to the return clause of the FLWOR, but rather creating a new XQuery expression. If you put parenthesis around the return statement (as shown below) this will enforce the precedence that you want in your query:

select doc.query ('
for $b in /a/b,
$c in /a/c
return ($b,$c)
')
from T1
where id=6
|||Thank you Mike
-Arsany Sawiressql

An existing connection was forcibly closed by the remote host

Hi,

I'm running a website using MSSQL 2000. Sometimes (not always) I get this error message on the website:

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

I have also websites running with MySQL and those don't have this problem.
I searched google but I couldn't find usefull information on this problem.

Someone who knows what's going on here ?

All I've found untill now is that it has something to do with the server not responding within a certain time, and thus assuming the connection was closed.

I can't find how I can just let it reconnect again in stead of giving me this error message?

An exception was thrown while trying to delete a maintenance plan.

Receive a message that an exception was thrown while trying to delete a
maintenance plan.
SQL 2005 with spk 2aSQLdba wrote:
> Receive a message that an exception was thrown while trying to delete a
> maintenance plan.
> SQL 2005 with spk 2a
I posted a message yesterday regarding this problem. :-)
I believe you've created that maintenance plan under either different
user or the same user with different password. If former, connect with
the same user, if latter - change the password, delete the MP and then
change the password back.

An exception was thrown while trying to delete a maintenance plan.

Receive a message that an exception was thrown while trying to delete a
maintenance plan.
SQL 2005 with spk 2a
SQLdba wrote:
> Receive a message that an exception was thrown while trying to delete a
> maintenance plan.
> SQL 2005 with spk 2a
I posted a message yesterday regarding this problem. :-)
I believe you've created that maintenance plan under either different
user or the same user with different password. If former, connect with
the same user, if latter - change the password, delete the MP and then
change the password back.

An error occurred during the move data process: -132

Hi friend,
I'm getting the following error message when applying Service Pack 2 for Analysis Services 2000 (SQL Server):
An error occurred during the move data process: -132
Anybody can help me?
Tks,
Alex BerenguerI had the same problem. To get around it, I had to shut down nearly every service on the Win2K server (with BackOffice 2000) I was installing to. After I shut down all the services I could, the installation seemed to work correctly.

Originally posted by alexberenguer
Hi friend,

I'm getting the following error message when applying Service Pack 2 for Analysis Services 2000 (SQL Server):

An error occurred during the move data process: -132

Anybody can help me?

Tks,
Alex Berenguer

An error occurred during the execution of the SQL file InstallRoles.sql.

Error text:

An error occurred during the execution of the SQL file 'InstallRoles.sql'. The SQL error number is 446 and the SqlException message is: Cannot resolve collation conflict for equal to operation.
Cannot resolve collation conflict for equal to operation.

Erroroccurred when running 'aspnet_regsql -Sservername-E -ddatabase -A r'
with previously installed mambership scheme ondatabase.

When googling on this issue, one post on forums.asp.net is listed but link is dead...
please help...

ADDITION:

Also I forgot to mentiona few things(thanks to Kris):
dead link url:forums.asp.net/1063397/ShowPost.aspx

An error occurred during printing (0x8007F303)

Sometimes when I try to print a report from Report Manager using the ActiveX
print control, I get the following error message:
"An error occurred during printing (0x8007F303)"
I can't find any information on why I get the error or what it means.
Can anyone help?The client active-x control is corrupted. To Fix this
IE -> Tools -> InternetOption -> Settings -> View Objects -> Remove RS
Client Print object or do the update. Now click the icon from the report .
The Active-x control will be downloaded to install again freshly to get
working.
"Roberto Kohler" wrote:
> Sometimes when I try to print a report from Report Manager using the ActiveX
> print control, I get the following error message:
> "An error occurred during printing (0x8007F303)"
> I can't find any information on why I get the error or what it means.
> Can anyone help?
>
>|||Hi,
Thanks for your help.
I tried updating the active-x control as you suggested.
When I do, I get the following error message:
Internet Explorer - Security Warning
Windows has found a problem with this file.
Name: GetImage=8.00.1038.00rsclientprint.cab
Publisher: Unknown Publisher
This file was blocked because it does not have a valid digital signature
that verifies its publisher.
Then when I finish installing the RS Client Print object, and I try printing
the report, I get the same error "An error occurred during printing
(0x8007F303)"
I also tried this by removing the active-x control and the same thing
happens.

An error message i encountered during remote installation

Good day good people of the forum thanks for the response this is the error message i encountered "an error occured while attempting to validate the setup source files unc path. verify that you have entered a valid unc path."

What is the product you are trying to install? SQL 2000 or SQL 2005?

If you are using a mapped network drive my recommendation is to use the UNC path directly or copy the SKU to your local hard drive of your host machine and try again.

An error message i encountered during remote installation

Good day good people of the forum thanks for the response this is the error message i encountered "an error occured while attempting to validate the setup source files unc path. verify that you have entered a valid unc path."

What is the product you are trying to install? SQL 2000 or SQL 2005?

If you are using a mapped network drive my recommendation is to use the UNC path directly or copy the SKU to your local hard drive of your host machine and try again.

An error has occurred while establishing a connection to the server. error: 40

hi i got the following error message while connectiong to my sql server 2005

NB :i am connecting from the same pc where sq server is installed.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

Any idea?

Tarek

If you're using SQL Server 2005 Express, and you did the default install, and you're trying to connect using the sqlcmd utility, then you need to start sqlcmd like this:-

sqlcmd -S .\SQLEXPRESS

This is because the default for SQL Server Express is to install a named instance called SQLEXPRESS instead of the 'default' instance installed by other versions of SQL Server 2005.

|||I am using sql 2005 Enterprise editon on my laptop and I get the sam error. If someone knows how to resolve the problem please help, before sql 2005 I had sql 2000 installed. Thnx in advance .|||

Add me to the list of people having this problem.

Wylbur
============================

|||I installed sql server 2000 and run it. The problem solved. But I have to keep the 2000 running, otherwise, the problem will reappear.
|||Now THAT is vel-ly in-ter-es-tink.

OK guys: Does this suggest that there is a needed service that should be running at startup
but isn't?

... and, if so, which one(s)?

Wylbur
========================

|||

You can look at this blog for information on enabling remote connects. The blog is for the Express edition but this posting is applicable to all editions: https://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Cheers,
Dan

|||

Having the same problem... here is how i resolved it.

Start -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server 2005 Surface Area Configuration.

Configure Surface Area for localhost -> Surface Area Configuration for Services and Connections

MSSQLSERVER + Database Engine -> Remote Connections

Select 'Local and remote connections' instead of 'Local connections only'

Select the appropriate option under this option, i picked Using both TCP/IP and named pipes.

An error has occurred while establishing a connection to the server. error: 40

hi i got the following error message while connectiong to my sql server 2005

NB :i am connecting from the same pc where sq server is installed.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

Any idea?

Tarek

If you're using SQL Server 2005 Express, and you did the default install, and you're trying to connect using the sqlcmd utility, then you need to start sqlcmd like this:-

sqlcmd -S .\SQLEXPRESS

This is because the default for SQL Server Express is to install a named instance called SQLEXPRESS instead of the 'default' instance installed by other versions of SQL Server 2005.

|||I am using sql 2005 Enterprise editon on my laptop and I get the sam error. If someone knows how to resolve the problem please help, before sql 2005 I had sql 2000 installed. Thnx in advance .|||

Add me to the list of people having this problem.

Wylbur
============================

|||I installed sql server 2000 and run it. The problem solved. But I have to keep the 2000 running, otherwise, the problem will reappear.
|||Now THAT is vel-ly in-ter-es-tink.

OK guys: Does this suggest that there is a needed service that should be running at startup
but isn't?

... and, if so, which one(s)?

Wylbur
========================

|||

You can look at this blog for information on enabling remote connects. The blog is for the Express edition but this posting is applicable to all editions: https://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Cheers,
Dan

|||

Having the same problem... here is how i resolved it.

Start -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server 2005 Surface Area Configuration.

Configure Surface Area for localhost -> Surface Area Configuration for Services and Connections

MSSQLSERVER + Database Engine -> Remote Connections

Select 'Local and remote connections' instead of 'Local connections only'

Select the appropriate option under this option, i picked Using both TCP/IP and named pipes.

An error has occurred while establishing a connection to the server. error: 40

hi i got the following error message while connectiong to my sql server 2005

NB :i am connecting from the same pc where sq server is installed.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

Any idea?

Tarek

If you're using SQL Server 2005 Express, and you did the default install, and you're trying to connect using the sqlcmd utility, then you need to start sqlcmd like this:-

sqlcmd -S .\SQLEXPRESS

This is because the default for SQL Server Express is to install a named instance called SQLEXPRESS instead of the 'default' instance installed by other versions of SQL Server 2005.

|||I am using sql 2005 Enterprise editon on my laptop and I get the sam error. If someone knows how to resolve the problem please help, before sql 2005 I had sql 2000 installed. Thnx in advance .|||

Add me to the list of people having this problem.

Wylbur
============================

|||I installed sql server 2000 and run it. The problem solved. But I have to keep the 2000 running, otherwise, the problem will reappear.
|||Now THAT is vel-ly in-ter-es-tink.

OK guys: Does this suggest that there is a needed service that should be running at startup
but isn't?

... and, if so, which one(s)?

Wylbur
========================

|||

You can look at this blog for information on enabling remote connects. The blog is for the Express edition but this posting is applicable to all editions: https://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Cheers,
Dan

|||

Having the same problem... here is how i resolved it.

Start -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server 2005 Surface Area Configuration.

Configure Surface Area for localhost -> Surface Area Configuration for Services and Connections

MSSQLSERVER + Database Engine -> Remote Connections

Select 'Local and remote connections' instead of 'Local connections only'

Select the appropriate option under this option, i picked Using both TCP/IP and named pipes.

An attempt to attach an auto-named database for file......

I've read through all the posts in this fourm that are related to the problem I'm getting with this error message on my main form, but none of the fixes seen to solve the problem. So I hope someone can get me pointed in the right direction.

I'm running visual studio 2005 professional, and SQL Server 2005 Express Edition and this is a desktop application. I added the db file to the solution with the Add New Data Source wizard, I can create the datasets and preview the data correctly.

An attempt to attach an auto-named database for file C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\dbInventory.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

When I run the program the exception is throw in the Settings.Designer.vb code, Public ReadOnly Property for the connection string. The InnerException is this...

{"An error occurred loading a configuration file: Could not find a part of the path 'C:\Documents and Settings\<UserName>\<ApplicationName>.vshos_StrongName_1sdf1e34hkn1hqmkn2bgjjwstusfj2sg\1.0.0.0\user.config'. (C:\Documents and Settings\<UserName>\Application Data\...\<ApplicationName>.vshos_StrongName_1sdf1e34hkn1hqmkn2bgjjwstusfj2sg\1.0.0.0\user.config)"}

This folder doesn't exist anywhere on the system, any ideas....

TIA,

SQL Express associates specific database names with the paths where those databases reside. At some point, you've created a database with the same name as the one you're using now, but at a different path. When your code attempts to attach the database with the specific autoname, it finds that name already existing, but at a different path than the one you're specifying, so it fails.

How all this works is wrapped up in the bowles of ClickOnce deployment and SQL User Instances.

You need to clear out the pointer to your database name at the old path, which is easiest to accomplish using SSEUtil. Once you download this utility, you'll be able to easily connect to the User Instance that VS created for you and get a list of all the databases it knows about. (sseutil -L) Find the one that is the same file name as your file but in the wrong location and detach it.(sseutil -d name=c:\somepath\dbinventory.mdf) The name of the database will likely be the path to the file, this is the result of the auto naming that the error is talking about. You can learn more about auto naming and User Instances from the link I've given above.

Mike

|||

Thanks for your reply,

I have run this utility and only one instance of the database is attached. However, I have finally been able to reproduce the problem. I created two identical projects with a dataview bound to one of the tables in the dataset. In one of the test app's, I checked the "Enable ClickOnce Security Settings" in the Security Tab of MyProject settings and ran the app. This caused the error that I'm getting in my real project.....

I went back and unchecked the the Enable ClickOnce Security Setting, ran the project again and it worked fine! So I checked the real project settings and found that indeed the "Enable ClickOnce Security Settings" was enabled, however,after unchecking it and verfiying the other project settings I still am getting the same error!

I ran the sseutil again to see if something was going on, but only one instance of the database is running.

So I went back to the test app and messed around with the settings to see if I could get it to fail, nothing I did seem to create a condition that I couldn't change and get it back working correctly............until I closed the application and reopened it..........It failed as soon as I ran it and I cannot get it running by restoring the settings. I didn't change any code.

The second test app works fine................I compared the code files in the project and can't find anything different between the two.

Is there anything outside of the project files somewhere that might be causing this, registry key or such.......My brain is turning to mush at this point.

Thanks for the help, Burl

|||

Additional information,

Looks like I had multipe problems. I have a habit of moving the solution files around to different folders in VS-2003, such as ActiveProjects, ReleaseProjects, doing so never gave me so much grief as doing this in VS-2005. Just renaming the parent folder cause's the error's along with the above examples.

So, what is the answer to moving a solution to another location?

|||

In your case, it seems your configuration file had a connection string specifying the path to the database. When you moved your project, the path to the database changed and the error was generated cause your connection string was looking in the wrong place. Typically, movement of the data file is handled by the use of the |DataDirectory| macro being used as part of the connection string as in...

AttachDbfilename=|DataDirectory|MyDatabase.mdf

VS knows where the Data Directroy is for your project (or you can set it yourself programatically) and attaches the database for you. If you specify an absolute path, the database has to stay in that path as VS will not automatically change the stored connection string for you.

Mike

|||

Thanks for the reply Mike:

After finally getting this application running again, after a few days I ran into this related problem: When I try to open the Main Form in the Designer I get the Big Red X with a stack trace list and the error message as follows.

"An attempt to attach an auto-named database for file C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\dbInventory.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

At this point the application runs fine, but I just can't view the Main Form in the designer without it barking about the database connection. Looking at the trace info, it was complaining about a userControl that gets added to the form. So I commented out the line in the designer.vb file where the userControl is added to the forms collection. The form designer now shows the form layout with no errors. I uncommented that line of code and the error came back. So this time I commented out the line again and ran the application just fine. I again uncommented the code and this time the form layout came back OK, no errors.....

Somewhere, somehow a file is getting out of sync but I sure can't find it. The connection string in the app.config file and in the Settings.Designer.vb file look correct.

App.config file string.

<connectionStrings>

<add name="<MyApplicationName>.My.MySettings.dbInventoryConnectionString"

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbInventory.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

providerName="System.Data.SqlClient" />

</connectionStrings>

Settings.Designer.vb Connection String Property

<Global.System.Configuration.ApplicationScopedSettingAttribute(), _

Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _

Global.System.Configuration.SpecialSettingAttribute(Global.System.Configuration.SpecialSetting.ConnectionString), _

Global.System.Configuration.DefaultSettingValueAttribute("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbInventory.mdf;Integra"& _

"ted Security=True;Connect Timeout=30;User Instance=True")> _

Public ReadOnly Property dbInventoryConnectionString() As String

Get

Return CType(Me("dbInventoryConnectionString"),String)

End Get

End Property

End Class

At least I know how to get it fixed at this point, any ideas on the cause. I deleted the bin folder contents and that didn't help.

Thanks, Burl

|||

I'm having a real problem with the path to your database in the error: C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\dbInventory.mdf

I would never expect a database to be located there, that is part of the VS program installation and is not normally used as the Data Directory. Are you doing anything in your application to set the DataDirectory to this location? Where are your project files located? How did you create this database?

Also, is this a Windows Forms application or ASP.NET? Are you running your application by hitting F5?

Thanks for the additional information.

Mike

|||

I created the database using the Microsoft SQL Server Management Studio Express, version 9.00.2047.00. Later I detached the database and moved the files into a folder located in C:\MySqlDatabases. This is a Windows form application, I created a Setup Project for it as well, no clickOnce deployment. I get the errors by clicking the "Start Debugging Icon" or by pressing F5.

I started working on some userControls for this project a few weeks ago and didn't need the database until recently. So I go the add the database using the Data Source Configuration Wizard. I browsed to the file location and selected the mdf for this project in the C:\MySqlDatabases folder using Windows authentication and DataSource is Microsoft SQL Server Database(SqlClient), the connection string shows up like this "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\MySqlDatabases\dbInventory.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True". It then asks if I want to copy the data file to my project and I say Yes. It then saves my connection string to the app.config file as "dbInventoryConnectionString". The connection string in the Settings Designer is "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbInventory.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True". The connection string in the app.config file is the same.

I don't understand why the designer is barking about the database in "C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\dbInventory.mdf " when that userControl is trying to load in the main form, again if I comment out the line where it gets added to the forms collection, run the application and uncomment the line the error goes away. However, in that userControl I have one call to the database to fill a dataset, I had that code commented out while I tried to figure out what was going on. Here is the code for that call.

Private Sub CreateCategoryButtons()
Dim da As New dsCategoriesTableAdapters.CategoriesTableAdapter
Dim ds As New dsCategories da.Fill(ds.Categories)

Me.CategoryStackStrip.Items.Clear()
For Each row As DataRow In ds.Categories
Dim btn As New ToolStripButton
With btn
.Text = row.Item("Category").ToString
.Font = New Font("Tahoma", 8.25, FontStyle.Bold, GraphicsUnit.Point)
.Image = My.Resources.alarmclock
.ImageScaling = ToolStripItemImageScaling.SizeToFit
.CheckOnClick = True
.Alignment = ToolStripItemAlignment.Left
.DisplayStyle = ToolStripItemDisplayStyle.ImageAndText
.ImageAlign = ContentAlignment.MiddleLeft
.Margin = New Padding(0)
.Padding = New Padding(2)
.TextAlign = ContentAlignment.MiddleRight
End With

' Add to Category stack.
Me.CategoryStackStrip.Items.Add(btn)

' Add click event handler for category buttons.
AddHandler btn.Click, AddressOf OnCategoryButton_Click Next

' Activates the first button in the stack.
Me.CategoryStackStrip.Items(0).PerformClick()
End Sub

Now that this code is back into the game, the application fails on trying to read the connection string property in the Settings.Designer.vb code. Exception is {"Configuration system failed to initialize"} and the innerException is {"An error occurred loading a configuration file: Could not find file 'C:\Documents and Settings\Burl\<ApplicationNameHere>.exe_Url_trdwo0grhpv0v5se5luy1xl11k1tlbl2\1.0.0.0\user.config'. (C:\Documents and Settings\Burl\Application Data\...\<ApplicationNameHere>.exe_Url_trdwo0grhpv0v5se5luy1xl11k1tlbl2\1.0.0.0\user.config)"}.

I have no idea why its looking in this location for the config file. I even deleted the dataset and rebuilt it by drag and drop from the Server Explorer onto a new dataset file. Same problem.... All the connection string information in this solution is the same.........

"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbInventory.mdf;Integra"& _

"ted Security=True;Connect Timeout=30;User Instance=True"

This was all working a few days ago, then I started working on it the next day and this is the mess I'm in now...........ugh.

Burl

|||

I finally uncovered the root cause of the problem......

When you try to add a user control to a Microsoft Windows Forms-based application in Microsoft Visual Studio 2005, you may receive the following error message:

An attempt to attach an auto-named database for file DriveLetter:\Program Files\Microsoft Visual Studio 8\Common7\IDE\DatabaseName.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

You experience this problem if the user control contains a data-binding object that uses an attached local database file.

http://support.microsoft.com/default.aspx/kb/908038

Ok, here is the workaround I came up with. In my userControl for the Load Event I have code that hits the database and builds some buttons for a menu strip. I wrapped the code with a designMode test.

Original UserControl Load Event:

Private Sub ucNavigation_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
' Add Category Items and Overflow items.
CreateCategoryButtons()
AddOverflowItems()
' Set Height.
InitializeSplitter()
End Sub

New UserControl Load Event:

Private Sub ucNavigation_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
' Add Category Items and Overflow items.
If Not Me.DesignMode Then
CreateCategoryButtons()
End If

AddOverflowItems()
' Set Height.
InitializeSplitter()
End Sub

After adding the code I closed all the open windows in the designer and rebuilt the solution, now all of the forms open with no errors and no more database or connection string errors. I hope this information helps someone else, cause it's been a real nightmare for me, so for now I've got my fingers crossed...........

Burl

|||

Thanks for the update Burl,

This is good information for everyone who is working with user controls.

Mike

ambigous column name message

I am modifying an existing stored procedure in SQL server 2005. I have added a new field to the sp and am now receiving an ambiguous column name message. The column being referred to was in the sp before I modified. The column is on the line above where I added my new field ( EMPLOYEE NUMBER) to the sp. I am at a loss to why I'm getting this error message when executing the sp because this column existed before I modified. Can anyone help me understand why I'm getting this message all of a sudden and/ or where to look for help? Thanks in advance for any light you can help shed on this matter. Code snippet is below:

[BILL DUE DATE], [PAYMENT DATE AND TIME],

[EMPLOYEE NUMBER] )

Msg 209, Level 16, State 1, Procedure spBuildNoReasonLetter, Line 34

Ambiguous column name 'PAYMENT DATE AND TIME'.

There is no way we can 'guess' what is happening without seeing the code.

Please post the entire stored procedure code.

|||

Sorry, here is the sp:

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

ALTERPROCEDURE [dbo].[spBuildNoReasonLetter]

AS

setnocounton

TRUNCATETABLE [NO REASON LETTER];

INSERTINTO [NO REASON LETTER] ( [EMPLOYER NUMBER], [EMPLOYER NAME],

[CONTACT PERSON], [ADDRESS LINE 1],

[ADDRESS LINE 2], [ADDRESS LINE 3],

[EMPLOYEE SSN], [SERVICE CODE],

[EMPLOYEE NAME], [BILLING PERIOD],

[BILL DUE DATE], [PAYMENT DATE AND TIME],

[EMPLOYEE NUMBER] )

SELECTDISTINCT [NIGHT BATCH TABLE].[EMPLOYER NUMBER],

[COMPANY ADDRESS].[FULL NAME] AS [EMPLOYER NAME],

[COMPANY ADDRESS].[CONTACT PERSON],

[COMPANY ADDRESS].[ADDRESS LINE 1],

[COMPANY ADDRESS].[ADDRESS LINE 2],

[COMPANY ADDRESS].[CITY]+', '+[COMPANY ADDRESS].[STATE]+' '+[COMPANY ADDRESS].[ZIP CODE] AS [ADDRESS LINE 3],

[NIGHT BATCH TABLE].[EMPLOYEE SSN],

[NIGHT BATCH TABLE].[SERVICE CODE],

[MAIN EMPLOYEE].[FULL NAME] AS [EMPLOYEE NAME],Convert(varchar(10),

[COMPANY PAY TABLE].[BILLING PERIOD START],101)+' Thru '+Convert(varchar(10),[COMPANY PAY TABLE].[BILLING PERIOD END],101)AS [BILLING PERIOD],

[NIGHT BATCH TABLE].[BILL DUE DATE], [PAYMENT DATE AND TIME],

[MAIN EMPLOYEE].[EMPLOYEE NUMBER]

FROM(([NIGHT BATCH TABLE] INNERJOIN [COMPANY ADDRESS] ON([NIGHT BATCH TABLE].[EMPLOYER NUMBER] = [COMPANY ADDRESS].[ADDRESS KEY])AND

([NIGHT BATCH TABLE].[ADDRESS TYPE] = [COMPANY ADDRESS].[ADDRESS TYPE]))

INNERJOIN [MAIN EMPLOYEE] ON [NIGHT BATCH TABLE].[EMPLOYEE SSN] = [MAIN EMPLOYEE].[EMPLOYEE SSN])

INNERJOIN [NO REASON LETTER] ON [MAIN EMPLOYEE].[EMPLOYEE NUMBER] = [NO REASON LETTER].[EMPLOYEE NUMBER]

INNERJOIN [COMPANY PAY TABLE] ON([NIGHT BATCH TABLE].[BILL DUE DATE] = [COMPANY PAY TABLE].[BILL DUE DATE])AND

([NIGHT BATCH TABLE].[EMPLOYER NUMBER] = [COMPANY PAY TABLE].[EMPLOYER NUMBER])

WHERE([NIGHT BATCH TABLE].[REASON CODE]='X'AND

[MAIN EMPLOYEE].[STATUS CODE] In('00','01','09'));

UPDATE NRL

SET NRL.[CARRIER NAME] = CA.[FULL NAME],

NRL.[LOGO PATH] = CA.[CONTACT PERSON],

NRL.[TOLL FREE SERVICE NO] = CA.[TOLL FREE SERVICE NO]

FROM [NO REASON LETTER] AS NRL INNERJOIN [COMPANY ADDRESS] AS CA ON(CA.[ADDRESS KEY] = NRL.[EMPLOYER NUMBER] AND

CA.[ADDRESS TYPE] ='R')

UPDATE NRL

SET NRL.[SORT FIELD1] = dbo.fnReturnNRLSortField(CO.[SORT BILL BY],

ME.[FULL NAME],

ME.[EMPLOYEE SSN],

ME.[EMPLOYEE NUMBER],

ME.[DEPARTMENT CODE],

ME.[LOCATION CODE], 1),

NRL.[SORT FIELD2] = dbo.fnReturnNRLSortField(CO.[SORT BILL BY],

ME.[FULL NAME],

ME.[EMPLOYEE SSN],

ME.[EMPLOYEE NUMBER],

ME.[DEPARTMENT CODE],

ME.[LOCATION CODE], 2),

NRL.[EMPLOYEE SSN] = dbo.fnReturnFieldOrBlank(ME.[EMPLOYEE SSN], CO.[DO NOT DISPLAY SSN])

FROM [NO REASON LETTER] AS NRL INNERJOIN [COMPANY] AS CO ON(CO.[EMPLOYER NUMBER] = NRL.[EMPLOYER NUMBER])

INNERJOIN [MAIN EMPLOYEE] AS ME ON(ME.[EMPLOYEE SSN] = NRL.[EMPLOYEE SSN])

INNERJOIN [MAIN EMPLOYEE] AS ME ON(ME.[EMPLOYEE NUMBER] = NRL.[EMPLOYEE NUMBER])

|||I have changed my code slightly and am now getting a differenet message (new code is below error message:

Msg 4104, Level 16, State 1, Procedure spBuildNoReasonLetter, Line 17

The multi-part identifier "NO REASON LETTER.EMPLOYEE NUMBER" could not be bound.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

ALTERPROCEDURE [dbo].[spBuildNoReasonLetter]

AS

setnocounton

TRUNCATETABLE [NO REASON LETTER];

INSERTINTO [NO REASON LETTER] ( [EMPLOYER NUMBER], [EMPLOYER NAME],

[CONTACT PERSON], [ADDRESS LINE 1],

[ADDRESS LINE 2], [ADDRESS LINE 3],

[EMPLOYEE SSN], [SERVICE CODE],

[EMPLOYEE NAME], [BILLING PERIOD],

[BILL DUE DATE], [PAYMENT DATE AND TIME],

[EMPLOYEE NUMBER])

SELECTDISTINCT [NIGHT BATCH TABLE].[EMPLOYER NUMBER],

[COMPANY ADDRESS].[FULL NAME] AS [EMPLOYER NAME],

[COMPANY ADDRESS].[CONTACT PERSON],

[COMPANY ADDRESS].[ADDRESS LINE 1],

[COMPANY ADDRESS].[ADDRESS LINE 2],

[COMPANY ADDRESS].[CITY]+', '+[COMPANY ADDRESS].[STATE]+' '+[COMPANY ADDRESS].[ZIP CODE] AS [ADDRESS LINE 3],

[NIGHT BATCH TABLE].[EMPLOYEE SSN],

[NIGHT BATCH TABLE].[SERVICE CODE],

[MAIN EMPLOYEE].[FULL NAME] AS [EMPLOYEE NAME],Convert(varchar(10),

[COMPANY PAY TABLE].[BILLING PERIOD START],101)+' Thru '+Convert(varchar(10),[COMPANY PAY TABLE].[BILLING PERIOD END],101)AS [BILLING PERIOD],

[NIGHT BATCH TABLE].[BILL DUE DATE], [PAYMENT DATE AND TIME],

[MAIN EMPLOYEE].[EMPLOYEE NUMBER]

FROM(([NIGHT BATCH TABLE] INNERJOIN [COMPANY ADDRESS] ON([NIGHT BATCH TABLE].[EMPLOYER NUMBER] = [COMPANY ADDRESS].[ADDRESS KEY])AND

([NIGHT BATCH TABLE].[ADDRESS TYPE] = [COMPANY ADDRESS].[ADDRESS TYPE]))

INNERJOIN [MAIN EMPLOYEE] ON [NIGHT BATCH TABLE].[EMPLOYEE SSN] = [MAIN EMPLOYEE].[EMPLOYEE SSN])

INNERJOIN [MAIN EMPLOYEE] AS MET ON [NO REASON LETTER].[EMPLOYEE NUMBER] = [MAIN EMPLOYEE].[EMPLOYEE NUMBER]

INNERJOIN [COMPANY PAY TABLE] ON([NIGHT BATCH TABLE].[BILL DUE DATE] = [COMPANY PAY TABLE].[BILL DUE DATE])AND

([NIGHT BATCH TABLE].[EMPLOYER NUMBER] = [COMPANY PAY TABLE].[EMPLOYER NUMBER])

WHERE([NIGHT BATCH TABLE].[REASON CODE]='X'AND

[MAIN EMPLOYEE].[STATUS CODE] In('00','01','09'));

UPDATE NRL

SET NRL.[CARRIER NAME] = CA.[FULL NAME],

NRL.[LOGO PATH] = CA.[CONTACT PERSON],

NRL.[TOLL FREE SERVICE NO] = CA.[TOLL FREE SERVICE NO]

FROM [NO REASON LETTER] AS NRL INNERJOIN [COMPANY ADDRESS] AS CA ON(CA.[ADDRESS KEY] = NRL.[EMPLOYER NUMBER] AND

CA.[ADDRESS TYPE] ='R')

UPDATE NRL

SET NRL.[SORT FIELD1] = dbo.fnReturnNRLSortField(CO.[SORT BILL BY],

ME.[FULL NAME],

ME.[EMPLOYEE SSN],

MET.[EMPLOYEE NUMBER],

ME.[DEPARTMENT CODE],

ME.[LOCATION CODE], 1),

NRL.[SORT FIELD2] = dbo.fnReturnNRLSortField(CO.[SORT BILL BY],

ME.[FULL NAME],

ME.[EMPLOYEE SSN],

MET.[EMPLOYEE NUMBER],

ME.[DEPARTMENT CODE],

ME.[LOCATION CODE], 2),

NRL.[EMPLOYEE SSN] = dbo.fnReturnFieldOrBlank(ME.[EMPLOYEE SSN], CO.[DO NOT DISPLAY SSN])

FROM [NO REASON LETTER] AS NRL INNERJOIN [COMPANY] AS CO ON(CO.[EMPLOYER NUMBER] = NRL.[EMPLOYER NUMBER])

INNERJOIN [MAIN EMPLOYEE] AS ME ON(ME.[EMPLOYEE SSN] = NRL.[EMPLOYEE SSN])

INNERJOIN [MAIN EMPLOYEE] AS MET ON(ME.[EMPLOYEE NUMBER] = NRL.[EMPLOYEE NUMBER])

|||

Hi,

you are not referencing the Table in the Select clause, therefore you cannot use it in the join part.

BTW. Did I mention that it is horrorible to use space and special characters in defintions ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Now I'm confused.

First you TRUNCATE the [NO REASON LETTER] table.

Then you attempt to use JOIN conditions to that EMPTY table (but there is no previous mention of a JOIN to that table.. What are you trying to accomplish?

INNERJOIN [MAIN EMPLOYEE] AS MET ON [NO REASON LETTER].[EMPLOYEE NUMBER] = [MAIN EMPLOYEE].[EMPLOYEE NUMBER]

Even if it 'could' happen (and it just can't), since the table is empty, this would serve to filter out ALL possible rows and nothing would be inserted.

So what's the point?

|||

One other thing to keep in mind is that if you are going to Alias a table in a join clause, you probably should use it in the join.

INNERJOIN [MAIN EMPLOYEE] AS MET ON(ME.[EMPLOYEE NUMBER] = NRL.[EMPLOYEE NUMBER])

I would think that you would want to use MET.[EMPLOYEE NUMBER] instead of ME.

Ben Miller

Am I correct?

I read the message with the subject "Can I recover?". It states that the
user only has a copy of the database backup in February and he executed an
update statement without a where clause. He wants to know if it can be
recovered...
From what I know, if the February backup was a Full backup and if the
database recovery mode has been setup in "Full", he should be able to backup
the current Transaction Log and do a "point in time restore" to restore data
back before he executed an update statement. Am I correct? I did test it s
o
many times for the "point in time" long time ago... and the point in time
could be used anytime in between "Full Backup"/Diff. Backup and a Transactio
n
Log backup.
I could be so suprise if I am wrong... I don't want to be shame on myseft
since I am a DBA for a fortunate 500 company...
Ed>Am I correct?
Yep.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:E1EE8615-658E-4779-ACF9-71C2357EC8A4@.microsoft.com...
>I read the message with the subject "Can I recover?". It states that the
> user only has a copy of the database backup in February and he executed an
> update statement without a where clause. He wants to know if it can be
> recovered...
> From what I know, if the February backup was a Full backup and if the
> database recovery mode has been setup in "Full", he should be able to
> backup
> the current Transaction Log and do a "point in time restore" to restore
> data
> back before he executed an update statement. Am I correct? I did test it
> so
> many times for the "point in time" long time ago... and the point in time
> could be used anytime in between "Full Backup"/Diff. Backup and a
> Transaction
> Log backup.
> I could be so suprise if I am wrong... I don't want to be shame on myseft
> since I am a DBA for a fortunate 500 company...
> Ed