How can I strip or replace values in a an image column, for example, if I select the data in an image col that returns the following x25005000440046002D00... how can I strip out all the '00's to end up with 0x255044462D any help would be great. Bri ...more >>

Hi I have an SQL 2000 Server, with a number of Databases on it, I would like to run a query which extracts data from one database and stores it in another database, the databases have a column which is the same in each called "AccNumber". How does one link two databases in an SQL 2000 query...more >>

I have a temp table #Temp(id int, statement varchar, result int) The statement column contains a prebuilt sql select statement e.g id statement Result 1 select count(*) from books where authorname like 'A%'. 2 select count(*) from books where authorname like '...more >>

I'm trying to use the following to calculate the median by year for the field "labor_rate". This should calculate the median for any year where the count is ODD. I haven't started on the "ELSE" part to calculate when the counts are EVEN. Anyway, the following doesn't work. I get an "inco...more >>

Greetings, I'm fairly new to stored procedures and temporary tables and i was going through this tutorial for a banner ad system. http://aspnet.4guysfromrolla.com/articles/033104-1.2.aspx it's a nice write up, how over i'm concearned over some of its logic. In part two, the author di...more >>

SQL Server Management Studio Express is free like SQL Server 2005 Express edition ? ...more >>

Hi I have to create a series of reports that look back over certain days ie. day before current week current month, quarter, year The reports will be scheduled to be run between the hours of 10pm to 6am when the business is shut. I am using getdate - days to get my time span an examp...more >>

