Hello,

We have a stored procedure called FriendSuggestion which we've mapped using LLBLGen Designer.

This is the generated code in the RetrivalProcedures.cs file:

private static StoredProcedureCall CreateFriendSuggestionCall(IDataAccessCore dataAccessProvider, System.Guid userId, System.Int32 numberOfLevels, System.Int32 pageNumber, System.Int32 pageSize, System.Boolean isRandom, System.String userName)
        {
            return new StoredProcedureCall(dataAccessProvider, @"[MonoX2].[dbo].[FriendSuggestion]", "FriendSuggestion")
                            .AddParameter("@UserId", "UniqueIdentifier", 0, ParameterDirection.Input, true, 0, 0, userId)
                            .AddParameter("@NumberOfLevels", "Int", 0, ParameterDirection.Input, true, 10, 0, numberOfLevels)
                            .AddParameter("@PageNumber", "Int", 0, ParameterDirection.Input, true, 10, 0, pageNumber)
                            .AddParameter("@PageSize", "Int", 0, ParameterDirection.Input, true, 10, 0, pageSize)
                            .AddParameter("@IsRandom", "Bit", 0, ParameterDirection.Input, true, 0, 0, isRandom)
                            .AddParameter("@UserName", "NVarChar", 200, ParameterDirection.Input, true, 0, 0, userName);
        }

In LLBLGen 3.1 we used the same setup and called the stored procedure using the following approach:

