Search Results

Search found 19966 results on 799 pages for 'datetime query'.

Page 294/799 | < Previous Page | 290 291 292 293 294 295 296 297 298 299 300 301  | Next Page >

  • How to predict result set row count?

    - by Saurabh Kumar
    I have an application where I create a big SQL query dynamically for SQL server 2008. This query is based on various search criteria which the user might give such as search by lastname, firstname, ssn etc. The requirement is that if the user gives a condition due to which the formed query might return a lot of rows(configurable for max N rows), then the application must send back a message instead to the user saying that he needs to refine his search query as the existing query will return too many rows. I would not want to bring back say, 5000 rows to the client and then discard that data just to show the user an error. What is an efficient way to tackle this issue?

    Read the article

  • (database) im trying to create a form in access 2007 with 2 drop down boxes to view a report by state or name

    - by jeff orris
    im an intern at a database mngmt company and the boss is training me in access...i took the access tutorials and were definitely not enough info involved to do a what seems a simple task.my problem is this: i have a simple table with contact info with 16 colums (Local_Utility, Requested_User_Type, First_Name, Last_Name, Address 1, Address 2, Country, State, City, Zip, Phone_Number, Username\Email, Password, Confirm Password, and Parcel_Number), with 6 rows of names (keep in mind this is just a test to help me from the boss) I created a form and with 2 drop down boxes (Last Name and State) and im trying to create a view button to view an individual report for a query i made for just simple contact info with 6 colums (Last_Name, First_Name, Address1, City, State, and Phone_Number) Problem1 is that i can view the query with the view by name or state button but cant view a simple individual report from the query using the button Problem2 is that for criteria on the query i put Forms!frmMyparamForm!txtMyStateParamField for the state drop box it works, but when i use Forms!frmMyparamForm!txtMyNameParamField it doesnt and that annoying parameter box pops up Problem3 is that after i close the query, all the states and names in my dropdown box on the form disappear Im a beginner at this please help me

    Read the article

  • sql server - how to execute tje second half of or only when first one fails

    - by fn79
    Suppose I have a table with following records value text company/about about Us company company company/contactus company contact I have a very simple query in sql server as below. I am having problem with the 'or' condition. In below query, I am trying to find text for value 'company/about'. If it is not found, then only I want to run the other side of 'or'. The below query returns two records as below value text company/about about Us company company Query select * from tbl where value='company/about' or value=substring('company/about',0,charindex('/','company/about')) How can I modify the query so the result set looks like value text company/about about Us

    Read the article

  • Rails 2.3.8 Compound condition

    - by Michael Guantonio
    I have a rails query that I would like to run. The only problem that I am having is the query structure. Essentially the query looks like this queryList = model.find(:all, :conditions => [id = "id"]) #returns a query list #here is the issue compound = otherModel.find(:first, :select => "an_id", :conditions => ["some_other_id=? and an_id=?, some_other_id, an_id]) Where an_id is actually a list of ids in the query list. How can I write that in rails to basically associate a single id to a list that may contain ids...

    Read the article

  • how do I refactor this to make single function calls?

    - by stack.user.1
    I've been using this for a while updating mysql as needed. However I'm not too sure on the syntax..and need to migrate the sql to an array. Particulary the line database::query("CREATE TABLE $name($query)"); Does this translate to CREATE TABLE bookmark(name VARCHAR(64), url VARCHAR(256), tag VARCHAR(256), id INT) This is my ...guess. Is this correct? class table extends database { private function create($name, $query) { database::query("CREATE TABLE $name($query)"); } public function make($type) { switch ($type) { case "credentials": self::create('credentials', 'id INT NOT NULL AUTO_INCREMENT, flname VARCHAR(60), email VARCHAR(32), pass VARCHAR(40), PRIMARY KEY(id)'); break; case "booomark": self::create('boomark', 'name VARCHAR(64), url VARCHAR(256), tag VARCHAR(256), id INT'); break; case "tweet": self::create('tweet', 'time INT, fname VARCHAR(32), message VARCHAR(128), email VARCHAR(64)'); break; default: throw new Exception('Invalid Table Type'); } } }

    Read the article

  • Where do I find scripts generated by SharePoint MCMS Migration Profiles

    - by HipCzeck
    I am attempting to migrate data from an Microsoft Content Management Server (MCMS) 2002 instance into a new Microsoft Office Sharepoint Server (MOSS) 2007 installation using the Manage Microsoft Content Management Server Migration Profiles tool in the Operations space of MOSS Central Administration. When analyzing the profile, I receive 4 warnings, all of which may be safely ignored, but when I actually execute the migration profile, I get the same warnings and an additional error with a description of: Line 6: Incorrect syntax near ';'. I have seen this error numerous times when mucking about in SQL Server and recognize it as a Transact SQL error message, but can't find the actual SQL statement that is being executed so that I may determine the source of the error. EDIT: After enabling verbose logging on the MCMS 2002 Migration category, and poring through the Unified Logging Service (ULS) logs, I received a more complete stack trace at the point of the error, and a couple more anomalies listed below. Anomalies: The following is an abbreviated listing from the ULS logs around the time of the pre-migration analysis. 01 MCMS 2002 Migration Verbose Start ConnectionCheck 02 MCMS 2002 Migration Verbose End ConnectionCheck 03 MCMS 2002 Migration Verbose Start DatabaseCheck 04 MCMS 2002 Migration High Extra table SiteDeployLock will not be migrated 05 MCMS 2002 Migration High Analysis: Extra index PK__SiteDeployLock__05D8E0BE 06 MCMS 2002 Migration Verbose End DatabaseCheck 07 MCMS 2002 Migration Medium Pre-migration analysis: RootCheckTask is skipped because database check is blocked. 08 MCMS 2002 Migration Medium Pre-migration analysis: RightsGroupNameCheckTask is skipped because database check is blocked. 09 MCMS 2002 Migration Medium Pre-migration analysis: InvalidNameCheckTask is skipped because database check is blocked. 10 MCMS 2002 Migration Medium Pre-migration analysis: LeafNameCheckTask is skipped because database check is blocked. 11 MCMS 2002 Migration Medium Pre-migration analysis: LeafLengthCheckTask is skipped because database check is blocked. 12 MCMS 2002 Migration Medium Pre-migration analysis: TemplateNameCheckTask is skipped because database check is blocked. 13 MCMS 2002 Migration Medium Pre-migration analysis: TemplateCollisionCheckTask is skipped because database check is blocked. 14 MCMS 2002 Migration Medium Pre-migration analysis: PlaceholderCheckTask is skipped because database check is blocked. 15 MCMS 2002 Migration Medium Pre-migration analysis: CheckedOutItemsCheckTask is skipped because database check is blocked. 16 MCMS 2002 Migration Medium Pre-migration analysis: SubmittedItemsCheckTask is skipped because database check is blocked. 17 MCMS 2002 Migration Medium Pre-migration analysis: DeletedItemsCheckTask is skipped because database check is blocked. 18 MCMS 2002 Migration Medium Pre-migration analysis: UserCheckTask is skipped because database check is blocked. 19 MCMS 2002 Migration Medium Pre-migration analysis: FileSizeCheckTask is skipped because database check is blocked. 20 MCMS 2002 Migration Medium Pre-migration analysis: HostHeaderMapCheckTask is skipped because database check is blocked. 21 MCMS 2002 Migration Verbose Start Server check 22 MCMS 2002 Migration Verbose End Server check 23 MCMS 2002 Migration Verbose Start Server emptyness check 24 MCMS 2002 Migration Verbose End Server emptyness check 25 MCMS 2002 Migration Medium PreMigrationAnalyzer: Dry run starts 26 MCMS 2002 Migration Verbose CleanLockProcedure: start. 27 MCMS 2002 Migration High CleanLockProcedure: connection system lock is null 28 MCMS 2002 Migration Verbose Finished all tasks 29 MCMS 2002 Migration High PreMigrationAnalyzer ends with True 30 MCMS 2002 Migration Verbose Migration profile status is changed to AnalysisPassed Specifically, the two High level alerts on lines 4 and 5 are reflected in the migration report as warnings when running Pre-migration Analysis or running the migration profile. In addition, two other warnings appear in the migration report indicating two tables containing data (LayoutProperty and NodeLayout) that should be empty. According to the documentation, warnings are not sufficient cause to stop migration from occurring. Other anomalies are on lines 7-20 indicating a series of tests that are skipped because database check is blocked. The ULS doesn't give any additional warnings to indicate that the database check was blocked or exited in exceptional circumstances. After switching the profile from pre-migration analysis to exporting, there is one medium level warning that LastChangeTime is not set or incorrect. (null). As with all the skipped test names and SQL table names from the warnings, the major search engines are unable (with the exception of LayoutProperty) to find any reference to these objects or tests. Finally, the section of the log indicating the actual live migration attempt is appended below: 01 MCMS 2002 Migration Medium LastChangeTime is not set or incorrect. (null) 02 MCMS 2002 Migration Verbose Set export lock 03 MCMS 2002 Migration Verbose CleanLockProcedure: start. 04 MCMS 2002 Migration Verbose CleanLockProcedure: end. 05 MCMS 2002 Migration Verbose Prepare for export 06 MCMS 2002 Migration Verbose Open connection... 07 MCMS 2002 Migration Verbose Create temporary stored procedures 08 MCMS 2002 Migration Verbose Create temporary tables... 09 MCMS 2002 Migration Verbose Initialize temporary tables... 10 MCMS 2002 Migration Verbose InitializeTemporaryTables: start 11 MCMS 2002 Migration Verbose Initialize export table... 12 MCMS 2002 Migration Verbose InitializeExportTable: start 13 MCMS 2002 Migration Verbose CleanLockProcedure: start. 14 MCMS 2002 Migration Verbose CleanLockProcedure: end. 15 MCMS 2002 Migration High Migration throws exception: Line 6: Incorrect syntax near ';'.. Stacktrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.SharePoint.Publishing.Internal.Administration... 16 MCMS 2002 Migration High ....MigrationBatchCommand.ExecuteImmediate(String command) at Microsoft.SharePoint.Publishing.Internal.Administration.MigrationBatchCommand.ExecuteWaitingCommands() at Microsoft.SharePoint.Publishing.Internal.Administration.MigrationDBSerializer.SerializeSelectedExportObject(StringCollection objectAttribs) at Microsoft.SharePoint.Publishing.Internal.Administration.MigrationDataAccess.InitializeExportTable(ScopeType scopeType) at Microsoft.SharePoint.Publishing.Internal.Administration.MigrationDataAccess.InitializeTemporaryTables(DateTime lastChangeTime) at Microsoft.SharePoint.Publishing.Internal.Administration.MigrationDataAccess.InitializeDatabase(DateTime lastChangeTime, Boolean isAnalysis, SqlConnection connection) at Microsoft.SharePoint.Publishing.Internal.Admin... 17 MCMS 2002 Migration High ...stration.MigrationDataAccess.InitializeDatabase(DateTime lastChangeTime, Boolean isAnalysis) at Microsoft.SharePoint.Publishing.Administration.ContentMigration.Export(MigrationDataAccess dataAccess) at Microsoft.SharePoint.Publishing.Administration.ContentMigration.MigrateInternal(). 18 MCMS 2002 Migration Verbose MigrationProfile: GetInstance. Start. 19 MCMS 2002 Migration Verbose MigrationProfile: GetInstance. End. 20 MCMS 2002 Migration Verbose Migration profile status is changed to Failed The stack trace of the failed parsing of the SQL command appear on lines 15-17. A cleaner version of the stack trace is appended below. Full Stack Trace: Migration throws exception: Line 6: Incorrect syntax near ';'.. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning( TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.SharePoint.Publishing.Internal.Administration.MigrationBatchCommand .ExecuteImmediate(String command) at Microsoft.SharePoint.Publishing.Internal.Administration.MigrationBatchCommand .ExecuteWaitingCommands() at Microsoft.SharePoint.Publishing.Internal.Administration.MigrationDBSerializer .SerializeSelectedExportObject(StringCollection objectAttribs) at Microsoft.SharePoint.Publishing.Internal.Administration.MigrationDataAccess .InitializeExportTable(ScopeType scopeType) at Microsoft.SharePoint.Publishing.Internal.Administration.MigrationDataAccess .InitializeTemporaryTables(DateTime lastChangeTime) at Microsoft.SharePoint.Publishing.Internal.Administration.MigrationDataAccess .InitializeDatabase(DateTime lastChangeTime, Boolean isAnalysis, SqlConnection connection) at Microsoft.SharePoint.Publishing.Internal.Administration.MigrationDataAccess .InitializeDatabase(DateTime lastChangeTime, Boolean isAnalysis) at Microsoft.SharePoint.Publishing.Administration.ContentMigration.Export (MigrationDataAccess dataAccess) at Microsoft.SharePoint.Publishing.Administration.ContentMigration .MigrateInternal(). None of this log information indicates the SQL command that is failing a parser check. I've checked the SQL servers hosting the source and destination databases for a trace of the query, but neither seems to have triggered the parse failure condition. That appears to have happened on the SharePoint server. Are there any other locations I should investigate that might tell me where to find the source of the error?

    Read the article

  • SharePoint threw "Unknown SQL Exception 206 occured." Anyone familiar with this?

    - by dalehhirt
    Our SharePoint instance threw the following errors when attempting to access data through a Content Query Tool: 04/02/2010 10:45:06.12 w3wp.exe (0x062C) 0x1734 Windows SharePoint Services Database 5586 Critical Unknown SQL Exception 206 occured. Additional error information from SQL Server is included below. Operand type clash: uniqueidentifier is incompatible with datetime 04/02/2010 10:45:06.25 w3wp.exe (0x062C) 0x1734 Office Server Office Server General 900n Critical A runtime exception was detected. Details follow. Message: Operand type clash: uniqueidentifier is incompatible with datetime Techinal Details: System.Data.SqlClient.SqlException: Operand type clash: uniqueidentifier is incompatible with datetime at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData(... 04/02/2010 10:45:06.25* w3wp.exe (0x062C) 0x1734 Office Server Office Server General 900n Critical ...) at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlC 04/02/2010 10:45:06.25 w3wp.exe (0x062C) 0x1734 CMS Publishing 8vyd Exception (Watson Reporting Cancelled) System.Data.SqlClient.SqlException: Operand type clash: uniqueidentifier is incompatible with datetime at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteRead... 04/02/2010 10:45:06.25* w3wp.exe (0x062C) 0x1734 CMS Publishing 8vyd Exception ...er(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command, ... 04/02/2010 10:45:06.25* w3wp.exe (0x062C) 0x1734 CMS Publishing 8vyd Exception ...CommandBehavior behavior) at Microsoft.SharePoint.SPSqlClient.ExecuteQuery(Boolean& bSucceed) at Microsoft.SharePoint.Library.SPRequestInternalClass.CrossListQuery(String bstrUrl, String bstrXmlWebs, String bstrXmlLists, String bstrXmlQuery, ISP2DSafeArrayWriter pCallback, Object& pvarColumns) at Microsoft.SharePoint.Library.SPRequest.CrossListQuery(String bstrUrl, String bstrXmlWebs, String bstrXmlLists, String bstrXmlQuery, ISP2DSafeArrayWriter pCallback, Object& pvarColumns) at Microsoft.SharePoint.SPWeb.GetSiteData(SPSiteDataQuery query) at Microsoft.SharePoint.Publishing.CachedArea.GetCrossListQuery(SPSiteDataQuery query, SPWeb currentContext) at Microsoft.SharePoint.Publishing.CrossListQueryCache.GetSiteData(CachedArea cachedArea, SPWeb web, SPSiteDataQuery qu... 04/02/2010 10:45:06.25* w3wp.exe (0x062C) 0x1734 CMS Publishing 8vyd Exception ...ery) 04/02/2010 10:45:06.25 w3wp.exe (0x062C) 0x1734 CMS Publishing 78ed Warning Error occured while processing a Content Query Web Part. Performing the following query ' 04/02/2010 10:45:06.25* w3wp.exe (0x062C) 0x1734 CMS Publishing 78ed Warning ...ue" Type="Number"/ The farm is MOSS 2007 with SQL Server 2005 backend. Any ideas are welcomed. Dale

    Read the article

  • An Xml Serializable PropertyBag Dictionary Class for .NET

    - by Rick Strahl
    I don't know about you but I frequently need property bags in my applications to store and possibly cache arbitrary data. Dictionary<T,V> works well for this although I always seem to be hunting for a more specific generic type that provides a string key based dictionary. There's string dictionary, but it only works with strings. There's Hashset<T> but it uses the actual values as keys. In most key value pair situations for me string is key value to work off. Dictionary<T,V> works well enough, but there are some issues with serialization of dictionaries in .NET. The .NET framework doesn't do well serializing IDictionary objects out of the box. The XmlSerializer doesn't support serialization of IDictionary via it's default serialization, and while the DataContractSerializer does support IDictionary serialization it produces some pretty atrocious XML. What doesn't work? First off Dictionary serialization with the Xml Serializer doesn't work so the following fails: [TestMethod] public void DictionaryXmlSerializerTest() { var bag = new Dictionary<string, object>(); bag.Add("key", "Value"); bag.Add("Key2", 100.10M); bag.Add("Key3", Guid.NewGuid()); bag.Add("Key4", DateTime.Now); bag.Add("Key5", true); bag.Add("Key7", new byte[3] { 42, 45, 66 }); TestContext.WriteLine(this.ToXml(bag)); } public string ToXml(object obj) { if (obj == null) return null; StringWriter sw = new StringWriter(); XmlSerializer ser = new XmlSerializer(obj.GetType()); ser.Serialize(sw, obj); return sw.ToString(); } The error you get with this is: System.NotSupportedException: The type System.Collections.Generic.Dictionary`2[[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.Object, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] is not supported because it implements IDictionary. Got it! BTW, the same is true with binary serialization. Running the same code above against the DataContractSerializer does work: [TestMethod] public void DictionaryDataContextSerializerTest() { var bag = new Dictionary<string, object>(); bag.Add("key", "Value"); bag.Add("Key2", 100.10M); bag.Add("Key3", Guid.NewGuid()); bag.Add("Key4", DateTime.Now); bag.Add("Key5", true); bag.Add("Key7", new byte[3] { 42, 45, 66 }); TestContext.WriteLine(this.ToXmlDcs(bag)); } public string ToXmlDcs(object value, bool throwExceptions = false) { var ser = new DataContractSerializer(value.GetType(), null, int.MaxValue, true, false, null); MemoryStream ms = new MemoryStream(); ser.WriteObject(ms, value); return Encoding.UTF8.GetString(ms.ToArray(), 0, (int)ms.Length); } This DOES work but produces some pretty heinous XML (formatted with line breaks and indentation here): <ArrayOfKeyValueOfstringanyType xmlns="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"> <KeyValueOfstringanyType> <Key>key</Key> <Value i:type="a:string" xmlns:a="http://www.w3.org/2001/XMLSchema">Value</Value> </KeyValueOfstringanyType> <KeyValueOfstringanyType> <Key>Key2</Key> <Value i:type="a:decimal" xmlns:a="http://www.w3.org/2001/XMLSchema">100.10</Value> </KeyValueOfstringanyType> <KeyValueOfstringanyType> <Key>Key3</Key> <Value i:type="a:guid" xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/">2cd46d2a-a636-4af4-979b-e834d39b6d37</Value> </KeyValueOfstringanyType> <KeyValueOfstringanyType> <Key>Key4</Key> <Value i:type="a:dateTime" xmlns:a="http://www.w3.org/2001/XMLSchema">2011-09-19T17:17:05.4406999-07:00</Value> </KeyValueOfstringanyType> <KeyValueOfstringanyType> <Key>Key5</Key> <Value i:type="a:boolean" xmlns:a="http://www.w3.org/2001/XMLSchema">true</Value> </KeyValueOfstringanyType> <KeyValueOfstringanyType> <Key>Key7</Key> <Value i:type="a:base64Binary" xmlns:a="http://www.w3.org/2001/XMLSchema">Ki1C</Value> </KeyValueOfstringanyType> </ArrayOfKeyValueOfstringanyType> Ouch! That seriously hurts the eye! :-) Worse though it's extremely verbose with all those repetitive namespace declarations. It's good to know that it works in a pinch, but for a human readable/editable solution or something lightweight to store in a database it's not quite ideal. Why should I care? As a little background, in one of my applications I have a need for a flexible property bag that is used on a free form database field on an otherwise static entity. Basically what I have is a standard database record to which arbitrary properties can be added in an XML based string field. I intend to expose those arbitrary properties as a collection from field data stored in XML. The concept is pretty simple: When loading write the data to the collection, when the data is saved serialize the data into an XML string and store it into the database. When reading the data pick up the XML and if the collection on the entity is accessed automatically deserialize the XML into the Dictionary. (I'll talk more about this in another post). While the DataContext Serializer would work, it's verbosity is problematic both for size of the generated XML strings and the fact that users can manually edit this XML based property data in an advanced mode. A clean(er) layout certainly would be preferable and more user friendly. Custom XMLSerialization with a PropertyBag Class So… after a bunch of experimentation with different serialization formats I decided to create a custom PropertyBag class that provides for a serializable Dictionary. It's basically a custom Dictionary<TType,TValue> implementation with the keys always set as string keys. The result are PropertyBag<TValue> and PropertyBag (which defaults to the object type for values). The PropertyBag<TType> and PropertyBag classes provide these features: Subclassed from Dictionary<T,V> Implements IXmlSerializable with a cleanish XML format ToXml() and FromXml() methods to export and import to and from XML strings Static CreateFromXml() method to create an instance It's simple enough as it's merely a Dictionary<string,object> subclass but that supports serialization to a - what I think at least - cleaner XML format. The class is super simple to use: [TestMethod] public void PropertyBagTwoWayObjectSerializationTest() { var bag = new PropertyBag(); bag.Add("key", "Value"); bag.Add("Key2", 100.10M); bag.Add("Key3", Guid.NewGuid()); bag.Add("Key4", DateTime.Now); bag.Add("Key5", true); bag.Add("Key7", new byte[3] { 42,45,66 } ); bag.Add("Key8", null); bag.Add("Key9", new ComplexObject() { Name = "Rick", Entered = DateTime.Now, Count = 10 }); string xml = bag.ToXml(); TestContext.WriteLine(bag.ToXml()); bag.Clear(); bag.FromXml(xml); Assert.IsTrue(bag["key"] as string == "Value"); Assert.IsInstanceOfType( bag["Key3"], typeof(Guid)); Assert.IsNull(bag["Key8"]); //Assert.IsNull(bag["Key10"]); Assert.IsInstanceOfType(bag["Key9"], typeof(ComplexObject)); } This uses the PropertyBag class which uses a PropertyBag<string,object> - which means it returns untyped values of type object. I suspect for me this will be the most common scenario as I'd want to store arbitrary values in the PropertyBag rather than one specific type. The same code with a strongly typed PropertyBag<decimal> looks like this: [TestMethod] public void PropertyBagTwoWayValueTypeSerializationTest() { var bag = new PropertyBag<decimal>(); bag.Add("key", 10M); bag.Add("Key1", 100.10M); bag.Add("Key2", 200.10M); bag.Add("Key3", 300.10M); string xml = bag.ToXml(); TestContext.WriteLine(bag.ToXml()); bag.Clear(); bag.FromXml(xml); Assert.IsTrue(bag.Get("Key1") == 100.10M); Assert.IsTrue(bag.Get("Key3") == 300.10M); } and produces typed results of type decimal. The types can be either value or reference types the combination of which actually proved to be a little more tricky than anticipated due to null and specific string value checks required - getting the generic typing right required use of default(T) and Convert.ChangeType() to trick the compiler into playing nice. Of course the whole raison d'etre for this class is the XML serialization. You can see in the code above that we're doing a .ToXml() and .FromXml() to serialize to and from string. The XML produced for the first example looks like this: <?xml version="1.0" encoding="utf-8"?> <properties> <item> <key>key</key> <value>Value</value> </item> <item> <key>Key2</key> <value type="decimal">100.10</value> </item> <item> <key>Key3</key> <value type="___System.Guid"> <guid>f7a92032-0c6d-4e9d-9950-b15ff7cd207d</guid> </value> </item> <item> <key>Key4</key> <value type="datetime">2011-09-26T17:45:58.5789578-10:00</value> </item> <item> <key>Key5</key> <value type="boolean">true</value> </item> <item> <key>Key7</key> <value type="base64Binary">Ki1C</value> </item> <item> <key>Key8</key> <value type="nil" /> </item> <item> <key>Key9</key> <value type="___Westwind.Tools.Tests.PropertyBagTest+ComplexObject"> <ComplexObject> <Name>Rick</Name> <Entered>2011-09-26T17:45:58.5789578-10:00</Entered> <Count>10</Count> </ComplexObject> </value> </item> </properties>   The format is a bit cleaner than the DataContractSerializer. Each item is serialized into <key> <value> pairs. If the value is a string no type information is written. Since string tends to be the most common type this saves space and serialization processing. All other types are attributed. Simple types are mapped to XML types so things like decimal, datetime, boolean and base64Binary are encoded using their Xml type values. All other types are embedded with a hokey format that describes the .NET type preceded by a three underscores and then are encoded using the XmlSerializer. You can see this best above in the ComplexObject encoding. For custom types this isn't pretty either, but it's more concise than the DCS and it works as long as you're serializing back and forth between .NET clients at least. The XML generated from the second example that uses PropertyBag<decimal> looks like this: <?xml version="1.0" encoding="utf-8"?> <properties> <item> <key>key</key> <value type="decimal">10</value> </item> <item> <key>Key1</key> <value type="decimal">100.10</value> </item> <item> <key>Key2</key> <value type="decimal">200.10</value> </item> <item> <key>Key3</key> <value type="decimal">300.10</value> </item> </properties>   How does it work As I mentioned there's nothing fancy about this solution - it's little more than a subclass of Dictionary<T,V> that implements custom Xml Serialization and a couple of helper methods that facilitate getting the XML in and out of the class more easily. But it's proven very handy for a number of projects for me where dynamic data storage is required. Here's the code: /// <summary> /// Creates a serializable string/object dictionary that is XML serializable /// Encodes keys as element names and values as simple values with a type /// attribute that contains an XML type name. Complex names encode the type /// name with type='___namespace.classname' format followed by a standard xml /// serialized format. The latter serialization can be slow so it's not recommended /// to pass complex types if performance is critical. /// </summary> [XmlRoot("properties")] public class PropertyBag : PropertyBag<object> { /// <summary> /// Creates an instance of a propertybag from an Xml string /// </summary> /// <param name="xml">Serialize</param> /// <returns></returns> public static PropertyBag CreateFromXml(string xml) { var bag = new PropertyBag(); bag.FromXml(xml); return bag; } } /// <summary> /// Creates a serializable string for generic types that is XML serializable. /// /// Encodes keys as element names and values as simple values with a type /// attribute that contains an XML type name. Complex names encode the type /// name with type='___namespace.classname' format followed by a standard xml /// serialized format. The latter serialization can be slow so it's not recommended /// to pass complex types if performance is critical. /// </summary> /// <typeparam name="TValue">Must be a reference type. For value types use type object</typeparam> [XmlRoot("properties")] public class PropertyBag<TValue> : Dictionary<string, TValue>, IXmlSerializable { /// <summary> /// Not implemented - this means no schema information is passed /// so this won't work with ASMX/WCF services. /// </summary> /// <returns></returns> public System.Xml.Schema.XmlSchema GetSchema() { return null; } /// <summary> /// Serializes the dictionary to XML. Keys are /// serialized to element names and values as /// element values. An xml type attribute is embedded /// for each serialized element - a .NET type /// element is embedded for each complex type and /// prefixed with three underscores. /// </summary> /// <param name="writer"></param> public void WriteXml(System.Xml.XmlWriter writer) { foreach (string key in this.Keys) { TValue value = this[key]; Type type = null; if (value != null) type = value.GetType(); writer.WriteStartElement("item"); writer.WriteStartElement("key"); writer.WriteString(key as string); writer.WriteEndElement(); writer.WriteStartElement("value"); string xmlType = XmlUtils.MapTypeToXmlType(type); bool isCustom = false; // Type information attribute if not string if (value == null) { writer.WriteAttributeString("type", "nil"); } else if (!string.IsNullOrEmpty(xmlType)) { if (xmlType != "string") { writer.WriteStartAttribute("type"); writer.WriteString(xmlType); writer.WriteEndAttribute(); } } else { isCustom = true; xmlType = "___" + value.GetType().FullName; writer.WriteStartAttribute("type"); writer.WriteString(xmlType); writer.WriteEndAttribute(); } // Actual deserialization if (!isCustom) { if (value != null) writer.WriteValue(value); } else { XmlSerializer ser = new XmlSerializer(value.GetType()); ser.Serialize(writer, value); } writer.WriteEndElement(); // value writer.WriteEndElement(); // item } } /// <summary> /// Reads the custom serialized format /// </summary> /// <param name="reader"></param> public void ReadXml(System.Xml.XmlReader reader) { this.Clear(); while (reader.Read()) { if (reader.NodeType == XmlNodeType.Element && reader.Name == "key") { string xmlType = null; string name = reader.ReadElementContentAsString(); // item element reader.ReadToNextSibling("value"); if (reader.MoveToNextAttribute()) xmlType = reader.Value; reader.MoveToContent(); TValue value; if (xmlType == "nil") value = default(TValue); // null else if (string.IsNullOrEmpty(xmlType)) { // value is a string or object and we can assign TValue to value string strval = reader.ReadElementContentAsString(); value = (TValue) Convert.ChangeType(strval, typeof(TValue)); } else if (xmlType.StartsWith("___")) { while (reader.Read() && reader.NodeType != XmlNodeType.Element) { } Type type = ReflectionUtils.GetTypeFromName(xmlType.Substring(3)); //value = reader.ReadElementContentAs(type,null); XmlSerializer ser = new XmlSerializer(type); value = (TValue)ser.Deserialize(reader); } else value = (TValue)reader.ReadElementContentAs(XmlUtils.MapXmlTypeToType(xmlType), null); this.Add(name, value); } } } /// <summary> /// Serializes this dictionary to an XML string /// </summary> /// <returns>XML String or Null if it fails</returns> public string ToXml() { string xml = null; SerializationUtils.SerializeObject(this, out xml); return xml; } /// <summary> /// Deserializes from an XML string /// </summary> /// <param name="xml"></param> /// <returns>true or false</returns> public bool FromXml(string xml) { this.Clear(); // if xml string is empty we return an empty dictionary if (string.IsNullOrEmpty(xml)) return true; var result = SerializationUtils.DeSerializeObject(xml, this.GetType()) as PropertyBag<TValue>; if (result != null) { foreach (var item in result) { this.Add(item.Key, item.Value); } } else // null is a failure return false; return true; } /// <summary> /// Creates an instance of a propertybag from an Xml string /// </summary> /// <param name="xml"></param> /// <returns></returns> public static PropertyBag<TValue> CreateFromXml(string xml) { var bag = new PropertyBag<TValue>(); bag.FromXml(xml); return bag; } } } The code uses a couple of small helper classes SerializationUtils and XmlUtils for mapping Xml types to and from .NET, both of which are from the WestWind,Utilities project (which is the same project where PropertyBag lives) from the West Wind Web Toolkit. The code implements ReadXml and WriteXml for the IXmlSerializable implementation using old school XmlReaders and XmlWriters (because it's pretty simple stuff - no need for XLinq here). Then there are two helper methods .ToXml() and .FromXml() that basically allow your code to easily convert between XML and a PropertyBag object. In my code that's what I use to actually to persist to and from the entity XML property during .Load() and .Save() operations. It's sweet to be able to have a string key dictionary and then be able to turn around with 1 line of code to persist the whole thing to XML and back. Hopefully some of you will find this class as useful as I've found it. It's a simple solution to a common requirement in my applications and I've used the hell out of it in the  short time since I created it. Resources You can find the complete code for the two classes plus the helpers in the Subversion repository for Westwind.Utilities. You can grab the source files from there or download the whole project. You can also grab the full Westwind.Utilities assembly from NuGet and add it to your project if that's easier for you. PropertyBag Source Code SerializationUtils and XmlUtils Westwind.Utilities Assembly on NuGet (add from Visual Studio) © Rick Strahl, West Wind Technologies, 2005-2011Posted in .NET  CSharp   Tweet (function() { var po = document.createElement('script'); po.type = 'text/javascript'; po.async = true; po.src = 'https://apis.google.com/js/plusone.js'; var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(po, s); })();

    Read the article

  • More SQL Smells

    - by Nick Harrison
    Let's continue exploring some of the SQL Smells from Phil's list. He has been putting together. Datatype mis-matches in predicates that rely on implicit conversion.(Plamen Ratchev) This is a great example poking holes in the whole theory of "If it works it's not broken" Queries will this probably will generally work and give the correct response. In fact, without careful analysis, you probably may be completely oblivious that there is even a problem. This subtle little problem will needlessly complicate queries and slow them down regardless of the indexes applied. Consider this example: CREATE TABLE [dbo].[Page](     [PageId] [int] IDENTITY(1,1) NOT NULL,     [Title] [varchar](75) NOT NULL,     [Sequence] [int] NOT NULL,     [ThemeId] [int] NOT NULL,     [CustomCss] [text] NOT NULL,     [CustomScript] [text] NOT NULL,     [PageGroupId] [int] NOT NULL;  CREATE PROCEDURE PageSelectBySequence ( @sequenceMin smallint , @sequenceMax smallint ) AS BEGIN SELECT [PageId] , [Title] , [Sequence] , [ThemeId] , [CustomCss] , [CustomScript] , [PageGroupId] FROM [CMS].[dbo].[Page] WHERE Sequence BETWEEN @sequenceMin AND @SequenceMax END  Note that the Sequence column is defined as int while the sequence parameter is defined as a small int. The problem is that the database may have to do a lot of type conversions to evaluate the query. In some cases, this may even negate the indexes that you have in place. Using Correlated subqueries instead of a join   (Dave_Levy/ Plamen Ratchev) There are two main problems here. The first is a little subjective, since this is a non-standard way of expressing the query, it is harder to understand. The other problem is much more objective and potentially problematic. You are taking much of the control away from the optimizer. Written properly, such a query may well out perform a corresponding query written with traditional joins. More likely than not, performance will degrade. Whenever you assume that you know better than the optimizer, you will most likely be wrong. This is the fundmental problem with any hint. Consider a query like this:  SELECT Page.Title , Page.Sequence , Page.ThemeId , Page.CustomCss , Page.CustomScript , PageEffectParams.Name , PageEffectParams.Value , ( SELECT EffectName FROM dbo.Effect WHERE EffectId = dbo.PageEffects.EffectId ) AS EffectName FROM Page INNER JOIN PageEffect ON Page.PageId = PageEffects.PageId INNER JOIN PageEffectParam ON PageEffects.PageEffectId = PageEffectParams.PageEffectId  This can and should be written as:  SELECT Page.Title , Page.Sequence , Page.ThemeId , Page.CustomCss , Page.CustomScript , PageEffectParams.Name , PageEffectParams.Value , EffectName FROM Page INNER JOIN PageEffect ON Page.PageId = PageEffects.PageId INNER JOIN PageEffectParam ON PageEffects.PageEffectId = PageEffectParams.PageEffectId INNER JOIN dbo.Effect ON dbo.Effects.EffectId = dbo.PageEffects.EffectId  The correlated query may just as easily show up in the where clause. It's not a good idea in the select clause or the where clause. Few or No comments. This one is a bit more complicated and controversial. All comments are not created equal. Some comments are helpful and need to be included. Other comments are not necessary and may indicate a problem. I tend to follow the rule of thumb that comments that explain why are good. Comments that explain how are bad. Many people may be shocked to hear the idea of a bad comment, but hear me out. If a comment is needed to explain what is going on or how it works, the logic is too complex and needs to be simplified. Comments that explain why are good. Comments may explain why the sql is needed are good. Comments that explain where the sql is used are good. Comments that explain how tables are related should not be needed if the sql is well written. If they are needed, you need to consider reworking the sql or simplify your data model. Use of functions in a WHERE clause. (Anil Das) Calling a function in the where clause will often negate the indexing strategy. The function will be called for every record considered. This will often a force a full table scan on the tables affected. Calling a function will not guarantee that there is a full table scan, but there is a good chance that it will. If you find that you often need to write queries using a particular function, you may need to add a column to the table that has the function already applied.

    Read the article

  • XML DB Content Connector unable to accept binary content due to Invalid argument(s) in call oracle.sql.BLOB.setBinaryStream(0L)

    - by sthieme
    Dear Readers, I am working on implementing a custom Document Management System using the Oracle XML DB Content Connector. See the following documentation link for details Oracle XML DB Developer's Guide 11g Release 2 (11.2)Chapter 31 Using Oracle XML DB Content Connectorhttp://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_jcr.htm especially the following example gave me some trouble to run it successfully Sample Code to Upload Filehttp://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_jcr.htm#ADXDB5627 I had already succeeded to set some of the properties successfully, i.e. jcr:encoding, jcr:mimeType, ojcr:displayName and ojcr:language. However setting the jcr:data property as described in the example failed consistently, both with the documented input FileStream or with a fixed string. contentNode.setProperty("jcr:data", "mystringvalue"); After some research I found the following Support Note which describes the cause for the issue in the JDBC driver version 11.2.0.1. Error "ORA-17068: Invalid argument(s) in call" Using Method setBinaryStream(0L) in JDBC 11.2.0.1 (Doc ID 1234235.1)https://support.oracle.com/epmos/faces/DocContentDisplay?id=1234235.1It can easily be solved by upgrading to JDBC 11.2.0.2 or worked around using the following property setting: java -Doracle.jdbc.LobStreamPosStandardCompliant=false ... Kind regards,Stefan C:\Oracle\Database\product\11.2.0\dbhome_1>java -Doracle.jdbc.LobStreamPosStandardCompliant=false UploadFile jdbc:oracle:oci:@localhost:1522:orcl XDB welcome1 /public MyFile.txt text/plain 19.08.2014 11:50:26 oracle.jcr.impl.OracleRepositoryImpl login INFO: JCR repository descriptors: query.xpath.pos.index = true option.versioning.supported = false jcr.repository.version = 11.1.0.0.0 option.observation.supported = false option.locking.supported = false oracle.jcr.framework.version = 11.1.0.0.0 query.xpath.doc.order = false jcr.specification.version = 1.0 jcr.repository.vendor = Oracle option.query.sql.supported = false jcr.specification.name = Content Repository for Java Technology API level.2.supported = true level.1.supported = true jcr.repository.name = XML DB Content Connector jcr.repository.vendor.url = http://www.oracle.com oracle.jcr.persistenceManagerFactory = oracle.jcr.impl.xdb.XDBPersistenceManagerFactory option.transactions.supported = false 19.08.2014 11:50:26 oracle.jcr.impl.OracleRepositoryImpl login INFO: Session Session-1 connected for user id XDB 19.08.2014 11:50:27 oracle.jcr.impl.OracleSessionImpl logout INFO: Session-1: logout instead of C:\Oracle\Database\product\11.2.0\dbhome_1>java UploadFile jdbc:oracle:oci:@localhost:1522:orcl XDB welcome1 /public MyFile.txt text/plain 19.08.2014 10:56:39 oracle.jcr.impl.OracleRepositoryImpl login INFO: JCR repository descriptors: query.xpath.pos.index = true option.versioning.supported = false jcr.repository.version = 11.1.0.0.0 option.observation.supported = false option.locking.supported = false oracle.jcr.framework.version = 11.1.0.0.0 query.xpath.doc.order = false jcr.specification.version = 1.0 jcr.repository.vendor = Oracle option.query.sql.supported = false jcr.specification.name = Content Repository for Java Technology API level.2.supported = true level.1.supported = true jcr.repository.name = XML DB Content Connector jcr.repository.vendor.url = http://www.oracle.com oracle.jcr.persistenceManagerFactory = oracle.jcr.impl.xdb.XDBPersistenceManagerFactory option.transactions.supported = false 19.08.2014 10:56:39 oracle.jcr.impl.OracleRepositoryImpl login INFO: Session Session-1 connected for user id XDB Exception in thread "main" javax.jcr.RepositoryException: Unable to accept binary content at oracle.jcr.impl.ExceptionFactory.repository(ExceptionFactory.java:142) at oracle.jcr.impl.ExceptionFactory.otherwiseFailed(ExceptionFactory.java:98) at oracle.jcr.impl.xdb.XDBPersistenceManager.acceptBinaryStream(XDBPersistenceManager.java:1421) at oracle.jcr.impl.xdb.XDBResource.setContent(XDBResource.java:898) at oracle.jcr.impl.ContentNode.setProperty(ContentNode.java:472) at oracle.jcr.impl.OracleNode.setProperty(OracleNode.java:1439) at oracle.jcr.impl.OracleNode.setProperty(OracleNode.java:460) at UploadFile.main(UploadFile.java:54) Caused by: java.sql.SQLException: Invalid argument(s) in call at oracle.jdbc.driver.T2CConnection.newOutputStream(T2CConnection.java:2392) at oracle.sql.BLOB.setBinaryStream(BLOB.java:893) at oracle.jcr.impl.xdb.XDBPersistenceManager.acceptBinaryStream(XDBPersistenceManager.java:1393) ... 5 more

    Read the article

  • Using rel=next and rel=prev with multiple sets of paginated content on the same page

    - by jakejgordon
    We are running into issues with trying to figure out how to implement rel="next" and rel="prev" -- coupled with rel="canonical" -- with multiple sets of paginated content on the same page, with pages in multiple cultures. In other words, how do we implement these when we have a pager for both Product Reviews and Questions and Answers (aka "Q&A") on the same page, with duplicate content across culture-specific URLs (e.g. /us/en/my-product vs. /ca/en/my-product)? Our current implementation will actually do a full postback when you click Page 2, and will add something to the query string (e.g. website.com/ca/en/my-product?previewpage=2 or website.com/ca/en/my-product?questionpage=2). If we only had one set of paginated content then the implementation would certainly be more straightforward. Adding a second set of paginated content (i.e. Q&A) complicates things. Let's assume that we want the United States English page to be the canonical target (i.e. /us/en/my-product) based on culture. If you go to the /ca/en/my-product page you'll have a rel="canonical" href="/us/en/my-product". So far so good. Let's also assume that we are not implementing a page that lists ALL Product Reviews and Q&A. This would likely solve a number of our problems by using rel="canonical" to this page, but is not an option for reasons that are out of scope for this discussion. Now if you click on page 2 of Product Reviews, it will reload the page with /ca/en/my-product?reviewpage=2 as the URL. Given this scenario, here are my questions: On page 2 of the my-product page on the Canadian site, should there be a rel="canonical" to /us/en/my-product?reviewpage=2 (assuming the content is identical in the United States and Canada)? Should the rel="prev" go to /ca/en/my-product?reviewpage=1 or should it go to /ca/en/my-product ? The query-string version would really only be accessible if using the pager and shows the exact same content as the base page. The following two questions are closely related to this one. Should the /ca/en/my-product?reviewpage=1 have a rel canonical directly to /us/en/my-product (United States page with nothing in query string) since the content is identical)? Given that Q&A content is also paginated, should there be a rel="next" on the base page without query string? In other words, should the /ca/en/my-product page have a rel="next" to /ca/en/my-product?reviewpage=2 AND rel="next" to /ca/en/my-product?questionpage=2 . So far as I can tell it doesn't make sense to have multiple rel="next" implementations on the same page. I suspect that the pages with query string values should have rel="next" and rel="prev" that only point to other pages with query strings and not to the base page. The ?reviewpage=1 and ?questionpage=1 pages would then just have a rel="canonical" to /us/en/my-product . Thoughts? I know this is a tough one -- that's why I brought it to this community. Thanks so much for your help in advance!

    Read the article

  • SQL to select random mix of rows fairly [migrated]

    - by Matt Sieker
    Here's my problem: I have a set of tables in a database populated with data from a client that contains product information. In addition to the basic product information, there is also information about the manufacturer, and categories for those products (a product can be in one or more categories). These categories are then referred to as "Product Categories", and which stores these products are available at. These tables are updated once a week from a feed from the customer. Since for our purposes, some of the product categories are the same, or closely related for our purposes, there is another level of categories called "General Categories", a general category can have one or more product categories. For the scope of these tables, here's some rough numbers: Data Tables: Products: 475,000 Manufacturers: 1300 Stores: 150 General Categories: 245 Product Categories: 500 Mapping Tables: Product Category -> Product: 655,000 Stores -> Products: 50,000,000 Now, for the actual problem: As part of our software, we need to select n random products, given a store and a general category. However, we also need to ensure a good mix of manufacturers, as in some categories, a single manufacturer dominates the results, and selecting rows at random causes the results to strongly favor that manufacturer. The solution that is currently in place, works for most cases, involves selecting all of the rows that match the store and category criteria, partition them on manufacturer, and include their row number from within their partition, then select from that where the row number for that manufacturer is less than n, and use ROWCOUNT to clamp the total rows returned to n. This query looks something like this: SET ROWCOUNT 6 select p.Id, GeneralCategory_Id, Product_Id, ISNULL(m.DisplayName, m.Name) AS Vendor, MSRP, MemberPrice, FamilyImageName from (select p.Id, gc.Id GeneralCategory_Id, p.Id Product_Id, ctp.Store_id, Manufacturer_id, ROW_NUMBER() OVER (PARTITION BY Manufacturer_id ORDER BY NEWID()) AS 'VendorOrder', MSRP, MemberPrice, FamilyImageName from GeneralCategory gc inner join GeneralCategoriesToProductCategories gctpc ON gc.Id=gctpc.GeneralCategory_Id inner join ProductCategoryToProduct pctp on gctpc.ProductCategory_Id = pctp.ProductCategory_Id inner join Product p on p.Id = pctp.Product_Id inner join StoreToProduct ctp on p.Id = ctp.Product_id where gc.Id = @GeneralCategory and ctp.Store_id=@StoreId and p.Active=1 and p.MemberPrice >0) p inner join Manufacturer m on m.Id = p.Manufacturer_id where VendorOrder <=6 order by NEWID() SET ROWCOUNT 0 (I've tried to somewhat format it to make it cleaner, but I don't think it really helps) Running this query with an execution plan shows that for the majority of these tables, it's doing a Clustered Index Seek. There are two operations that take up roughly 90% of the time: Index Seek (Nonclustered) on StoreToProduct: 17%. This table just contains the key of the store, and the key of the product. It seems that NHibernate decided not to make a composite key when making this table, but I'm not concerned about this at this point, as compared to the other seek... Clustered Index Seek on Product: 69%. I really have no clue how I could make this one more performant. On categories without a lot of products, performance is acceptable (<50ms), however larger categories can take a few hundred ms, with the largest category taking 3s (which has about 170k products). It seems I have two ways to go from this point: Somehow optimize the existing query and table indices to lower the query time. As almost every expensive operation is already a clustered index scan, I don't know what could be done there. The inner query could be tuned to not return all of the possible rows for that category, but I am unsure how to do this, and maintain the requirements (random products, with a good mix of manufacturers) Denormalize this data for the purpose of this query when doing the once a week import. However, I am unsure how to do this and maintain the requirements. Does anyone have any input on either of these items?

    Read the article

  • What Counts For a DBA – Depth

    - by Louis Davidson
    SQL Server offers very simple interfaces to many of its features. Most people could open up SSMS, connect to a server, write a simple query and see the results. Even several of the core DBA tasks are deceptively straightforward. It doesn’t take a rocket scientist to perform a basic database backup or run a trace (even using the newfangled Extended Events!). However, appearances can be deceptive, and often times it is really important that a DBA understands not just the basics of how to perform a task, but why we do a task, and how that task works. As an analogy, consider a child walking into a darkened room. Most would know that they need to turn on the light, and how to do it, so they flick the switch. But what happens if light fails to shine forth. Most would immediately tell you that you need to consider changing the light bulb. So you hop in the car and take them to the local home store and instruct them to buy a replacement. Confronted with a 40 foot display of light bulbs, how will they decide which of the hundreds of types of bulbs, of different types, fittings, shapes, colors, power and efficiency ratings, is the right choice? Obviously the main lesson the child is going to learn this day is how to use their cell phone as a flashlight so they don’t have to ask for help the next time. Likewise, when the metaphorical toddlers who use your database server have issues, they will instinctively know something is wrong, and may even have some idea what caused it, but will have no depth of knowledge to figure out the right solution. That is where the DBA comes in and attempts to save the day. However, when one looks beneath the shiny UI, SQL Server has its own “40 foot display of light bulbs”, in the form of the tremendous number of tools and the often-bewildering amount of information they can present to the DBA, to help us find issues. Unfortunately, resorting to guesswork, to trying different “bulbs” over and over, hoping to stumble on the answer. This is where the right depth of knowledge goes a long way. If we need to write a SELECT statement, then knowing the syntax and where to find the data is not enough. Knowledge of indexes and query plans is essential. Without it, we might hit on a query that “works”, but we are basically still a user, not a programmer, because we have no real control over our platform. Is that level of knowledge deep enough? Probably not, since knowledge of the underlying metadata and structures would be very useful in helping us make sense of any query plan. Understanding the structure of an index makes the “key lookup” operator not sound like what you do when someone tapes your car key to the ceiling. So is even this level of understanding deep enough? Do we need to understand the memory architecture used to process the query? It might be a comforting level of knowledge, and will doubtless come in handy at some point, but is not strictly necessary in most cases. Beyond that lies (more or less) full knowledge of SQL language and the intricacies of every step the SQL Server engine takes to process our query. My personal theory is that, as a professional, our knowledge of a given task should extend, at a minimum, one level deeper than is strictly necessary to perform the task. Anything deeper can be left to the ridiculously smart, or obsessive, or both. As an example. tasked with storing an integer value between 0 and 99999999, it’s essential that I know that choosing an Integer over Decimal(8,0) will likely offer performance benefits. It is then useful that I also understand the value of adding a CHECK constraint, to make sure the values are valid to the desired range; and comforting that I know a little about the underlying processors, registers and computer math. Anything further, I leave to the likes of Joe Chang, whose recent blog post on the topic offers depth by the bucketful!  

    Read the article

  • Is it possible that an insert would use parallelism?

    - by Lieven Cardoen
    In what situation can inserting a simple record (the table has got some 10 columns, two of them are xml) use parallelism? CREATE TABLE [dbo].[PackageSessions]( [PackageSessionId] [int] IDENTITY(1,1) NOT NULL, [PackageId] [int] NOT NULL, [UserId] [int] NOT NULL, [StartDateTime] [datetime] NULL, [StopDateTime] [datetime] NULL, [Score] [float] NOT NULL, [ScoreMax] [float] NOT NULL, [CompletionStatus] [int] NOT NULL, [ReviewPlayerContextId] [int] NOT NULL, [PlayerContextId] [int] NOT NULL, [ReducedScore] [float] NOT NULL, [ReducedScoreMax] [float] NOT NULL, [PackageSnapShot] [xml] NULL, [InterfaceLanguageId] [int] NOT NULL, [Data] [xml] NULL, [PackageSessionLanguageId] [int] NOT NULL, CONSTRAINT [PackageSessions_PK] PRIMARY KEY CLUSTERED ( [PackageSessionId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

    Read the article

  • How to manage GetDate() with Entity Framework

    - by wcpro
    I have a column like this in 1 of my database tables DateCreated, datetime, default(GetDate()), not null I am trying to use the Entity Framework to do an insert on this table like this... PlaygroundEntities context = new PlaygroundEntities(); Person p = new Person { Status = PersonStatus.Alive, BirthDate = new DateTime(1982,3,18), Name = "Joe Smith" }; context.AddToPeople(p); context.SaveChanges(); When i run this code i get the following error The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.\r\nThe statement has been terminated. So i tried setting the StoreGeneratedPattern to computed... same thing, then identity... same thing. Any ideas?

    Read the article

  • MVC2 DataAnnotations on ViewModel - ModelState.isValid Always Returns true

    - by ScottSEA
    I have an MVC2 Application that uses MVVM pattern. I am trying use Data Annotations to validate form input. In my ThingsController I have two methods: [HttpGet] public ActionResult Index() { return View(); } public ActionResult Details(ThingsViewModel tvm) { if (!ModelState.IsValid) return View(tvm); try { Query q = new Query(tvm.Query); ThingRepository repository = new ThingRepository(q); tvm.Airplanes = repository.All(); return View(tvm); } catch (Exception) { return View(); } } My Details.aspx view is strongly typed to the ThingsViewModel: <%@ Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage<Config.Web.Models.ThingsViewModel>" %> The ViewModel is a class consisting of a IList of returned Thing objects and the Query string (which is submitted on the form) and has the Required data annotation: public class ThingsViewModel { public IList<Thing> Things{ get; set; } [Required(ErrorMessage="You must enter a query")] public string Query { get; set; } } When I run this, and click the submit button on the form without entering a value I get a YSOD with the following error: The model item passed into the dictionary is of type 'Config.Web.Models.ThingsViewModel', but this dictionary requires a model item of type System.Collections.Generic.IEnumerable`1[Config.Domain.Entities.Thing]'. How can I get Data Annotations to work with a ViewModel? I cannot see what I'm missing or where I'm going wrong - the VM was working just fine before I started mucking around with validation.

    Read the article

  • Manual (Dynamic) LINQ subquery using IN clause

    - by immortalali-msn-com
    Hi Everyone, I want to query the DB through LINQ writing manual SQL, my linq method is: var q = db.TableView.Where(sqlAfterWhere); returnValue = q.Count(); this method queries well if the value passed to variable "sqlAfterWhere" is: (this variable is String type) it.Name = 'xyz' but what if i want to use IN clause, using a sub query. (i need to use 'it' before every column name in the above query to work), i cant use 'it' before the sub query columns as its a separate query, so what should i do, if i dont use any thing, and use column names directly it gives error saying " could not be resolved" where is my column names with out 'it' at the begining. So the query not working is: (this is a string passed to the variable above): it.Name IN (SELECT Name FROM TableName WHERE Address LIKE '%SomeAddress%') the errors come out as: Name could not be resolved Address could not be resolved The exact error is: "'Name' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly., near simple identifier, line 6, column 25." Same error for "Address as well if i use 'it.' before these columns it gives error as: "The element type 'Edm.Int32' and the CollectionType 'Transient.collection[Transient.rowtype(GroupID,Edm.Int32(Nullable=True,DefaultValue=))]' are not compatible. The IN expression only supports entity, primitive, and reference types. , near WHERE predicate, line 6, column 14." Thanks for the help

    Read the article

  • Sharepoint Lists.GetListItems Method rowLimit problem

    - by Linda
    In SharePoint I am using the default view of a list. When I use GetListItems method I can pass into it the following: public XmlNode GetListItems ( string listName, string viewName, XmlNode query, XmlNode viewFields, string rowLimit, XmlNode queryOptions, string webID ) I am passing in "" for the viewName and am passing a rowLimit of 1000. By Default view only returns 100 items. 100 Items are still being returned not 1000. Can you use the rowLimit when not specifying a view? Is it possible to bring back 1000 items using the query instead? I do not really want to use a GUID for the viewName as I would have to look it up for each list and perform a big refactor. Update I am now using the guid of the view and my list still returns the incorrect number of items. I know the guid is being used as I sued an incorrect one and it errord out. Any ideas what could be wrong? The code that is being sent to the service is as follows: <GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'> <listName>Media Outlet</listName> <viewName>{2822F0D9-A905-44B5-8913-34E6497F1AAF}</viewName> <query><Query><Where><Eq><FieldRef Name='Outlet_x0020_Type' /><Value Type='Lookup'></Value></Eq></Where><OrderBy><FieldRef Name='Title' /></OrderBy></Query></query> <ViewFields></ViewFields> <RowLimit>10000</RowLimit> <QueryOptions></QueryOptions> <webID></webID> </GetListItems>

    Read the article

  • General type conversion without risking Exceptions

    - by Mongus Pong
    I am working on a control that can take a number of different datatypes (anything that implements IComparable). I need to be able to compare these with another variable passed in. If the main datatype is a DateTime, and I am passed a String, I need to attempt to convert the String to a DateTime to perform a Date comparison. if the String cannot be converted to a DateTime then do a String comparison. So I need a general way to attempt to convert from any type to any type. Easy enough, .Net provides us with the TypeConverter class. Now, the best I can work out to do to determine if the String can be converted to a DateTime is to use exceptions. If the ConvertFrom raises an exception, I know I cant do the conversion and have to do the string comparison. The following is the best I got : string theString = "99/12/2009"; DateTime theDate = new DateTime ( 2009, 11, 1 ); IComparable obj1 = theString as IComparable; IComparable obj2 = theDate as IComparable; try { TypeConverter converter = TypeDescriptor.GetConverter ( obj2.GetType () ); if ( converter.CanConvertFrom ( obj1.GetType () ) ) { Console.WriteLine ( obj2.CompareTo ( converter.ConvertFrom ( obj1 ) ) ); Console.WriteLine ( "Date comparison" ); } } catch ( FormatException ) { Console.WriteLine ( obj1.ToString ().CompareTo ( obj2.ToString () ) ); Console.WriteLine ( "String comparison" ); } Part of our standards at work state that : Exceptions should only be raised when an Exception situation - ie. an error is encountered. But this is not an exceptional situation. I need another way around it. Most variable types have a TryParse method which returns a boolean to allow you to determine if the conversion has succeeded or not. But there is no TryConvert method available to TypeConverter. CanConvertFrom only dermines if it is possible to convert between these types and doesnt consider the actual data to be converted. The IsValid method is also useless. Any ideas? EDIT I cannot use AS and IS. I do not know either data types at compile time. So I dont know what to As and Is to!!! EDIT Ok nailed the bastard. Its not as tidy as Marc Gravells, but it works (I hope). Thanks for the inpiration Marc. Will work on tidying it up when I get the time, but I've got a bit stack of bugfixes that I have to get on with. public static class CleanConverter { /// <summary> /// Stores the cache of all types that can be converted to all types. /// </summary> private static Dictionary<Type, Dictionary<Type, ConversionCache>> _Types = new Dictionary<Type, Dictionary<Type, ConversionCache>> (); /// <summary> /// Try parsing. /// </summary> /// <param name="s"></param> /// <param name="value"></param> /// <returns></returns> public static bool TryParse ( IComparable s, ref IComparable value ) { // First get the cached conversion method. Dictionary<Type, ConversionCache> type1Cache = null; ConversionCache type2Cache = null; if ( !_Types.ContainsKey ( s.GetType () ) ) { type1Cache = new Dictionary<Type, ConversionCache> (); _Types.Add ( s.GetType (), type1Cache ); } else { type1Cache = _Types[s.GetType ()]; } if ( !type1Cache.ContainsKey ( value.GetType () ) ) { // We havent converted this type before, so create a new conversion type2Cache = new ConversionCache ( s.GetType (), value.GetType () ); // Add to the cache type1Cache.Add ( value.GetType (), type2Cache ); } else { type2Cache = type1Cache[value.GetType ()]; } // Attempt the parse return type2Cache.TryParse ( s, ref value ); } /// <summary> /// Stores the method to convert from Type1 to Type2 /// </summary> internal class ConversionCache { internal bool TryParse ( IComparable s, ref IComparable value ) { if ( this._Method != null ) { // Invoke the cached TryParse method. object[] parameters = new object[] { s, value }; bool result = (bool)this._Method.Invoke ( null, parameters); if ( result ) value = parameters[1] as IComparable; return result; } else return false; } private MethodInfo _Method; internal ConversionCache ( Type type1, Type type2 ) { // Use reflection to get the TryParse method from it. this._Method = type2.GetMethod ( "TryParse", new Type[] { type1, type2.MakeByRefType () } ); } } }

    Read the article

  • Sharepoint GetListItems using rowLimit parameter is not limiting the results returned

    - by Linda
    In SharePoint I am using the default view of a list. When I use GetListItems method I can pass into it the following: public XmlNode GetListItems ( string listName, string viewName, XmlNode query, XmlNode viewFields, string rowLimit, XmlNode queryOptions, string webID ) I am passing in "" for the viewName and am passing a rowLimit of 1000. By Default view only returns 100 items. 100 Items are still being returned not 1000. Can you use the rowLimit when not specifying a view? Is it possible to bring back 1000 items using the query instead? I do not really want to use a GUID for the viewName as I would have to look it up for each list and perform a big refactor. Update I am now using the guid of the view and my list still returns the incorrect number of items. I know the guid is being used as I sued an incorrect one and it errord out. Any ideas what could be wrong? The code that is being sent to the service is as follows: <GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'> <listName>Media Outlet</listName> <viewName>{2822F0D9-A905-44B5-8913-34E6497F1AAF}</viewName> <query><Query><Where><Eq><FieldRef Name='Outlet_x0020_Type' /><Value Type='Lookup'></Value></Eq></Where><OrderBy><FieldRef Name='Title' /></OrderBy></Query></query> <ViewFields></ViewFields> <RowLimit>10000</RowLimit> <QueryOptions></QueryOptions> <webID></webID> </GetListItems>

    Read the article

  • LINQ to Entity, using a SQL LIKE operator

    - by Mario
    I have a LINQ to ENTITY query that pulls from a table, but I need to be able to create a "fuzzy" type search. So I need to add a where clause that searches by lastname IF they add the criteria in the search box (Textbox, CAN be blank --- in which case it pulls EVERYTHING). Here is what I have so far: var query = from mem in context.Member orderby mem.LastName, mem.FirstName select new { FirstName = mem.FirstName, LastName = mem.LastName, }; That will pull everything out of the Member table that is in the Entity object. Then I have an addition to the logic: sLastName = formCollection["FuzzyLastName"].ToString(); if (!String.IsNullOrEmpty(sLastName)) query = query.Where(ln => ln.LastName.Contains(sLastName)); The problem is when the search button is pressed, nothing is returned (0 results). I have run the query against the SQL Server that I expect to happen here and it returns 6 results. This is the query I expect: SELECT mem.LastName, mem.FirstName FROM Members mem WHERE mem.LastName = 'xxx' (when xxx is entered into the textbox) Anyone see anything wrong with this?

    Read the article

  • JavaScript and CSS files for ASP.NET MVC 2 EditorTemplate user controls

    - by Zack Peterson
    I'm using an EditorTemplate DateTime.ascx in my ASP.NET MVC 2 project. <%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl<DateTime>" %> <%: Html.TextBox(String.Empty, Model.ToString("M/dd/yyyy h:mm tt")) %> <script type="text/javascript"> $(function () { $('#<%: ViewData.TemplateInfo.GetFullHtmlFieldId(String.Empty) %>').AnyTime_picker({ format: "%c/%d/%Y %l:%i %p" }); }); </script> This uses the Any+Time™ JavaScript library for jQuery by Andrew M. Andrews III. I've added those library files (anytimec.js and anytimec.css) to the <head> section of my master page. Rather than include these JavaScript and Cascading Style Sheet files on every page of my web site, how can I instead include the .js and .css files only on pages that need them--pages that edit a DateTime type value?

    Read the article

  • Django Error: NameError name 'current_datetime' is not defined

    - by Diego
    I'm working through the book "The Definitive Guide to Django" and am stuck on a piece of code. This is the code in my settings.py: ROOT_URLCONF = 'mysite.urls' I have the following code in my urls.py from django.conf.urls.defaults import * from mysite.views import hello, my_homepage_view urlpatterns = patterns('', ('^hello/$', hello), ) urlpatterns = patterns('', ('^time/$', current_datetime), ) And the following is the code in my views.py file: from django.http import HttpResponse import datetime def hello(request): return HttpResponse("Hello World") def current_datetime(request): now = datetime.datetime.now() html = "<html><body>It is now %s.</body></html>" % now return HttpResponse(html) Yet, I get the following error when I test the code in the development server. NameError at /time/ name 'current_datetime' is not defined Can someone help me out here? This really is just a copy-paste from the book. I don't see any mistyping.

    Read the article

  • LINQ-to-SQL: ExecuteQuery(Type, String) populates one field, but not the other

    - by Daniel Schaffer
    I've written an app that I use as an agent to query data from a database and automatically load it into my distributed web cache. I do this by specifying an sql query and a type in a configuration. The code that actually does the querying looks like this: List<Object> result = null; try { result = dc.ExecuteQuery(elementType, entry.Command).OfType<Object>().ToList(); } catch (Exception ex) { HandleException(ex, WebCacheAgentLogEvent.DatabaseExecutionError); continue; } elementType is a System.Type created from the type specified in the configuration (using Type.GetType()), and entry.Command is the SQL query. The specific entity type I'm having an issue with looks like this: public class FooCount { [Column(Name = "foo_id")] public Int32 FooId { get; set; } [Column(Name = "count")] public Int32 Count { get; set; } } The SQL query looks like this: select foo_id as foo_id, sum(count) as [count] from foo_aggregates group by foo_id order by foo_id For some reason, when the query is executed, the "Count" property ends up populated, but not the "FooId" property. I tried running the query myself, and the correct column names are returned, and the column names match up with what I've specified in my mapping attributes. Help!

    Read the article

< Previous Page | 290 291 292 293 294 295 296 297 298 299 300 301  | Next Page >