I should know this.......(so now's the time to figure it out I guess) I want to update a table based on a query like update table1 (select col2, col5, col6 FROM table2 WHERE <<some condition here>>) I actually have many columns that need to be updated and do not want to do the following...more >>

Greetings! We were running into an issue with creating a stored procedure that was around 1400 lines long (calling other procs in the process). The stored procedure creation would take over an hour to complete - note that this is just the Create proc statement. The database had many tables,...more >>

We are using SQL Server 2000 Standard Edition. Among other things, the "Enterprise" edition adds "indexed views". Could someone tell me what indexed views are, and what they are good for? Is this simply an index on a view? Do the underlying tables have to be static for the index to be effect...more >>

Could some one please point me to a good resource on how to design history tables in a dataware house situation?? For example, in the case of products table, if product description got changed over time after the product was purchased. The old invoice still shows the old description but the...more >>

Hi, I need to store a hierarchy in my database (a tree) I have 3 types of data: Cases, Groups and Users. I can have any combination of them. Check the following link ('Groups', 'Cases', 'Users' and 'Tree' will be tables in my database): http://www.lemforever.com/temp/tree.jpg What do ...more >>

Hi all, I have two tables that don't have any common data: [Table1] Column11 Int AnotherColumn Int [Table2] Column21 Int Data: [Table1] Column11 | AnotherColumn 111 | 8 112 | 8 113 | 8 114 | 8 [Table2] Col...more >>

Hi, I've created this code to determine the dates for an exam when a certification must be met within a X number of months or years. The last deadline date for an exam is the enddate. The marge where one can schedule his time to study depends on how many exams he must take. So based on ...more >>

I am trying to rewrite a sp that I created years ago to avoid using cursors so there is no problems with mutiprocessor systems and parallelism. Simplifying, we have an order table and task table for each order: CREATE TABLE [TOrders] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [Description] [...more >>

I have a feeling I already know the answer to this, but is there any way that I can use the results of an SP in another query? In other words, I have an SP that outputs a table, and I want to use that table as an input to a SELECT... -mdb...more >>

Hello there Is there a way to get the data file size and log file size with code? ...more >>

I have two triggers that execute on update, Trigger 1 also executes on insert , I need it to check to see if an update is allowed ALTER trigger [trg_Mytable_ins_upd] on [dbo].[MyTable] for insert, update as set nocount on; Declare @IsClosed bit IF UPDATE(Isclosed) BEGIN S...more >>

I need to add a rowguid to 300+ tables. Is there a way to script it? Thanks Jeff ...more >>

I am trying to insert into multiple tables and if any of the inserts fail, I would like to rollback the entire thing. This approach works but is it a good design?? Please let me know. declare @err int declare @id1 int declare @id2 int set @err = -1 begin tran insert into ...more >>

Hi I have some problem about SQL 2000 SP4. After I setup sp4 my application connect to sql for query transaction it very slow so I have to recreate index it good more, Why SP4 make database to slow? Thanks. new sql ...more >>

Hi, I have been researching for a while about ways of storing trees in a database. I found a nice solution in the following page (there are 2 actually, I'm talking about the second one) It starts when they talk about creating this table: CREATE TABLE Tree ( Node int NOT NULL IDENTITY(10...more >>

am trying to dynamically determine the number of Active Projects in a given month dynamically for a give ProjectType. (e.g. For June 2005, I would like to see all projects created on June 30, 2005 or before that have not yet closed grouped by Project Type.) **Simplified DDL ** Projects ( ...more >>

I have a table that contains a datetime field. It's meant to keep track of monthly equipment inspections. There can be only one inspection per month, and I need to keep track of the day it happens. Currently I have one datetime field that keeps track of the date the inspection happened. ...more >>

I have a couple of tables (see below) that tell me what days a particular customer depot (LocationID) is scheduled to have deliveries on (DeliveryDays table), and what days the Courier picks up for these deliveries (CarrierCollections table) I want to create a query (a view actually) that t...more >>

Hi there, I'm putting together a simple database auditing system (on a SQL 2005 system), using triggers to capture database changes and logging them to a set of shadow tables. I need to also capture transactional information; that is, be able to group the captured changes by their respectiv...more >>

I have started to migrate our SQL 2000 tables and views to our new SQL 2005 box. One thing that I have noticed is that one of our DB's is still at 65 for compatibility. I have now migrated the DB and found that certain things do not look the same as they did before. Here is an example of a vie...more >>

I have come across this several times now, and I cannot figure out how to do it better. Say I have a simple table called SourceTable: DECLARE @sourceTable TABLE ( data1 INT, data2 INT, data3 INT, data4 INT ) I need to create a table (view, tv function, etc.) that looks...more >>

I'm using SQL 2005. I would like to calculate the median (middle value in a dataset) in either transact SQL or by using a function. Any suggestions? Thanks. -- Craig...more >>

Hi there, I've made a join query: SELECT B.ITEMDESC AS ITEMDESC, A.ITEMNMBR AS ITEMNMBR, 0 AS 'SUM QTYORDER', A.QTYONHND AS 'QTYONHND' FROM IV00102 A LEFT JOIN IV00101 B ON A.ITEMNMBR = B.ITEMNMBR WHERE A.RCRDTYPE IN (2) AND A.LOCNCODE IN ('SALES') AND A.ITEMNMBR = ...more >>

Hi all, Happy New Year!!! I have a table that has the following fields: Client, Date Data: Microsoft, 1/1/05 Microsoft, 2/2/05 IBM, 3/8/05 HP, 2/4/05 HP, 2/5/05 How do I construct a query so that the results are: Microsoft, 2/2/05 IBM, 3/8/05 HP, 2/5/05 Thus it is only givi...more >>

Below is my DDL. The table is self referencing. The foreign key reference is bfhvT_ParentSID which references bfhvT_SID ---Start CREATE TABLE [dbo].[tb_bfhvTest] ( [bfhvT_SID] [int], -- THis IS MY IDENTITY (The Real table has an Identity Column .....I Promise) [bfhvT_ParentSID] [int] NULL , ...more >>

Is there a way to do a query and include a field if it is *not* null? For instance, I know I can: Select LastName + isnull(FirstName, '') from tblClients I want to include a field only if it isn't null, for instance, if a client is inactive, I want to display "(inactive)" in the results: ...more >>

I have a decimal field. Decimal (18,4) How can I only select whole numbers from this field or vice versa? Thanks ...more >>

Hi, I have a problem to divide two numbers type float, and for 100. The problem is it to use the function the ROUND in the result. The example is the next: declare @m1 float declare @m2 float declare @w1 float declare @w2 float declare @acumulado float declare @division float decl...more >>

Have a slight problem, When I view the lists of servers on our network in managment studio, our SQL Server 2005 default instance does not show up in hte list... we have two instances running... default instance and one named SQL2005 the SQL2005 one shows up fine with a version of 9.0, all our ...more >>

I just got a request for combining a date, with an incremental to generate a job #. I hate the idea but then when do we think this up anyway? Here is what I'm playing with as of now from one of my developers: SELECT LEFT(CONVERT(VARCHAR(10),GETDATE(),10),2) + SUBSTRING(CONVERT(VARCHAR(1...more >>

We are currently using VB to create order numbers. Each user requests an order number as and when required. The problem is that the next in the sequence seems to be allocated on the odd occasion to more than one user. This being due to the requests coming at almost the same time but not as yet...more >>

Hi, Here is the query I am trying to achieve and having syntax issues Select count(distinct name, number) from results. To replicate the situation use the following SQL create table results (name varchar(100), number int) insert into results values ('test1', 1) insert into resu...more >>

I'm trying to validate to run the following statments. I have several special characters that aren't being accepted as the string to insert. I'm pretty new to the SQL syntax and string limitation, can someone please review and tell me ho I need to handle the special characters in my strings?...more >>

Hi Folks, I have two tables, one of which I want to update from another. Essentiall I have a table of orders and a product table. I want to subtract the qty sold in the orders table from the QtyInStock column in the Products table, for every line in an order. But I dont really want to ...more >>

I have a simple SQL statement, I need to populate columns from several tables. I need to populate some of the columns depending if there is data in a child table that matched the parent table (a one to many relationship). How can I put in an "if exists" into a select statement? I need resul...more >>

Need to create a SELECT statement which is above my skills. There are two tables involved, both are shown below. Table 1 stores messages to be delivered to a user on a web page. Messages can be sent to a specific set of users by setting BlockList to false and adding each user to Table 2 or ...more >>

i have an equation which i have to implement in query: availa= (1-[return0]/sum(r1+r2+r3+r4+r5)) when tried the query i get avail as zero always, help me out ...more >>

Hi, I've posted this message previously on sqlserver.server but somebody mentioned that it might be related to fulltext (though I don't think so) so I'm posting it here now. I recently migrated my DB to SQL 2005 from SQL 2000, the migration was mostly painless except for the following problem ...more >>

Hi all, I've now spent a large amount of time reading posts about IsNumeric('.') - but thats not my problem. In the SQL Server Book it says about IsNumeric "ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it ...more >>

Hi is there a way of sorting elements in a predefined order like select * from currencies order by ('eur', 'can' ,'yen', *) so that eur, can,yen comes first and then the rest? (without an sp) greets mike ...more >>

Dear all, We've got a very strange issue and we can't work out so any help will be welcomed. Involved Access and Sql Server. From Access db a specific field of a row is not be able be updated for the developer (by hand) as well as by the end-user. But the rest ones (other fields in the sa...more >>