EntityCollection<FriendSuggestionTypedViewEntryEntity> userEntities = new EntityCollection<FriendSuggestionTypedViewEntryEntity>();
            using (IRetrievalQuery query = RetrievalProcedures.GetFriendSuggestionCallAsQuery(userId, numberOfLevels, pageNumber, pageSize, isRandom, searchTerm))
            {
                using (IDataAccessAdapter adapter = DependencyInjectionFactory.Resolve<IGenericRepository>().GetAdapter())
                {
                    using (IDataReader reader = adapter.FetchDataReader(query, CommandBehavior.CloseConnection))

In our scenario the development database name is usually different than the version of the live system for example. With LLBLGen 3.1 this worked without any problems.

However recently after upgrading to LLBLGen 4.1 this no longer appears to be the case as we're experiencing the following errors:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: Reference to database and/or server name in 'MonoX2.dbo.FriendSuggestion' is not supported in this version of SQL Server.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> System.Data.SqlClient.SqlException: Reference to database and/or server name in 'MonoX2.dbo.FriendSuggestion' is not supported in this version of SQL Server.

The exception is thrown when the FetchDataReader is called. This occurs when we use a different database name. Can you help me track down the issue and fix it?

Regards, Mario

I believe this is due to the following: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=21486&StartAtMessage=0&#120972

Could you please use overwrites in the .config file.

Walaa wrote:

I believe this is due to the following: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=21486&StartAtMessage=0&#120972

Could you please use overwrites in the .config file.

Hello,

Can you please explain what do you mean by using config overwrites? We always used a custom adapter and using the same calls in v3.1 catalog appears to have been resolved when the FetchDataReader was called.

Regards, Mario

Do you use the following to resolve the database name?

Catalog name overwriting

Also check whether you're connecting to the correct server (so check the connection string used)

Otis wrote:

Also check whether you're connecting to the correct server (so check the connection string used)

The connection string is correct.

I'm attaching the screenshots taken using the watch window in Visual Studio. This is when the query is initialized : http://imgur.com/P40uAIG

This is after the FetchDataReader is called, as you can see the [MonoX2] is stripped: http://imgur.com/lsfojGA

I think that there is a problem in LLBLGen 4.1. All we did was upgrade our solution from LLBGen v3.1 to v4.1. The following approach has been working since version 2.6 and it was working in 3.1, now after we've upgraded to v4.1 it no longer works.

Regards, Mario

I don't understand your second screenshot: the catalog name is stripped, though you still get the error? Or is that a screenshot from v3.1?

I'll look into the bugfix database, I do recall it's due to a bugfix, believe it or not I do assume you don't use any catalog name overwriting.

What I don't understand is the following: the entities work OK with your different catalog name? The thing is: the project in the designer has as catalog name MonoX2. This name is generated into the persistence information. At runtime this name is used by default, unless you overwrite it with another name, which can be anything. If you specify the empty string as new name, the queries will be executed on the default catalog you connect to in the connection string. Otherwise they query will be run on the catalog mentioned in the query.

So if you don't use catalog name overwriting, the entity queries too will run on MonoX2, so that catalog must exist. The stored procedures will also run on that catalog, as their mapping data contains the same catalog name from the project. If at runtime you need to run a procedure in a different catalog, you thus have to overwrite the catalog name at runtime. This can be done with a simple config file setting, see the documentation Walaa linked to.

So please explain: do entities work ok? If so, do you use name overwriting somewhere for the catalog?

The bugfix which caused this change is this one: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=21658&StartAtMessage=0&#122035

It's introduced in v4.0, so a change compared to v3.1. However I can't advice you what to do before I know if you're currently using name overwriting.

So if you set the flag NoNameOverwriting on the query object to true, it should work OK IF you're using some name overwriting. Please read the thread above for information about the issue. It might not be related to your issue, but I think it is. It's a case of double name overwriting being applied which doesn't work. Normally this isn't happening, only in your case it does.

Otis wrote:

I don't understand your second screenshot: the catalog name is stripped, though you still get the error? Or is that a screenshot from v3.1?

I'll look into the bugfix database, I do recall it's due to a bugfix, believe it or not I do assume you don't use any catalog name overwriting.

This is from version 3.1 and yes this works. We didn't use catalog name overwriting previously.

What are you recommendations in order for us to resolve this?

Please read my updated post above

Otis wrote:

Please read my updated post above

Yes the entities work OK and the whole application except for this stored procedure call. In our own adapter we used to set SD.LLBLGen.Pro.ORMSupportClasses.CatalogNameUsage.Clear flag. And this is currently how it is set on all of our projects.

Is the following CatalogNameUsage.Clear setting obsolete now? If so what do you recommend that we use instead.

mzilic wrote:

Otis wrote:

Please read my updated post above

Yes the entities work OK and the whole application except for this stored procedure call. In our own adapter we used to set SD.LLBLGen.Pro.ORMSupportClasses.CatalogNameUsage.Clear flag. And this is currently how it is set on all of our projects.

Is the following CatalogNameUsage.Clear setting obsolete now? If so what do you recommend that we use instead.

Yes clear was deprecated since v2 I think. Clear simply adds a "*"->"" overwrite.

Use this: Catalog name overwriting

and overwrite "MonoX2" with ""

The flag I gave you above should also work I as your situation is precisely why the flag was added.

Otis wrote:

mzilic wrote:

Otis wrote:

Please read my updated post above

Yes the entities work OK and the whole application except for this stored procedure call. In our own adapter we used to set SD.LLBLGen.Pro.ORMSupportClasses.CatalogNameUsage.Clear flag. And this is currently how it is set on all of our projects.

Is the following CatalogNameUsage.Clear setting obsolete now? If so what do you recommend that we use instead.

Yes clear was deprecated since v2 I think. Clear simply adds a "*"->"" overwrite.

Use this: Catalog name overwriting

and overwrite "MonoX2" with ""

The flag I gave you above should also work I as your situation is precisely why the flag was added.

Can the following setting be set programmatically? If so, do you have any samples?

You can set it for each adapter instance using adapter.CatalogNameOverwrites That's the from-to name value pairs and setting for the overwriting of catalog names.

Another way, is to have it set once in the config file to an empty string, and change it through connection string, which can be passed to the ConnectionString property of the adapter as well.

Walaa wrote:

You can set it for each adapter instance using adapter.CatalogNameOverwrites That's the from-to name value pairs and setting for the overwriting of catalog names.

Another way, is to have it set once in the config file to an empty string, and change it through connection string, which can be passed to the ConnectionString property of the adapter as well.

I'm assumed that we should just set the CatalogNameOverwrites property and it should be good to go. However, even if I do specify a catalog name to overwrite it with it doesn't work, the default rule is as Otis says is "*" > "". Which should replace any catalog name with an empty string however this does not happen.

The only way I can reproduce anything similar to the default rule which worked as expected in v3.1 and lower versions is to use a similar config setting:

  <sqlServerCatalogNameOverwrites>
    <add key="*" value="" />    
  </sqlServerCatalogNameOverwrites>

However if the following property can be used to programmatically override the catalog name why doesn't the wildcard take effect? Is this perhaps as well obsolete? Do you have a different approach in v4.1?

Did you look at the query.NoNameOverwriting property I described above as well?

Otis wrote:

Did you look at the query.NoNameOverwriting property I described above as well?

Hello,

We tried that and this option would require us to change most of our applications which is an approach that simply doesn't work for us. We will have to resort to the configuration changes.

However, if these options are obsolete (ex. SD.LLBLGen.Pro.ORMSupportClasses.CatalogNameUsage.Clear) I wonder why there were no compiler warnings at all in LLBLGen? Even though you mentioned that the following stopped working due to a bugfix this issue is not fully resolved in LLBLGen I think.

Regards

The option is deprecated but will still work (sorry for this confusion). The problem is caused by multiple name overwriting calls due to the setup you're using. The solution we implemented is to set the flag on the query to have it not do a rename again (as it renames the schema).

At least that's what I think happens as the information is a bit scattered around what exactly is going on in your application. As you have a global overwriting defined, it will rename whatever to "", causing problems when the name is renamed too many times (as it doesn't know which fragment is which as multiple formats can be passed into the method)

The config options were suggested as it was unclear what was going on, whether you used name overwriting or not, so we thought that was the cause.

From the looks of it, the only solution here is to avoid naming again through the flag, OR use explicit name overwriting so explicitly rename MonoX2 to "" and use no other name overwriting setting, so don't use the Clear option you use now. Multiple times renaming then doesn't matter as MonoX2 isn't in the name anymore and there's no global overwrite to rename any fragment it thinks is a catalog to ""

Could you try that for me please? (so get rid of the Clear usage, and explicitly specify a rename in the config file to rename MonoX2 to "" and remove any global renames).

Otis wrote:

The option is deprecated but will still work (sorry for this confusion). The problem is caused by multiple name overwriting calls due to the setup you're using. The solution we implemented is to set the flag on the query to have it not do a rename again (as it renames the schema).

At least that's what I think happens as the information is a bit scattered around what exactly is going on in your application. As you have a global overwriting defined, it will rename whatever to "", causing problems when the name is renamed too many times (as it doesn't know which fragment is which as multiple formats can be passed into the method)

