I have a table and want to insert the current values of records that are being updated or deleted. I tried to create a trigger on before update and on before delete but SQL Server 2005 doesn't like the before word. How do I get this to work? I want the trigger to take the current record and copy it to a history table that is a mirror ...
I have two tables and I'm looking for the rows in one table where a time column is not near any of the values in another table's time column. (Near is defined as within a minute). Here's a code sample: create table temp1 ( id int identity primary key, value datetime not null ) GO create index ix_temp1 on temp1(value, id); GO ...
I have a problem with regional string characters inserted to MS SQL Server database. There is a PHP application that connects with mssql server and inserts some data. But instead of inserting characters such as: ą, Ą, ć, Ł, ź (and so on - btw. these are polish regional characters), when inserted into mssql table they appear as a, A, c,...
Hello! I have a PHP application currently connectig to a SQL Server via ODBC using Windows Authentication (odbc_connect). Under Apache this works fine. However I need to run this under PHP-IIS. While I can run PHP under IIS, and get this very same application connect to SQL Server when I run it from Apache, trying to run via IIS gives...
Hey all, I have a database server that currently has two databases, call them [A] and [B]. [A] is the standard database used for my application [B] is used by another application (that puts considerable load on the server), however, we use a few tables sparingly (that are shared between my application and the primary application that ...
Is there a way to get build a WHERE clause on the fly in a sql statement? This code is within a Stored Procedure. I have x amount of parameters and each parameter's default value is NULL SELECT * FROM MyTable m WHERE IF(NOT(@Param1 IS NULL)) m.Col1 = @Param1 END IF AND IF(NOT(@Param2 IS NULL)) m.Col2 = @Param2 END...
i have a sql server that has a test pp i have written, this thing is gaining traction and i am worried about loosing data (and the time it took users to enter the data) if the SQL server gets corrupted or someone does something dumb. i am thinking of what options i have that would do a nightly backup as a maintenance plan, and what i ca...
I have data Table1 ID Name ----------- 1 n1 2 n2 3 n4 Table2 FID YearS Val ---------------------- 1 2008 Up 1 2009 Down 1 2010 Up 2 2000 Up 2 2001 Down 2 2002 Up 2 2003 Up 3 2009 Down 3 2010 Up I want to return data in following format:...
Is there something like ISNULL() OR COALESCE() but not that checks for null value but for an empty value. for example: SELECT cu.last_name, cu.first_name, cu.email, hu.email FROM (SELECT DISTINCT c.first_name, c.last_name, c.email, c.household_id, h.head_of_household_id FROM rd_customers c JOIN rd_households h ON c.household_i...
I have something minor, yet very odd happening in SQL Server Management Studio 2005. I have 2 database catalogs which are essentially identical. There is a Market table with a foreign key to other several other tables for example the "Project" table has a MarketID column with an FK reference from dbo.Market. in both db catalogs the Marke...
I am trying to update data while I am reading them from database, see below. But after the whole thing finish, the data didn't get updated. Is there any transaction syntax i need to specify? (When I debug, I can see I have the right record retrieved.) using (conn = new SqlConnection(MyConnectionString)) using (SqlComman...
Hi, I have existing application which uses Sql server 2005 as a backend. It contains huge records, I need to join tables which contain 50K-70K. Client m/c is lower hardware. So, can I improve its performance by using MS Access as a backend? I also need to search operation on access file. So, which one is better for performance. Queryin...
How to rewrite this query NOT to use UNION (UNION ALL) clause: SELECT c FROM a UNION SELECT c FROM b expected result (recordset should be the same): SELECT c FROM .... ...
I have this procedure used for getting items on the current page. I would also like the include an OUT var having the total number of items so I can calculate the total number of pages. USE [some_name] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetFilteredProductDetails] @start int, @end int AS B...
I'm running SQL Server Management Studio 2008 against a SQL Server 2005 back-end. SSMS just exhibited a behavior I have never seen before. I don't know if this is something new in SSMS 2008 or just a function of something else. Basically, what happened in that I added some new columns to an existing table. After adding those columns,...
here are the 2 tables i have, i want to implement an trigger that customer cannot have more than 5 accounts from a one bank, but can have more than 5 in total. CREATE TABLE ACCOUNT( ACCOUNT_NO VARCHAR(20) NOT NULL, BALANCE REAL, BANK_CODE VARCHAR(20), BRANCH_NO VARCHAR(25), ACCOUNT_CODE VARCHAR(20), PRIMARY KEY(ACCO...
I created 1 database with 2 file groups: 1 primary and 1 index. Primary file group includes 1 data file (*.mdf): store all tables Index file group includes 1 index file (*.ndf): store all indexes Most of indexes are non-clustered indexes After a short time using the database, the data file is 2GB but the index file is 12 GB. I do no...
Say I have "Table A": Id | Col A 1 Z 2 I 3 Null ...and n number of tables that have this format: Id | A_FK | OtherInfo 1 1 "Some info" 2 2 "Some more info" 3 3 "Blah" ...where A_FK is a foreign key reference to the "Table A" Id. So there is one "Table A", and n number of "Table B's". For these "B" ...
Hi, I am making a db schema for facebook like wall structure. I need to save wall posts, share a link, share a video stuff into my database. Till now I am able to make this schema : GO CREATE TABLE [Wall] ( [ID] [int] NOT NULL IDENTITY(1, 1) , [PostText] [nvarchar](MAX) [PostedByUserID] [int] NULL , [PostedO...
It appears that my automatic CakePHP table columns - created and modified - are causing an error when I migrated to Microsoft SQL Server 2005. I found this old ticket, but nothing that seemed to resolve the issue. Here is the error: Warning (512): SQL Error: Conversion failed when converting datetime from character string. [CORE/cake/l...