Search Results

Search found 31546 results on 1262 pages for 'sql white papers'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 249/1262 | < Previous Page | 245 246 247 248 249 250 251 252 253 254 255 256  | Next Page >

  • SQL Azure table size

    - by user224564
    In mssql2005 when i want to get size of table in MBs, i use EXEC sp_spaceused 'table'. Is there any way to get space used by particular table in SQL Azure using some query or API?

    Read the article

  • LINQ to SQL SOUNDEX - possible?

    - by Steve Hayes
    Hello, I have done a little bit of research on this and looked through a few articles both here on StackOverflow as well as some blog posts, but haven't found an exact answer. I also read that it is possible to do it using the 4.0 framework, but have yet to find any supporting evidence. So my question, is it possible to perform SOUNDEX via a LINQ to SQL Query?

    Read the article

  • OrderBy and Distinct using LINQ-to-Entities

    - by BlueRaja
    Here is my LINQ query: (from o in entities.MyTable orderby o.MyColumn select o.MyColumn).Distinct(); Here is the result: {"a", "c", "b", "d"} Here is the generated SQL: SELECT [Distinct1].[MyColumn] AS [MyColumn] FROM ( SELECT DISTINCT [Extent1].[MyColumn] AS [MyColumn] FROM [dbo].[MyTable] AS [Extent1] ) AS [Distinct1] Is this a bug? Where's my ordering, damnit?

    Read the article

  • NullReferenceException when calling InsertOnSubmit in Linq to Sql.

    - by Charlie
    I'm trying to insert a new object into my database using LINQ to SQL but get a NullReferenceException when I call InsertOnSubmit() in the code snippet below. I'm passing in a derived class called FileUploadAudit, and all properties on the object are set. public void Save(Audit audit) { try { using (ULNDataClassesDataContext dataContext = this.Connection.GetContext()) { if (audit.AuditID > 0) { throw new RepositoryException(RepositoryExceptionCode.EntityAlreadyExists, string.Format("An audit entry with ID {0} already exists and cannot be updated.", audit.AuditID)); } dataContext.Audits.InsertOnSubmit(audit); dataContext.SubmitChanges(); } } catch (Exception ex) { if (ObjectFactory.GetInstance<IExceptionHandler>().HandleException(ex)) { throw; } } } Here's the stack trace: at System.Data.Linq.Table`1.InsertOnSubmit(TEntity entity) at XXXX.XXXX.Repository.AuditRepository.Save(Audit audit) C:\XXXX\AuditRepository.cs:line 25" I've added to the Audit class like this: public partial class Audit { public Audit(string message, ULNComponent component) : this() { this.Message = message; this.DateTimeRecorded = DateTime.Now; this.SetComponent(component); this.ServerName = Environment.MachineName; } public bool IsError { get; set; } public void SetComponent(ULNComponent component) { this.Component = Enum.GetName(typeof(ULNComponent), component); } } And the derived FileUploadAudit looks like this: public class FileUploadAudit : Audit { public FileUploadAudit(string message, ULNComponent component, Guid fileGuid, string originalFilename, string physicalFilename, HttpPostedFileBase postedFile) : base(message, component) { this.FileGuid = fileGuid; this.OriginalFilename = originalFilename; this.PhysicalFileName = physicalFilename; this.PostedFile = postedFile; this.ValidationErrors = new List<string>(); } public Guid FileGuid { get; set; } public string OriginalFilename { get; set; } public string PhysicalFileName { get; set; } public HttpPostedFileBase PostedFile { get; set; } public IList<string> ValidationErrors { get; set; } } Any ideas what the problem is? The closest question I could find to mine is here but my partial Audit class is calling the parameterless constructor in the generated code, and I still get the problem. UPDATE: This problem only occurs when I pass in the derived FileUploadAudit class, the Audit class works fine. The Audit class is generated as a linq to sql class and there are no Properties mapped to database fields in the derived class.

    Read the article

  • Linq 2 Sql DateTime format to string yyyy-MM-dd

    - by Bogdan Maxim
    Basically, i need the equivalent of T-SQL CONVERT(NVARCHAR(10), datevalue, 126) I've tried: from t in ctx.table select t.Date.ToString("yyyy-MM-dd") but it throws not supported exception from t in ctx.table select "" + t.Date.Year + "-" + t.Date.Month + "-" + t.Date.Day but i don't think it's an usable solution, because i might need to be able to change the format. The only option I see is to use Convert.ToString(t.Date, FormatProvider), but i need a format provider, and I'm not sure it works either.

    Read the article

  • Converting sql query to EF query - nested query in from

    - by vdh_ant
    Hey guys Just wondering how the following sql query would look in linq for Entity Framework... SELECT KPI.* FROM KeyPerformanceIndicator KPI INNER JOIN ( SELECT SPP.SportProgramPlanId FROM SportProgramPlan PSPP INNER JOIN SportProgramPlan ASPP ON (PSPP.SportProgramPlanId = @SportProgramPlanId AND PSPP.StartDate >= ASPP.StartDate AND PSPP.EndDate <= ASPP.EndDate ) AS SPP ON KPI.SportProgramPlanId = SPP.SportProgramPlanId Cheers Anthony

    Read the article

  • Unit of Measurement for Duration Column in Sql Profiler

    - by Mubashar Ahmad
    What is the Unit of Duration column in SQL Profiler? i thought it is milliseconds but in following profiler row i found it contradicting with start and end time spid=163 duration=11310646 starttime=2010-04-06 17:45:24.480 endtime=2010-04-06 17:45:35.790 reads=152 writes=2 cpu=16 eventclass=12 textdata= DELETE FROM dbo.[Icon] WHERE Id = 20087

    Read the article

  • C# export to excel from sql server

    - by Manish Gupta
    In my C# windows application, I am exporting sql server data to excel on remote drive. But it is too slow. However, if I export data to excel in the local drive, it is fast. How can I increase the time if I want to export data to remote drive? Thanks in advance...

    Read the article

  • T-SQL: @@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity

    - by Terrapin
    I have seen various methods used when retrieving the value of a primary key identity field after insert. declare @t table ( id int identity primary key, somecol datetime default getdate() ) insert into @t default values select SCOPE_IDENTITY() --returns 1 select @@IDENTITY --returns 1 Returning a table of identities following insert: Create Table #Testing ( id int identity, somedate datetime default getdate() ) insert into #Testing output inserted.* default values What method is proper or better? Is the OUTPUT method scope-safe? The second code snippet was borrowed from SQL in the Wild

    Read the article

  • Beware Sneaky Reads with Unique Indexes

    - by Paul White NZ
    A few days ago, Sandra Mueller (twitter | blog) asked a question using twitter’s #sqlhelp hash tag: “Might SQL Server retrieve (out-of-row) LOB data from a table, even if the column isn’t referenced in the query?” Leaving aside trivial cases (like selecting a computed column that does reference the LOB data), one might be tempted to say that no, SQL Server does not read data you haven’t asked for.  In general, that’s quite correct; however there are cases where SQL Server might sneakily retrieve a LOB column… Example Table Here’s a T-SQL script to create that table and populate it with 1,000 rows: CREATE TABLE dbo.LOBtest ( pk INTEGER IDENTITY NOT NULL, some_value INTEGER NULL, lob_data VARCHAR(MAX) NULL, another_column CHAR(5) NULL, CONSTRAINT [PK dbo.LOBtest pk] PRIMARY KEY CLUSTERED (pk ASC) ); GO DECLARE @Data VARCHAR(MAX); SET @Data = REPLICATE(CONVERT(VARCHAR(MAX), 'x'), 65540);   WITH Numbers (n) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM master.sys.columns C1, master.sys.columns C2 ) INSERT LOBtest WITH (TABLOCKX) ( some_value, lob_data ) SELECT TOP (1000) N.n, @Data FROM Numbers N WHERE N.n <= 1000; Test 1: A Simple Update Let’s run a query to subtract one from every value in the some_value column: UPDATE dbo.LOBtest WITH (TABLOCKX) SET some_value = some_value - 1; As you might expect, modifying this integer column in 1,000 rows doesn’t take very long, or use many resources.  The STATITICS IO and TIME output shows a total of 9 logical reads, and 25ms elapsed time.  The query plan is also very simple: Looking at the Clustered Index Scan, we can see that SQL Server only retrieves the pk and some_value columns during the scan: The pk column is needed by the Clustered Index Update operator to uniquely identify the row that is being changed.  The some_value column is used by the Compute Scalar to calculate the new value.  (In case you are wondering what the Top operator is for, it is used to enforce SET ROWCOUNT). Test 2: Simple Update with an Index Now let’s create a nonclustered index keyed on the some_value column, with lob_data as an included column: CREATE NONCLUSTERED INDEX [IX dbo.LOBtest some_value (lob_data)] ON dbo.LOBtest (some_value) INCLUDE ( lob_data ) WITH ( FILLFACTOR = 100, MAXDOP = 1, SORT_IN_TEMPDB = ON ); This is not a useful index for our simple update query; imagine that someone else created it for a different purpose.  Let’s run our update query again: UPDATE dbo.LOBtest WITH (TABLOCKX) SET some_value = some_value - 1; We find that it now requires 4,014 logical reads and the elapsed query time has increased to around 100ms.  The extra logical reads (4 per row) are an expected consequence of maintaining the nonclustered index. The query plan is very similar to before (click to enlarge): The Clustered Index Update operator picks up the extra work of maintaining the nonclustered index. The new Compute Scalar operators detect whether the value in the some_value column has actually been changed by the update.  SQL Server may be able to skip maintaining the nonclustered index if the value hasn’t changed (see my previous post on non-updating updates for details).  Our simple query does change the value of some_data in every row, so this optimization doesn’t add any value in this specific case. The output list of columns from the Clustered Index Scan hasn’t changed from the one shown previously: SQL Server still just reads the pk and some_data columns.  Cool. Overall then, adding the nonclustered index hasn’t had any startling effects, and the LOB column data still isn’t being read from the table.  Let’s see what happens if we make the nonclustered index unique. Test 3: Simple Update with a Unique Index Here’s the script to create a new unique index, and drop the old one: CREATE UNIQUE NONCLUSTERED INDEX [UQ dbo.LOBtest some_value (lob_data)] ON dbo.LOBtest (some_value) INCLUDE ( lob_data ) WITH ( FILLFACTOR = 100, MAXDOP = 1, SORT_IN_TEMPDB = ON ); GO DROP INDEX [IX dbo.LOBtest some_value (lob_data)] ON dbo.LOBtest; Remember that SQL Server only enforces uniqueness on index keys (the some_data column).  The lob_data column is simply stored at the leaf-level of the non-clustered index.  With that in mind, we might expect this change to make very little difference.  Let’s see: UPDATE dbo.LOBtest WITH (TABLOCKX) SET some_value = some_value - 1; Whoa!  Now look at the elapsed time and logical reads: Scan count 1, logical reads 2016, physical reads 0, read-ahead reads 0, lob logical reads 36015, lob physical reads 0, lob read-ahead reads 15992.   CPU time = 172 ms, elapsed time = 16172 ms. Even with all the data and index pages in memory, the query took over 16 seconds to update just 1,000 rows, performing over 52,000 LOB logical reads (nearly 16,000 of those using read-ahead). Why on earth is SQL Server reading LOB data in a query that only updates a single integer column? The Query Plan The query plan for test 3 looks a bit more complex than before: In fact, the bottom level is exactly the same as we saw with the non-unique index.  The top level has heaps of new stuff though, which I’ll come to in a moment. You might be expecting to find that the Clustered Index Scan is now reading the lob_data column (for some reason).  After all, we need to explain where all the LOB logical reads are coming from.  Sadly, when we look at the properties of the Clustered Index Scan, we see exactly the same as before: SQL Server is still only reading the pk and some_value columns – so what’s doing the LOB reads? Updates that Sneakily Read Data We have to go as far as the Clustered Index Update operator before we see LOB data in the output list: [Expr1020] is a bit flag added by an earlier Compute Scalar.  It is set true if the some_value column has not been changed (part of the non-updating updates optimization I mentioned earlier). The Clustered Index Update operator adds two new columns: the lob_data column, and some_value_OLD.  The some_value_OLD column, as the name suggests, is the pre-update value of the some_value column.  At this point, the clustered index has already been updated with the new value, but we haven’t touched the nonclustered index yet. An interesting observation here is that the Clustered Index Update operator can read a column into the data flow as part of its update operation.  SQL Server could have read the LOB data as part of the initial Clustered Index Scan, but that would mean carrying the data through all the operations that occur prior to the Clustered Index Update.  The server knows it will have to go back to the clustered index row to update it, so it delays reading the LOB data until then.  Sneaky! Why the LOB Data Is Needed This is all very interesting (I hope), but why is SQL Server reading the LOB data?  For that matter, why does it need to pass the pre-update value of the some_value column out of the Clustered Index Update? The answer relates to the top row of the query plan for test 3.  I’ll reproduce it here for convenience: Notice that this is a wide (per-index) update plan.  SQL Server used a narrow (per-row) update plan in test 2, where the Clustered Index Update took care of maintaining the nonclustered index too.  I’ll talk more about this difference shortly. The Split/Sort/Collapse combination is an optimization, which aims to make per-index update plans more efficient.  It does this by breaking each update into a delete/insert pair, reordering the operations, removing any redundant operations, and finally applying the net effect of all the changes to the nonclustered index. Imagine we had a unique index which currently holds three rows with the values 1, 2, and 3.  If we run a query that adds 1 to each row value, we would end up with values 2, 3, and 4.  The net effect of all the changes is the same as if we simply deleted the value 1, and added a new value 4. By applying net changes, SQL Server can also avoid false unique-key violations.  If we tried to immediately update the value 1 to a 2, it would conflict with the existing value 2 (which would soon be updated to 3 of course) and the query would fail.  You might argue that SQL Server could avoid the uniqueness violation by starting with the highest value (3) and working down.  That’s fine, but it’s not possible to generalize this logic to work with every possible update query. SQL Server has to use a wide update plan if it sees any risk of false uniqueness violations.  It’s worth noting that the logic SQL Server uses to detect whether these violations are possible has definite limits.  As a result, you will often receive a wide update plan, even when you can see that no violations are possible. Another benefit of this optimization is that it includes a sort on the index key as part of its work.  Processing the index changes in index key order promotes sequential I/O against the nonclustered index. A side-effect of all this is that the net changes might include one or more inserts.  In order to insert a new row in the index, SQL Server obviously needs all the columns – the key column and the included LOB column.  This is the reason SQL Server reads the LOB data as part of the Clustered Index Update. In addition, the some_value_OLD column is required by the Split operator (it turns updates into delete/insert pairs).  In order to generate the correct index key delete operation, it needs the old key value. The irony is that in this case the Split/Sort/Collapse optimization is anything but.  Reading all that LOB data is extremely expensive, so it is sad that the current version of SQL Server has no way to avoid it. Finally, for completeness, I should mention that the Filter operator is there to filter out the non-updating updates. Beating the Set-Based Update with a Cursor One situation where SQL Server can see that false unique-key violations aren’t possible is where it can guarantee that only one row is being updated.  Armed with this knowledge, we can write a cursor (or the WHILE-loop equivalent) that updates one row at a time, and so avoids reading the LOB data: SET NOCOUNT ON; SET STATISTICS XML, IO, TIME OFF;   DECLARE @PK INTEGER, @StartTime DATETIME; SET @StartTime = GETUTCDATE();   DECLARE curUpdate CURSOR LOCAL FORWARD_ONLY KEYSET SCROLL_LOCKS FOR SELECT L.pk FROM LOBtest L ORDER BY L.pk ASC;   OPEN curUpdate;   WHILE (1 = 1) BEGIN FETCH NEXT FROM curUpdate INTO @PK;   IF @@FETCH_STATUS = -1 BREAK; IF @@FETCH_STATUS = -2 CONTINUE;   UPDATE dbo.LOBtest SET some_value = some_value - 1 WHERE CURRENT OF curUpdate; END;   CLOSE curUpdate; DEALLOCATE curUpdate;   SELECT DATEDIFF(MILLISECOND, @StartTime, GETUTCDATE()); That completes the update in 1280 milliseconds (remember test 3 took over 16 seconds!) I used the WHERE CURRENT OF syntax there and a KEYSET cursor, just for the fun of it.  One could just as well use a WHERE clause that specified the primary key value instead. Clustered Indexes A clustered index is the ultimate index with included columns: all non-key columns are included columns in a clustered index.  Let’s re-create the test table and data with an updatable primary key, and without any non-clustered indexes: IF OBJECT_ID(N'dbo.LOBtest', N'U') IS NOT NULL DROP TABLE dbo.LOBtest; GO CREATE TABLE dbo.LOBtest ( pk INTEGER NOT NULL, some_value INTEGER NULL, lob_data VARCHAR(MAX) NULL, another_column CHAR(5) NULL, CONSTRAINT [PK dbo.LOBtest pk] PRIMARY KEY CLUSTERED (pk ASC) ); GO DECLARE @Data VARCHAR(MAX); SET @Data = REPLICATE(CONVERT(VARCHAR(MAX), 'x'), 65540);   WITH Numbers (n) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM master.sys.columns C1, master.sys.columns C2 ) INSERT LOBtest WITH (TABLOCKX) ( pk, some_value, lob_data ) SELECT TOP (1000) N.n, N.n, @Data FROM Numbers N WHERE N.n <= 1000; Now here’s a query to modify the cluster keys: UPDATE dbo.LOBtest SET pk = pk + 1; The query plan is: As you can see, the Split/Sort/Collapse optimization is present, and we also gain an Eager Table Spool, for Halloween protection.  In addition, SQL Server now has no choice but to read the LOB data in the Clustered Index Scan: The performance is not great, as you might expect (even though there is no non-clustered index to maintain): Table 'LOBtest'. Scan count 1, logical reads 2011, physical reads 0, read-ahead reads 0, lob logical reads 36015, lob physical reads 0, lob read-ahead reads 15992.   Table 'Worktable'. Scan count 1, logical reads 2040, physical reads 0, read-ahead reads 0, lob logical reads 34000, lob physical reads 0, lob read-ahead reads 8000.   SQL Server Execution Times: CPU time = 483 ms, elapsed time = 17884 ms. Notice how the LOB data is read twice: once from the Clustered Index Scan, and again from the work table in tempdb used by the Eager Spool. If you try the same test with a non-unique clustered index (rather than a primary key), you’ll get a much more efficient plan that just passes the cluster key (including uniqueifier) around (no LOB data or other non-key columns): A unique non-clustered index (on a heap) works well too: Both those queries complete in a few tens of milliseconds, with no LOB reads, and just a few thousand logical reads.  (In fact the heap is rather more efficient). There are lots more fun combinations to try that I don’t have space for here. Final Thoughts The behaviour shown in this post is not limited to LOB data by any means.  If the conditions are met, any unique index that has included columns can produce similar behaviour – something to bear in mind when adding large INCLUDE columns to achieve covering queries, perhaps. Paul White Email: [email protected] Twitter: @PaulWhiteNZ

    Read the article

  • Concatenating Date Values - SQL Injection

    - by Kyle Rozendo
    Hi All, We currently receive parameters of values as VARCHAR's, and then build a date from them. I am wanting to confirm that the method would stop the possibility of SQL injection from this statement: select CONVERT(datetime, '2010' + '-' + '02' + '-' + '21' + ' ' + '15:11:38.990') Another note is that the actual parameters being passed through to the stored proc are length bound at (4, 2, 2, 10, 12) in correspondence to the above. Thanks a ton, Kyle

    Read the article

  • Linq to SQL - Invalid attempt to call FieldCount when reader is closed

    - by Justin
    Hey, Has anyone seen this error before when making a Linq to SQL call? Invalid attempt to call FieldCount when reader is closed Here's the code: public static MM GetDeviceConfiguration(long ipAddress) { string sprocName = ConfigurationBL.ReadSproc("ReadDeviceConfiguration"); return db.ExecuteQuery<MM>("exec {0} @IPAddressNumber = {1}", sprocName, ipAddress).FirstOrDefault(); } Thanks, Justin

    Read the article

  • TransactionScope() in Sql Azure

    - by Rick Make
    Does Sql Azure support using TransactionScope() when performing inserts? Below is a code snippet of what I am trying to do. using (var tx = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions() { IsolationLevel = IsolationLevel.ReadCommitted })) { using (var db = MyDataContext.GetDataContext()) { try { MyObject myObject = new MyObject() { SomeString = "Monday" }; db.MyObjects.InsertOnSubmit(myObject); db.SubmitChanges(); tx.Complete(); } catch (Exception e) { } } }

    Read the article

  • Extract words from sentence(s) using TSQL(SQL SERVER 2005) [ SET BASED SOLUTION]

    - by Newbie
    I have the following input. INPUT: TableA ID Sentences --- ---------- 1 I am a student 2 Have a nice time guys! What I need to do is to extract the words from the sentence(s) and insert each individual word in another table OUTPUT: SentenceID WordOccurance Word ---------- ------------ ----- 1 1 I 1 2 am 1 3 a 1 4 student 2 1 Have 2 2 a 2 3 nice 2 4 time 2 5 guys! I am using SQL SERVER 2005. I am looking for a set based solution. Thanks

    Read the article

  • Windows Scheduler OR SQL Server Job for sending out digest e-mails

    - by wgpubs
    Will be sending out e-mails from an application on a scheduled basis. I have an EmailController in my ASP.NET MVC application with action methods, one for each kind of notification/e-mail, that will need to be called at different times during the week. Question: Is Windows Scheduler (running on a Server 2008 box) any better or worse than scheduling this via a SQL Server job? And why? Thanks

    Read the article

  • SQL Like keyword in Dynamic Linq

    - by Erwin
    Hi fellow programmer I want to use SQL's Like keyword in dynamic LINQ. The query that I want to make is like this select * from table_a where column_a like '%search%' Where the column_a can be dynamically changed to other column etc In this dynamic LINQ var result = db.table_a.Where( a=> (a.column_a.Contains("search")) ); But the column can't be dynamically changed , only the search key can How do we create a dynamic LINQ like var result = db.table_a.Where("column_a == \"search\""); That we can change the column and the search key dynamically

    Read the article

  • T/SQL Efficiency and Order of Execution

    - by Kyle Rozendo
    Hi All, In regards to the order of execution of statements in SQL, is there any difference between the following performance wise? SELECT * FROM Persons WHERE UserType = 'Manager' AND LastName IN ('Hansen','Pettersen') And: SELECT * FROM Persons WHERE LastName IN ('Hansen','Pettersen') AND UserType = 'Manager' If there is any difference, is there perhaps a link etc. that you may have where one can learn more about this? Thanks a ton, Kyle

    Read the article

  • SQL Database Mirroring and your web application

    - by Khou
    You have two servers when you perform a SQL Server database mirroring You have 1 primary database and 1 mirror database Do you need to make any changes to web application to tell it that your using database mirroring? If not how does your web application know which database to use when the primary database fails?

    Read the article

  • How do I drop SQL Databases? sp_delete_database_backuphistory woes

    - by rlb.usa
    I want to delete some SQL Databases on my server, but I'm having problems. My login has the roles: public dbcreator serveradmin When I right click the database and hit Delete, it says that Delete backup history failed for server 'MYSERVER' (Microsoft.SqlServer.Smo) Additional Information: The EXECUTE permission was denied on the object 'sp_delete_database_backuphistory' How do I delete these databases?

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 245 246 247 248 249 250 251 252 253 254 255 256  | Next Page >