The config options were suggested as it was unclear what was going on, whether you used name overwriting or not, so we thought that was the cause.

From the looks of it, the only solution here is to avoid naming again through the flag, OR use explicit name overwriting so explicitly rename MonoX2 to "" and use no other name overwriting setting, so don't use the Clear option you use now. Multiple times renaming then doesn't matter as MonoX2 isn't in the name anymore and there's no global overwrite to rename any fragment it thinks is a catalog to ""

Could you try that for me please? (so get rid of the Clear usage, and explicitly specify a rename in the config file to rename MonoX2 to "" and remove any global renames).

Hi Otis,

We already tried that and it works for us. However we were curious if there was a programmatic solution aside from the config changes. What we were looking for is to have this piece of config section:

<sqlServerCatalogNameOverwrites>
    <add key="MonoX2" value="" />   
</sqlServerCatalogNameOverwrites>

be set somewhere from inside the code for example. However what it seems to me that currently you cannot do that in the current version of LLBLGen.

Regards

Sure you can

See: http://www.llblgen.com/documentation/4.1/LLBLGen%20Pro%20RTF/hh_goto.htm#Using%20the%20generated%20code/Adapter/gencode_dataaccessadapter_adapter.htm#Catalogspecificpersistenceinfo

So use the Multi name setting by providing a CatalogNameOverwriteHashtable instance. You can add that to your adapter's derived class (I understand from your posts that you use a custom adapter class)

You have to set these with each adapter instance, so it's best to set these in the ctor of your adapter derived class.

Otis wrote:

Sure you can

See: http://www.llblgen.com/documentation/4.1/LLBLGen%20Pro%20RTF/hh_goto.htm#Using%20the%20generated%20code/Adapter/gencode_dataaccessadapter_adapter.htm#Catalogspecificpersistenceinfo

So use the Multi name setting by providing a CatalogNameOverwriteHashtable instance. You can add that to your adapter's derived class (I understand from your posts that you use a custom adapter class)

You have to set these with each adapter instance, so it's best to set these in the ctor of your adapter derived class.

Hi Otis,

I maybe failed to mention that I had explored that option however that approach would have required me to use the NoNameOverwriting as well if I recall correctly. We opted for the config option in the end. Anyway, thanks.

Regards

mzilic wrote:

Otis wrote:

Sure you can

See: http://www.llblgen.com/documentation/4.1/LLBLGen%20Pro%20RTF/hh_goto.htm#Using%20the%20generated%20code/Adapter/gencode_dataaccessadapter_adapter.htm#Catalogspecificpersistenceinfo

So use the Multi name setting by providing a CatalogNameOverwriteHashtable instance. You can add that to your adapter's derived class (I understand from your posts that you use a custom adapter class)

You have to set these with each adapter instance, so it's best to set these in the ctor of your adapter derived class.

Hi Otis,

I maybe failed to mention that I had explored that option however that approach would have required me to use the NoNameOverwriting as well if I recall correctly. We opted for the config option in the end. Anyway, thanks. Regards

It should not matter: code or config file, the config file is global, the code option is per instance, but that's about it. In both situations it's essential the general * -> "" overwrite is not there, maybe it was still enabled?

It should not matter: code or config file, the config file is global, the code option is per instance, but that's about it. In both situations it's essential the general * -> "" overwrite is not there, maybe it was still enabled?

It wasn't, but sorry for me not going in depth (it's a bit late for me now). Your suggested options and configurations will work for us, ex: 1. Using a global config setting 2. Using the adapter approach with no .config options set, however in that case the NoNameOverwriting flag has to be set to false "manually"

Let's use a simple scenario: a) No .configs defined whatsoever b) You have custom catalog name overwrite defined in the custom adapter in the constructor c) You use the default "setup" on the Retrieval Queries which sets the NoNameOverwritting property to true (I think)

In this instance LLBLGen will try to connect to the original catalog when you call FetchDataReader and not the overwritten catalog because of the NoNameOverwritting flag. Although I'm not sure why one would need to manually set the NoNameOverwritting to false to get the catalog overwritten if I haven't set any global config overwrite options but I have some catalog name overwrites defined in my adapter (regardless if this is "*" > "" or "MyDb" > ""). Based on your reply I'm assuming that something different is supposed to happen...

thanks, we'll look into this.

one question:

b) You have custom catalog name overwrite defined in the custom adapter in the constructor

what does this mean exactly, could you post the code for what you mean by this?

Otis wrote:

thanks, we'll look into this.

one question:

b) You have custom catalog name overwrite defined in the custom adapter in the constructor

what does this mean exactly, could you post the code for what you mean by this?

You explained this approach right here:

So use the Multi name setting by providing a CatalogNameOverwriteHashtable instance. You can add that to your adapter's derived class

(I understand from your posts that you use a custom adapter class)

You have to set these with each adapter instance, so it's best to set these in the ctor of your adapter derived class.

However here is a sample project custom adapter I set up right here:

using Sample.DatabaseSpecific;
using SD.LLBLGen.Pro.ORMSupportClasses;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WindowsFormsApplication1
{
    public class CustomAdapter : DataAccessAdapter
    {
        const string conString = "Data Source=;uid=;pwd=;database=LLBLGenExample2;Connect Timeout=90;";
        public CustomAdapter()
            : base(conString, false)
        {
            CatalogNameOverwriteHashtable overwrites = new CatalogNameOverwriteHashtable();
            overwrites.Add("LLBLGenExample", string.Empty);
            this.CatalogNameOverwrites = overwrites;
        }
    }
}

So in this instance the default catalog is LLBLGenExample while I'm connecting to the LLBLGenExample2.

I'm just posting some additional information below in case you need it.

This part is not relevant I think but here is call to the stored procedure:

            try
            {
                using (IRetrievalQuery query = RetrievalProcedures.GetGetTestDataCallAsQuery("test"))
                {                                       
                    using (var adapter = this.GetAdapter())
                    {
                        using (IDataReader reader = adapter.FetchDataReader(query, CommandBehavior.CloseConnection))
                        {

                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

This line will throw an error because no overwriting will take place:

adapter.FetchDataReader(query, CommandBehavior.CloseConnection)

If I manually set the NoNameOverwritting property to false then I'll get the desired behavior, for example:

            try
            {
                using (IRetrievalQuery query = RetrievalProcedures.GetGetTestDataCallAsQuery("test"))
                {
                    query.NoNameOverwriting = false;        
                    using (var adapter = this.GetAdapter())
                    {
                        using (IDataReader reader = adapter.FetchDataReader(query, CommandBehavior.CloseConnection))
                        {

                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

Let me know if you need me to clarify anything or if you need some additional information.

Regards

Thanks for the info, I think we can work with that to reproduce it I'll get back to you when we have more info about this.