Showing posts with label call. Show all posts
Showing posts with label call. Show all posts

Friday, March 9, 2012

Method EnumDatabaseMapping on Login SMO object takes 3 minutes

Hi,

The EnumDatabaseMapping call below takes upto 3 minutes on some SQL Servers whereas it takes hardly any time at all on others. What may cause such a long delay?

Regards,
Joginder Nahil
www.starprint2000.com

Private Sub OutputUserMapping(ByVal oLogin As Microsoft.SqlServer.Management.Smo.Login)

Dim colDatabaseMapping() As DatabaseMapping
colDatabaseMapping = oLogin.EnumDatabaseMappings

' Rest of the code has been deleted

End sub

Hi Joginder,

EnumDatabaseMappings is an expensive operation. SMO issues queries that iterate over all databases on the server and stores intermediate results in a temporary table. This might take time for a server with many databases and users, especially if under heavy load.

You can find out more by running SQL Server Profiler and inspecting the queries that are being sent to the server.

Arur Laksberg
SQL Server Team
Microsoft

|||

Hi Artur,

I am inclined to say that 3 minutes is a very long time in computer processing time (BTW my SQL Server is on the same computer as running the application and has 1GB memory + 3.6 Pentium 4 Processor+ there is nothing much else running) to enumerate just three databases AdventureWorks, Pubs and Northwind.

Regards,

Joginder Nahil
www.starprint2000.com

method call works from .Net form and not from SQL CLR - EnterpriseLibrary used

Hello

I created a wrapper class for a function, and exposed it through CLR. However, if I call this function form SQL it blows up but if I call directly from a test Windows Form the call works fine.

The blow up is related to EnterpriseLibrary.Data, where my Queue class uses that library to do all data access call ops

Here's my wrapper class:

