Hi, I'm writing an application using c# 2005 and Sql Server 2000. I have a table, with a unique constraint and, in the case I am concerned with, I have two users using a form which will (when Save is pressed) update the table. If the table is, say, NAMES( ID int, NAME varchar(20)) and the unique constraint is on NAME, if the first use...
Does anyone know of any work around by which i can save unsigned integers (0 to 4294967295) simply using 4 bytes instead of using 8 bytes and bigint? I know we can create user defined datatypes and create a constraint on them to not allow negative values but that still does not allow me to enter values over 2147483647. I only want to u...
Can anyone tell me if there is an equivalent of SCOPE_IDENTITY() when using GUIDs as a primary key in SQL Server? I don't want to create the GUID first and save as a variable as we're using sequential GUIDs as our primary keys. Any idea on what the best way to retrieve the last inserted GUID primary key. Thanks in advance! ...
I'm using asp.net and sql server. i have 2 tables: Categories and Products. in the products table i have categoryId as FK. what i want to do is: when i delete category from the category table, i want that all the products from that category will be deleted in the products table. how can this be done ( i prefer with store procedure but it...
How do i convert a string of format -> mmddyyyy into datetime in sql server 2008? My target column in 'DateTime' I have tried with Convert and most of the Date style values - I get a 'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.' error message ...
Using SQL Server 2000 query Select id, CONVERT(char(8), CASE WHEN DateAdd(Day, - DateDiff(Day, 0, OutTime), OutTime) > Normal_Outtime THEN Cast(Normal_Outtime AS datetime) ELSE DateAdd(Day, - DateDiff(Day, 0, OutTime), OutTime) END - CASE WHEN DateAdd(Day, - DateDiff(Day, 0, InTime), InTime) < Normal_Intime THEN Cast(Normal_Intime AS d...
I'm trying to acheve the above in reporting services. Current month is fine but how can i add prev month to the table? The data is currently stored in a different dataset that has exactly the same fields. Using reporting services 2005. ...
We know that Linq-To-Sql InsertAllOnSubmit sucks in terms of performance, but using SqlBulkCopy for mass insertions requires some coding. Have anyone found any code/library that uses SqlBulkCopy in a InsertAllOnSubmit alternative implementation as simple to use as the original? ...
would I export .iif file from the reporing services sql 2005 ? .iif means INTUIT INTERCHANGE FORMAT use in the quickbooks accounting software for export/import data in that s/w. ...
First of all, I'm working in SQL Server 2000 (although prayers have been made to the Federal Stimulus Money gods for an upgrade to 2008.) I have a listing of students and educational events. I can successfully get the most recent event, except that it may be a combination of events: StudentID Event Date 1 Triennia...
We have a 16 processor SQL Server 2005 cluster. When looking at CPU usage data we see that most of the time only 4 of the 16 processors are ever utilized. However, in periods of high load, occasionally a 5th and 6th processor will be used, although never anywhere near the utilization of the other 4. I'm concerned that in periods of tr...
In responses to this question, KM said if you are on or above SQL Server 2005, you can use IFs to have multiple queries in the same procedure and each will have an query plan saved for it (equiv to a procedure for each on older versions), see the article in my answer or this link to proper section: sommarskog.se/dyn-search-2005.html#...
When I try to use the Invoke-Sqlcmd cmdlet from SQL Server 2008 to execute a query that contains scripting variables, ex. $(MyVar), I receive the following exception: Invoke-Sqlcmd : Object reference not set to an instance of an object. Here's the code I'm trying to run (which is copy/paste from the Books Online example with only t...
I have a backup like this: Select * Into BACKUP From ORIGINAL Then I need to restore some data from that backup, how do I update from that table, I can't Insert Into nor Drop Table because of the Foreign Keys ...
Hello, I'm getting the following error when I run this code: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ')'. Any ideas on how to fix this? Any help would be appreciated. Declare @Month int Declare @Year int Declare @newYear int Declare @EndYearMonth int Declare @PreviousMonthInt int Declare @OldYear int Declare @P...
First of all I'm not asking if NOLOCK should or should not be used. Let's get past that. I guess the question comes down to how sql server writes data? Is an entire row written at once or does it write it a column at a time? I'm asking because the NOLOCK hint is being considered. A dirty read is fine as long as the entire row is retu...
We have updated our servers with Windows 2008 Server, but we are still using SQL Server 2005 in our production software. The problem comes in one part of the system where we store times as datetimes. Since ever, storing a time in a datetime column stored the base date as the date. It is 1900-01-01 in SQL Server 2005. Back then, IIS under...
I have a C# application that calls a stored procedure that produces an xml result (using FOR XML Explicit) that I want to save to disk. Doing some research I have found the following method: var data = new DataSet(); XmlReader reader = cmd.ExecuteXmlReader(); data.ReadXmlSchema(reader); data.ReadXml(reader, XmlReadMode.Fragment); data....
We have a table with 17Mil rows containing product attributes, let's say they're: brandID, sizeID, colorID, price, shapeID And we need to query for aggregates by brand and size. Currently we query and filter this data by doing something like this: select brandID, sizeID, count(*) from table where colorID in (1,2,3) and price=10...
I have a large report I am running on sql server. It takes several minutes to run. I don't want users clicking run twice. Since i wrap the whole procedure in a transaction, how do I check to see if the table is locked by a transaction? If so I would want to return an error message saying "report generating, please try again in a few min...