I'm working with a legacy application with surprise surprise, next to no useful documentation on naming convensions or over all data structure. There are similarly named tables in the database with one of them ending in HX. Does this hold any significance in anyones database experience? The data seems to be replicated so I would assume...
SELECT "2 0 Analytical (Testing Phase)" FROM "dbo"."Lab Occurrence Form" WHERE (("Occurrence Date" BETWEEN @P1 AND @P2 ) AND ("2 0 Analytical (Testing Phase)" LIKE ''%2.%'' ) ) ', N'@P1 datetime,@P2 datetime','2010-04-30 00:00:00','2010-04-01 00:00:00' is this the correct format to return records between a certain datetime?...
How can you generate a unique value for a field that matches a concatenation of certain fields and a random number i.e. First Name: Jim Last Name: Jones Field Value: jimjones0345 obviously there's a need to ensure that this value was not populated before. How would one go about this? ...
I have a Master and Detail table, the Detail linking to the Master record on a FK reference. I need to display all the data from the Master table, and the corresponding number of details for each record, i.e. MASTER TABLE ID Name Age 1 John 15 2 Jane 14 3 Joe 15 DETAIL MasterID Subjects 1 Trigonometry 1 Chemistr...
Here's a table with a computed column in SQL Server 2005: CREATE TABLE footable AS [identifier] nvarchar(255) NOT NULL, [result] AS CASE WHEN [identifier] like '%\[%]' escape '\' THEN LEFT( [identifier], CHARINDEX('[',[identifier]) - 1 ) END Here's what I'm expecting: identifier result ======================...
I have a huge table with the following format: DATETIME NUMBER -------------------------------------- 2009-03-31 16:05:52.000 2453651622 2009-03-31 16:16:12.000 30206080 2009-03-31 16:16:16.000 16890039 2009-03-31 16:16:28.000 2452039696 2009-03-31 16:16:33.000 140851934 2009-03-31 16:16:51.000 245312...
Hi, is there a way in SQL server that i can count the items 90 days prior to the inception date and the items 90 days after the inception date. Example: select site, count(*) from mytable where date >=10/1/2009' and date <'12/30/2009' group by site 90 days before - after inception date. prior to inception date = 7/3/2009. inceptio...
Here (and not only there) people are using the following code to handle an error from sp_xml_preparedocument EXEC @err = sp_xml_preparedocument @hdoc OUTPUT, @xmlDoc SELECT @err = @@error + coalesce(@err, 4711) Are there any reasons for this? And what does 4711 mean in this context? ...
I would like to solve this issue avoiding to use cursors (FETCH). Here comes the problem... 1st Table/quantity ------------------ periodid periodstart periodend quantity 1 2010/10/01 2010/10/15 5 2st Table/sold items ----------------------- periodid periodstart periodend solditems 14343 2010/10/05 2010/10/06 2 Now...
I need to group by ,even if is there a difference in values for a column by converting to a text value and needs that text value to be included in select list How is this possibe? SELECT col1,col2 FROM ( SELECT col1,col2 FROM table1 UNION ALL SELECT col1,col2 FROM table2 UNION ALL SE...
Hello, I am working with a database schema that I cannot change - and I need to run a bulk update. I am moving data between source and target databases, with the same schema. I know this is awful, but it is what I am working with right now. The schema is as follows: Car CarType1 CarType2 CarType3 CarType4 What I am trying to do - is...
I am using trigger to trace DDL changes but when I change column name from the SQL Server Management Studio the trigger is not working? create TRIGGER trgLogDDLEvent ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS FOR AS DECLARE @data XML SET @data = EVENTDATA() IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') <> 'CREATE_ST...
Hi, I have a stored procedure that has a BEGIN TRANSACTION and COMMIT TRANSACTION statement. Within the transaction is a select query WITH(XLOCK, ROWLOCK). The transaction can potentially fail due to some calculations that cause an arithmetic overflow error if out of bounds values are supplied. This error would happen before any inser...
I'd like to do this but I know this isn't the right syntax: INSERT INTO TBL1 SELECT Col1 FROM TBL2 WHERE Col1.endswith('s') ...
Hi, i have a simple table "MyTable" with single colum "id" which type is uniqueidetifier and rowguid is set to true. I insert new values like this INSERT INTO MyTable DEFAULT VALUES how to get inserted by server guid ? Best Regards, Iordan ...
I have a table with records for each zip code in the united states. For the purposes of displaying on a map, I need to select X random records per state. How would I go about doing this? ...
I have a (hopefully) quick SQL question that is driving me nuts and I have not been able to locate an answer anywhere. I have the following SQL trigger: DECLARE @ABCOwnerGroup varchar(30) DECLARE @DEFOwnerGroup varchar(30) SET @ABCOwnerGroup='GROUP ONE' SET @DEFOwnerGroup='GROUP TWO' etc.. --IF OWNERGROUP IS MISSING (Location NOT NULL...
I need to run a simple select statement for a column called AddrZip to show all records that contain '1/2 ' after the first space in the column. In Access 2007 it would be: **Left([Names],InStr(1,[Names]," ")-1), but can't find out how to do it in SQL 2005. All help will be appreciated. ...
Hello, I am using C#. net and SQL server 2005. I am uploading files(word, PDF) to DB and displaying on page using grid view. I have different claim Numbers. like co50000006( like 10 rows). I mean the claim number has different files. I have other claim numbers on my table.(like c08000131, c01000001). I want to display only one claim n...
I am working on a small piece that will generate an API Key using SQL Sever's NEWID(). The key will be used to access certain parts of our web app and will be passed in through a URL. The key is generated when a new API consumer is created (in a Stored Proc). The key is unique for each consumer. While the initial number of consumers is e...