namespace inlineCLRsql{

public static class Wrapper{

public static void CallQueueEntry(int queueId, int deskNo, int missed){

inLineLib.Queue oQueue;

inLineLib.QueueEntry oQueueEntry;

oQueue = new inLineLib.Queue(queueId);

oQueueEntry = oQueue.callQueueEntry(deskNo, false);

Microsoft.SqlServer.Server.SqlContext.Pipe.Send(oQueueEntry.queueNum.ToString());

}

}

And this is my CLR SQL creation code:

CREATE PROC sp_CallQueueEntry

@.queueId int,

@.deskNo int,

@.missed int

AS

EXTERNAL NAME inLineLib.[inlineCLRsql.Wrapper].CallQueueEntry

GO

sp_CallQueueEntry 4,2,0

Here is what I get as a result

System.NullReferenceException: Object reference not set to an instance of an object.

System.NullReferenceException:

at Microsoft.Practices.EnterpriseLibrary.Data.DatabaseConfigurationView.get_DefaultName()

at Microsoft.Practices.EnterpriseLibrary.Data.DatabaseMapper.MapName(String name, IConfigurationSource configSource)

at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder.ConfigurationNameMappingStrategy.BuildUp(IBuilderContext context, Type t, Object existing, String id)

at Microsoft.Practices.ObjectBuilder.BuilderBase`1.DoBuildUp(IReadWriteLocator locator, Type typeToBuild, String idToBuild, Object existing, PolicyList[] transientPolicies)

at Microsoft.Practices.ObjectBuilder.BuilderBase`1.BuildUp(IReadWriteLocator locator, Type typeToBuild, String idToBuild, Object existing, PolicyList[] transientPolicies)

at Microsoft.Practices.ObjectBuilder.BuilderBase`1.BuildUp[TTypeToBuild](IReadWriteLocator locator, String idToBuild, Object existing, PolicyList[] transientPolicies)

at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder.EnterpriseLibraryFactory.BuildUp[T](IReadWriteLocator locator, IConfigurationSource configurationSource)

at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder.EnterpriseLibraryFactory.BuildUp[T](IConfigurationSource configurationSource)

at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder.NameTypeFactoryBase`1.CreateDefault()

at Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase()

at inLineLib.Queue.getNextQueueEntry(Int32 servedBy)

at inLineLib.Queue.callQueueEntry(Int32 servedBy, Boolean callMissed)

at inlineCLRsql.Wrapper.CallQueueEntry(Int32 queueId, Int32 deskNo, Int32 missed)

What can I do to fix this?

Cheers

M

This is almost a total guess - I've never used EntLib. However, I found some one else hit this issue when EntLib was not able to find the database in the application config file: http://www.experts-exchange.com/Programming/Programming_Languages/C_Sharp/Q_21833370.html

This is likely to be the same problem you're facing. I believe you can solve this by running the Enterprise Library Config tool and specify your config file as sqlservr.exe.config in the same directory as sqlservr.exe. Or you can try copying and renaming the config file your Windows Form app is using.

Hope this works.

Steven

Monday, February 20, 2012

Merging two tables with selection

I would like to have two tables. One I call SystemPropertyTypeTable which
contains the defaults and the other UserPropertyTypeTable. Each has 3
fields. PropertyType, Description, Status.

The idea here is to allow a user to change his/her defaults or to add a new
Property Type without messing with the system default list.

I would like to Merge these two tables using the following logic.
The SystemPropertyTypeTable any records that have "ACTIVE" for the status.
The UserPropertyTypeTable all records.

Group by Name and remove any duplicates.
if the UserPropertyTypeTable has INACTIVE then Throw away the Active Record
from the SystemPropertyTypeTable and keep the INACTIVE record.

Here is my code so far.

SELECT T.PropertyType, T.Status
FROM [SELECT PropertyType,Status
FROM SystemPropertyTypeTable Where Status='ACTIVE'
UNION ALL
SELECT PropertyType,Status
FROM UserPropertyTypeTable]. AS T
GROUP BY T.PropertyType, T.Status
HAVING (((Count(*))=1));

here is the resultset
ShowAllRecordsMerged PropertyType Status
APARTMENT ACTIVE
APARTMENT INACTIVE
BUILDING ACTIVE
GARAGE ACTIVE
KOISK ACTIVE
MAINTENANCE SHOP ACTIVE
MAINTENANCE STORAGE AREA ACTIVE
OFFICE ACTIVE
PARKING SPACE ACTIVE
PARKING SPACE INACTIVE
SHOP ACTIVE
STORAGE AREA ACTIVE

So looking at this I would still like to remove any duplicates leaving the
INACTIVE ones which would be the first APARTMENT record and the first
PARKING SPACE record. Also it would be nice to add the description back into
this as well.

Any help anyone can be here would be wonderful.

Thanks in advance.

BruceBruce Stradling (bstradling@.cox.net) writes:

Quote:

Originally Posted by

I would like to Merge these two tables using the following logic.
The SystemPropertyTypeTable any records that have "ACTIVE" for the status.
The UserPropertyTypeTable all records.
>
Group by Name and remove any duplicates. if the UserPropertyTypeTable
has INACTIVE then Throw away the Active Record from the
SystemPropertyTypeTable and keep the INACTIVE record.


If I understand this correctly, you want:

SELECT U.PropertyType, U.Description, U.Status
FROM UserPropertyTable U
UNION ALL
SELECT S.PropertyType, S.Description, S.Status
FROM SystemPropertyType S
WHERE S.Status = 'ACTIVE'
AND NOT EXISTS (SELECT *
FROM UserPropertyTable U
WHERE S.Property = U.Property)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks ... that was exactly what I needed. Here is the final code:

SELECT U.PropertyType, U.Description, U.Status
FROM UserPropertyTypeTable U
UNION ALL SELECT S.PropertyType, S.Description, S.Status
FROM SystemPropertyTypeTable S
WHERE S.Status = 'ACTIVE'
AND NOT EXISTS (SELECT *
FROM UserPropertyTypeTable U
WHERE S.PropertyType = U.PropertyType)
ORDER BY PropertyType;

And then another that removed all inactive records:

SELECT U.PropertyType, U.Description, U.Status
FROM UserPropertyTypeTable U
WHERE U.Status = 'ACTIVE'
UNION ALL SELECT S.PropertyType, S.Description, S.Status
FROM SystemPropertyTypeTable S
WHERE S.Status = 'ACTIVE'
AND NOT EXISTS (SELECT *
FROM UserPropertyTypeTable U
WHERE S.PropertyType = U.PropertyType)
ORDER BY PropertyType;

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns982BE98115752Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Bruce Stradling (bstradling@.cox.net) writes:

Quote:

Originally Posted by

I would like to Merge these two tables using the following logic.
The SystemPropertyTypeTable any records that have "ACTIVE" for the


status.

Quote:

Originally Posted by

Quote:

Originally Posted by

The UserPropertyTypeTable all records.

Group by Name and remove any duplicates. if the UserPropertyTypeTable
has INACTIVE then Throw away the Active Record from the
SystemPropertyTypeTable and keep the INACTIVE record.


>
If I understand this correctly, you want:
>
SELECT U.PropertyType, U.Description, U.Status
FROM UserPropertyTable U
UNION ALL
SELECT S.PropertyType, S.Description, S.Status
FROM SystemPropertyType S
WHERE S.Status = 'ACTIVE'
AND NOT EXISTS (SELECT *
FROM UserPropertyTable U
WHERE S.Property = U.Property)
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx