I have the following code for a trigger: create trigger trPassDat ON men FOR INSERT, UPDATE AS declare @man int; select @man = I.man from men U join inserted I on U.man = I.man if Not exists (select 'True' from deleted where man = @man) BEGIN update men set passdate = getdate() where man = (selec...
Hi All, Is there a way to insert/update a datetime value (getdate()) into a temp table created like the following: select id, null as testdate into #temp_table and then later statement: update #temp_table set testdate=getdate() i get error: "cannot convert datetime into int..." Thanks, rod. ...
I have a large table with 10+ millions records in a SQL Server database. The table contains certain type of data for all 50 states in the US. So if I create 50 views, one for each state, from this table, would the performance of making queries from my application be improved? Other suggestions? ...
I would like to have a TSQL Statement to move Name Suffix (Jr, Sr., IV, etc) into another field. The suffixes I see are JR SR I II III IV V Here is a sample of the data LastName BRUNNING, II BURCH II BUSS, JR. CANI III CHRISTIAN,SR COLVIN Jr COWHERD,JR. I would like the suffix moved out of the LastName field into another field call...
hi all, One curious question. if i have a table with column with weblinks then what could be the datatype nvarchar or varchar. and what could be the size of that datatype? ...
Let's say my table structure looks something like this: CREATE TABLE [dbo].[table1] ( [id] [int] IDENTITY(1,1) NOT NULL, [data] [varchar](255) NOT NULL, CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED ([id] ASC) ) CREATE TABLE [dbo].[table2] ( [id] [int] IDENTITY(1,1) NOT NULL, [table1_id] [int] NOT NULL, [data] [v...
Hi All, Given 2 tables CustomerActivity CustomerId, ActivityId, CreatedOnDate 1, 1, 8/1/2010 1, 2, 8/15/2010 2, 1, 7/24/2010 2, 2, 8/15/2010 TempUpdateTable CustomerId, RecentActivityDate 1, NULL 2, NULL How do I fill in the NULLs in TempUpdateTable using the CustomerActivity table? My first attempt didn't pan out: UPDATE [TempUpd...
When executing a SELECT statement with a JOIN of two tables SQL Server seems to lock both tables of the statement individually. For example by a query like this: SELECT ... FROM table1 LEFT JOIN table2 ON table1.id = table2.id WHERE ... I found out that the order of the locks depends on the WHERE condition. The que...
Running SQL Server 2000. I have a linked server over ODBC to Providex data files. Never mind mind if you are not familiar with them, the point is that for SQL Server to be able to select from the linked server, an arcane and undocumented TRACE needs to be turned on. The exact command is: DBCC TRACEON(8765) The problem is that i need to ...
My DBA's are saying my foxpro application or .DBC (Database container) are hitting SQL server but searching all the code can't find the SQL call (FMTONLY ON/OFF). This is the SQL command being sent: FMTONLY ON/OFF Getting called 16260 times every few minuets? Any ideas how to find this or what could be causing it, maybe my DBC file? ...
I'm a bit of a noob with DAO and SQL Server and I'm running into a problem when I'm trying to insert values into two tables that have a relation. The table Photos has a gpsId field which has a foreign key relation with the id field of the GPSLocations table. I want to create a new Photos entry linked to a new GPSLocation, so the code l...
i have a field that is a varchar and the string is stored as "1,223" or "23,342,234" they have comas! is it possible to read them as numbers? i dont like any of these answers. in mysql reading a varchar 1,123 and converting it to a number was no problem at all. ...
After customizing the role settings (changed the 'public' role, just tick everything to Deny, then I set it back), all of my users cannot login, except to give them the sysadmin permission. So, I want to reset the role settings, is there any way to do that? ...
I have the following tables: Section and Content And I want to relate them. My current approach is the following table: In which I would store Section to Section Section to Content Content to Section Content to Content Now, while I clearly can do that by adding a pair of fields that indicate whether the source is a section or...
I need help. I don't know what the right way to create the table I need is. I can do it different ways, but I might be doing it a stupid way. Here is an example of the situation I have. I have persons. Each person has fields. Fields could be: ShirtType, PantType, HairColor, EyeColor, ShoeType, Height, Weight, etc. I will need to add...
Hi, I have existing application which uses Sql server 2005 as a backend. It contains huge records, I need to join tables which contain 50000-70000. In client machine it slowdown. So, can I improve its performance by using XML as a backend? I have checked that the size of xml file becomes 35MB to 50MB, when I load data. I also need to...
Hi, I just can't get around my head about AUTO_CREATE_STATISTICS? I run the tuning advisor and it advised me to create statistics on a table. the AUTO_CREATE_STATISTICS is set to true and i would have expected the statistics would have been created automatically. Can anyone please explain? ...
SELECT P.PK_PatientId ,PV.PK_PatientVisitId --, PV.LastUpdated , ISNULL(P.FName,'')+ ', '+ ISNULL(P.LName,'') AS NAME , MAX(TVP.PK_VisitProcedureId) AS PK_VisitProcedureId , MAX(PV.LastUpdated)AS DateSort FROM dbo.M_Patient AS P ...
Hi, For my project, I have to log all changes made on my objects, through the entity framework. This consists just to register which fields have been edited on which table at which time. Roughly, put changes in a table with this kind of structure: IDEvent, EventDate, TableName, RowID, FieldName, OldValue, NewValue If there is multiple...
Hi all, I want to store a native binary file into the DB having the column datatype as 'image', in SQL Server 2000, through SQL Query. I have found the solution for the case of 2005, thet is using the OPENROWSET and BULK. The solution as follows. CREATE TABLE [dbo].[temp]( [name] [nchar](10) NULL, [blob] [image] NULL ) INSERT...