I need to store binary files in a varbinary(max) column on SQL Server 2005 like this: FileInfo FileInfoId int, PK, identity FileText varchar(max) (can be null) FileCreatedDate datetime etc. FileContent FileInfoId int, PK, FK FileContent varbinary(max) FileInfo has a one to one relationship with FileContent. The FileText is meant...
Hi I am looking for A solution to update values in table1 only if the values change. I mean compare to tableb and update only the changed values ...
I have a project that utilizes a number of FormViews and SQLDataSources. I would like the default behavior of the SQLDataSource to insert values that are empty as null. I know I can do this by creating an insert parameter for each parameter, but this gets really old for having to do this for update and insert on every parameter that co...
I just realized my caps was on from working in SQL Server, ha. Anyways, Im trying to write from serverName.databaseName.databaseInstanceName.TableName to C:\FileName.xml using bcp and I want to write only columns Col1 and Col2, could I get a little syntax help? I'm struggling over here. Thanks, Ted ...
I have events and photos, and then comments for both. Right now, I have two comments tables, one for comments related to the events, and another for photo comments. Schema is similar to this: CREATE TABLE EventComments ( CommentId int, EventId int, Comment NVarChar(250), DateSubmitted datetime ) CREATE TABLE PhotoComments ( C...
I have a loginAudit table and I am trying to get a count for all logins for each day. What I'd like to do is have days where there are no logins return their day and a login count of 0. Currently no row is returned for days with no logins. Could be this isn't possible and have to fill in empty groups in the app after query results re...
Hi , I have got an "A" DATABASE and "B" DATABASE.When i inserted new record to "X TABLE" in "A" DATABASE i wanna insert another record to "X2 Table" in "B" DATABASE with Trigger Function of SQL SERVER 2005.How can i do this ? Can someone explain me this. How can i connect another Database and insert record specific table. Thanx everyon...
I'd like to clear a database's schema on my SqlServer instance. What tsql should I use? By schema I mean tables, constraints, etc. I want the result to be similar to if I created a new database, however I don't want to actually drop and create the database. Why: For those curious, I need to empty the schema without dropping because of...
We are developing an ASP.NET application and carrying out the database design too. Kindly let me know if you know any best reference document for SQL Server database design - best practices. ...
We are working on a C# application, we've been using Linq to SQL or standard ADO (when performance needed) to work with SQL Server. We have a table layed out like so: Customer ID, Year/Month, Product Name, Quantity Each customer has additional columns per product. We need display this information in a data grid like so: Customer, Y...
I have an application which has two database servers: one is the development database server and the other one is a production database server. Accidentaly, a couple of months back I published the application for use and it was pointing towards the development database. Since it was pointing to the development database, all the informa...
Ok, the basic situation: Due to a few mixed up starts, a project ends up with not one, but three separate databases, each containing a portion of the overall project data. All three databases are the same, it's just that, say 10% of the project was run into the first, then a new DB was made due to a code update and 15% of the project w...
I need to create a sales & commission report Basically it goes (please forgive the blatent craziness of the SaleDate table, but I'm simplifying the business logic, and in reality it actually makes sense to have it this way) SELECT agentName, SUM(sales.Amount) AS Gross, SUM(sales.Amount * sales.Commission) AS Commission F...
I have discovered some strange behavior where a stored procedure is returning inaccurate results by a penny or two. Here's the code (I didn't write it): ALTER PROCEDURE [dbo].[TN_GetSimpleBalance] @custID int, @nBalance decimal (8,2) output AS declare @ArBalance as decimal (8,2) declare @custStatusCode varchar (2) declare @u...
Consider the need to create a resultset of dates. We've got a start and end dates, and we'd like to generate a list of dates in between. DECLARE @Start datetime ,@End datetime DECLARE @AllDates table (@Date datetime) SELECT @Start = 'Mar 1 2009', @End = 'Aug 1 2009' --need to fill @AllDates. Trying to avoid loop her...
How can i get this result with changing query below. foo 01 02 03 declare @a as int set @a = 1 select single.* from (select case when 0=0 then '0'+ case when @a = 1 then '1' when @a = 2 then '2' when @a = 3 then '3' end end as foo) single cross join (select 1 as bir union all select 2 union all select 3) multi ...
Hi, How do I Select records between two dates in two columns? Select * From MyTable Where 2009-09-25 is between ColumnDateFrom to ColumnDateTo I have a date (2009-09-25) and I like to select the records that is in the timeframe ColumnDateFrom to ColumnDateTo. Sample Record 1 ColumnDateFrom = 2009-08-01 AND ColumnDateTo = 2009-...
Say I write the query: INSERT INTO DestinationTable (ColumnA, ColumnB, ColumnC, etc.) SELECT FROM SourceTable (ColumnA, ColumnB, ColumnC, etc.) And my source table has 22 million rows. SQL server fills up my hard drive, and errors out. Why can't SQL server handle my query? Should I use a cursor and insert a row at a time? PS - it ...
Many times when I've written stored procedures, etc. I use a CURSOR at first and later find some performance issue with my procedure. Every thing I read says CURSORS are awful, cause unnecessary locking, etc. and performance testing proves the same. My question is when do you use a CURSOR and in what situations are they useful or good...
I am new to sql and How can I automatically set the value of one variable depending other variable's value. I have price in foods table, and in orders table I want to change the total value of the price according to the # of orders for a specific food. ...