I'm working on my first ASP.NET MVC app, and I'm running into a little confusion about creating/updating certain data. I have a database table User, a LinqToSql-generated partial class User, and my own custom partial class User. I'm using [Bind(Exclude = "Id, InsertDateTime, UpdateDateTime")] on my version of User because I don't want ...
Hi I want to copy the values of one node to another in sql server(2005 and 2008). e.g if one of the xml data is as below <Data> <Name></Name> <ShortName>Joe</ShortName> </Data> the resulting xml should be <Data> <Name>Joe</Name> <ShortName>Joe</ShortName> the update statement should affect all the rows in the table appreciate a...
Say I've got some throw-away sample procedure that looks like this: CREATE procedure simpletestproc_prc ( @companyId int, @objectid float = 5.678, @personname varchar(255) = 'hello world' ) as select @companyId + 10, @objectid, @personname I can use the below query to get the types and names of all...
I have a column in ntext which holds large unicode strings longer than 4000 chars in length. I need to update/modify the data of the rows of the column in sql but I have no clue how to do so. I have tried nvarchar(max) as a buffer but it truncates the data into 4000 chars. Could anyone help me give me a hint or an idea or a workround so...
Hello, This question is linked to my previous one ( posted as an anonymous user - now I have an account ) and, before I begin, I would like to give the credit to Rob Farley for providing the right indexing schema. But the problem is not the indexing schema. It's the Query Optimizer ! The query : SELECT s.ID_i , s.ShortName_v ...
Hello, I found some table valued function for splitting string in sp and getting values in columns, but when data is huge these functions are became slow. so if anybody has a good function for splitting values separated by commas into rows of a table... please provide me. The link where I found some functions: Click Here ...
I'm using sql Server 2008. following dataPool: PK Col1 Col2 1 SomeValue1 DataToTake1 2 SomeValue1 3 SomeValue1 4 SomeValue1 5 SomeValue2 DataToTake2 6 SomeValue2 ... i want to insert DataToTake1 into Col2 of records with PK 2, 3 and 4 and DataToTake2 into Col2 of record with PK 6. to make it more obvious: record...
Can I return UNIQUEIDENTIFIER from a stored procedure using the RETURN statement or is it only by using the OUTPUT statement? i.e to return the PersonID UNIQUEIDENTIFIER: CREATE PROCEDURE CreatePerson @Name NVARCHAR(255), @Desc TEXT AS DECLARE @Count INT DECLARE @JobFileGUID UNIQUEIDENTIFIER -- Check if job exists? SET @Coun...
my system support modules like calendar, Leads manager, Contact Manager the users of the system can register in my site and subscribe in any of this modules what is the database schema needed to handle this situation ? and how i will handle it by the code ? ...
We're running SQL Server Reporting Services 2005 (SSRS) with a custom security extension to integrate with our ASP.NET's Forms Authentication, and are seeing a peculiar problem on one environment only. The SSRS portal itself works fine - can see list of reports and execute them ok. The problem is when running the reports from our custo...
I would like to know which values are null in datatable in c# that is returned from ExecuteDataTable of SqlHelper class. string select = "select * from testTable"; string val=""; DataTable dt=dbcon.ExecuteDataTable(select); foreach (DataRow dr in dt.Rows) { foreach (DataColumn dc in dt.Columns ) { if(dr[dc].Equals (null)...
In our SQL Server 2005 database (tested using Management Studio with DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS), the following statement is fast (~0.2s compile time, ~0.1s execution time): SELECT ... FROM ... WHERE a = 1 AND b = '' ... The following statement, however, is slow (~0.2s compile time, 7-11s execution time): exec sp_e...
Please I need Tutorials on how to connect/manipulate sql server with vb.net. ...
Hi I have a very large database (40 gig) and have run the procedure sp_space_used and found that there is 10 gig of unallocated space. Obviously this is a lot and the .mdf file is taking up most of the disk. I have looked into running DBCC SHRINKDATABASE (db, TRUNCATEONLY); Do I also need to shrink the transaction log or will s...
Currently have this to get a value from the registry in TSQL. However, I need to get the DigitalProductId and it does not return the value required. I think its stored as a binary in the registry. Any ideas? DECLARE @retvalue int, @data varchar(500) EXECUTE @retvalue = master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Micr...
The question is quite simple: What can I do to make sure that the SQL Server query optimizer has all the information it needs to choose the "best" query plan? The background of this question is that recently we've run into more and more cases where SQL Server chooses a bad query plan, i.e., cases, where adding query hints, join hints or...
I have a table that looks like this: CREATE TABLE [dbo].[SomeTable]( [Guid] [uniqueidentifier] NOT NULL, [Column1] [int] NOT NULL, [Column2] [datetime] NOT NULL, [Column3] [bit] NOT NULL, [Column4] [smallint] NOT NULL, [Column5] [uniqueidentifier] NULL, [Column6] [varchar](100) NULL, [Column7] [datetime] ...
This has been bugging me for a while and I'm hoping that one of the SQL Server experts can shed some light on it. The question is: When you index a SQL Server column containing a UDT (CLR type), how does SQL Server determine what index operation to perform for a given query? Specifically I am thinking of the hierarchyid (AKA SqlHierar...
i'll use a concrete, but hypothetical, example. Each Order normally has only one line item: Orders: OrderGUID OrderNumber ========= ============ {FFB2...} STL-7442-1 {3EC6...} MPT-9931-8A LineItems: LineItemGUID Order ID Quantity Description ============ ======== ======== ================================= {098...
I would like to take the average of the time difference from Table1 below. The values are not consecutive and occasionally the time value is repeated, so I need to 1) sort by time, 2) discard non-unique values, 3) perform the time difference (in milliseconds), then 4) average the resulting time difference values. Further I'd like to 5)...