Hi to All! we have recently updated our reference codes from 3 digits to 7 digits due to shortage of reference codes first we have reference no like reference no 101 102 103 .. .. .. now we have changed the reference nos to 7 digits due to some constraints like 1001101 1001201 ...more >>

I have SQL 2005 Developers Edition on my developers box. It has a table that contains a REAL column. When reading it programmatically from a Framework 2.0 app, the value from the column is correct, example 410 returns 410.0 On the server is SQL Express and the database was backed up from the de...more >>

Hi My below query is not working SELECT 'EstReqStd' As Type, WO.WorkOrderTypeCd + '-' + WO.SequenceNo as OFSType FROM FROM WORK_ORDER WO (NOLOCK) Group By 'EstReqStd' --Not Working WO.WorkOrderTypeCd + '-' + WO.SequenceNo Having (WO.WorkOrderTypeCd + '-' + WO.SequenceNo)...more >>

Hi guys, Is there a trivial way for looping through each record in a table that's equivalent to plsql's for r1 in (select * from tablename) loop v_name := r1.name; .... end loop ? I've tried the cursor approach but realised I'll need to define a variable for each column inside the t...more >>

Hi, I need write a script get data from 3 different. All 3 tables will have emailAddr, modify_date and sum details. But information may be duplicated in the 3 tables, having same emailAddr but different modify_date eg. My end result(new table) will contain the entry with latest modify_date if...more >>

Hi, I am trying to alter an SP in all my dB's. I am trying to loop through all the databases ,however my script always returns false when it checks if the SP exists (even though the SP exists)...for some reason it appears to be still in master db even though I change db inside the cursor. ...more >>

I am trying to delete hyphens and spaces from a field containing phone numbers. I know that the "Replace" Function should allow me to do this but I don't know how. Every phone number is different, so I don't have a 1st value to put into the arguments. I am very new to SQL Server and this is...more >>

It seems my much loved TSEQUAL function was finally buried in SQL 2005. Unless someone can suggest something better I guess I'm going to have to resort to something like the code below. Any other suggestions would be greatly apprieciated. I'll probably create a custom message to replace 532...more >>

Hello- I have three tables that I'm using to implement subtyping. Here's what they look like: Party [ID] INT PK IDENTITY(1,1) Created DATETIME Person [ID] INT PK FirstName VARCHAR(100) LastName VARCHAR(100) Organization [ID] INT PK Name VARCHAR(200) What's important to...more >>

If we have a SQL Server 2005 standard edition/Windows 2003 Standard Environment on a 32 bit 2 dual core proc Dell 2850 server, what is the maximum size of the memory we could possibly have? Thank you, Shiva ...more >>

SQL 2K Table schema id1, id2, name, inv_num Sample Data AA, 100, Sam, 1 BB, 100, Paul, 1 CC, 101, Gene,1 AA,100,Gita,2 BB,100,Manu,2 I want to compare inv_num 1 and 2 and get the extra record (CC,101,Gene,1) which doesn't exist for inv_num 2 ,comparing 'id1' and 'id2' Thank...more >>

When I start I get a message sayinf rundll-cmicfg.cpl. Thanks for any advice you can give -- Roy...more >>

Using xp pro. Had to reinstal sound drivers. Now getting message create secondary for getting property set extention failed.-- Thanks for any help you can give. Roy...more >>

Hello everyone I was perusing the threads and found a helpful link that Aaron Bertrand posted http://www.aspfaq.com/5006. In short it expalined the three things (DDL, sample data and expected results) that should be given to increase my chances that my query question would be answered. I wa...more >>

We are in the process of updating a Text column. How can we update the row and column and add a carriage return to the existing text???...more >>

Here's table 1: Fans FanID, username Table 2: Photos PhotoID, FanID, photofilename, defaultPhoto (true or false) Not all records in Fans will have a corresponding record in Photos. I'm doing an outer join that gets ALL the Fan records, and if they have a record in Photos, and displays ...more >>

I have a stored procedure that needs to retrieve the top 1000 sent items and the top 1000 received items - each ordered by date. So, effectively, the most recent 1000 sent items and the most recent 1000 received items. Then, I need to combine them into one result set and again take the top 1000 i...more >>

Hi All, I'm splitting a large database into filegroups. Files that join each other are put in different filegroups to eliminate contentions. Is it advisable to create Clustered indexes on different filegroup and not on the base table filegroup.? I know it can be done on nonclustered indexes...more >>

I am trying to combine two rows of data into one row for comparison purposes and I can do it using a number of steps but thought that there had to be a way to do it in one SQL statement. Any help would be appreciated... Instead of having two rows with the different period end dates, I would ...more >>

I have a field called EntryDate which is a datetime field. I have a field called starttime which is a datetime field. How do I pull out the date part of EntryDate and the time part of starttime and throw them into a third datetime field? Bill ...more >>

I have column named "Address" that uses varchar(100). I have data 101 Server St 2 Costco Ave 102 Server St If you run SELECT Address FROM tbladdress ORDER BY address I get 101 Server St 102 Server St 2 Costco Ave What I actually want is 2 Costco Ave 101 Server St 102 ...more >>

Hello. I am struggling to figure this one out. Can someone please advise. These are the two UDF's. Something is non-deterministic or imprecise here, but I don't know what... *** CREATE FUNCTION dbo.fn_GetLineItemText ( @LineItemId int ) RETURNS varchar(500) WITH SCHEMABINDING AS BEGI...more >>

Hi ppl, Who knows why select * from Table where VarcharField NOT LIKE '%' returns different result on SQL 2000 and 2005? 2000: it returns rows were VarcharField IS NULL. 2005: returns nothing Was it an intentional bug fix or just an accident?...more >>

In a db, is it a good practice to have Address info in Address table in upper case or lower case? ex: street name, city, state, county, country etc.. How about name [first name, middle name, last name, prefix, suffix....] If you have any links that talk in detail about this, I'd like to rea...more >>

Hi there, May be this is a popular issue.... but this is the first time for me. Any ideas are appreciated. ------------------ ISSUE 1 ----------------------- select * from Table1 as t1 left outer join Table2 as t2 on t1.id = t2.id and t2.type in (case when <condition> ...more >>

Query: update entries set item_convert_status=0, item_code=key_cross_ref.item_code, mida=key_cross_ref.mida, zeva_default=key_cross_ref.zeva_default from entries,key_cross_ref where entries.company_code=1 and (ltrim(rtrim(entries.origin_barcode))=key_cross_ref.barcode or ent...more >>

I've been trying and I can't seem to get it. The results need to be grouped together based on their intLeadID value, but ordered by the intSortOrder field. If intSortOrderID has more "1" than "2" for a givin group of records, then that record-group needs to appear higher on the list. Just s...more >>

This is a counting issue following an inner join. I have two tables (simplifying it for clarity) Offering Table: OfferingID, year Registration Table: RegistrationID, OfferingID, Registration_Status --------------------------- Start Table Definitions ------------------------------ CREATE ...more >>

I have many Excel financial reports that I am trying to convert and import into SQL Server tables. I have used VBA to strip the reports down to a set of row headings and many data rows. The problem is that most of the row headings are month_year combinations; e.g. 200602, 200603, 200604, etc...more >>

I really need help with this, I am sure it cant be too hard, I have just been looking at it too long. I have part numbers and vendor numbers and I would like a query to show a distinct part number and vendor derived from the vendor that has delivered the most of that part. The reason I have...more >>

In general, is it bad practice to call a view from w/in a stored proc? Some sites imply that SQL server creates two connections thereby straining system resources that much more, some sites claim it doesn't. ...more >>

I need to loop through all the columns in a table to check for null values in each column. So I wrote the following cursor. I added a test null to one of the columns in the table, so I know that at least one column contains a null value. The cursor loops through a table that contains a list...more >>

I am look to maintain a directory tree table in SQL Server 2005. The table will contain details of file and folder paths, titles, size and a few other additional fields not related to the file system. Before I begin, and start writing code to maintain the table is there anything in 2005 wh...more >>

I have three connections from oracle DBs which all insert into 1 SQL Server 2000 table. About 1 million rows in all. The problem is only one connection is inserting at one time.... then the second.... then third..... The first connection obtains a table lock causing other connections to wait unt...more >>

I am trying to insert data from three different oracle connections to one sql server 2000 table. However, the first connection obtains a table lock and prevents other connections to insert data into the table. Is there a way all three can insert data simultaneously in the table???? Does recovery...more >>

I have a 3 tables tblAgent, tblClient, tblVendor. Since each entity requires different info to be stored, it makes sense to have 3 different tables. Now each agent, client or vendor can write many notes, so I created the table like this to store all info ContactID, ContactTypeID, NoteE...more >>

I have a cursor that is supposed to fetch all the records from a temporary table, #temp, one at a time, do some manipulation on the data, insert the results into another temporary table, #detailedTransRpt, then delete the record from #temp. All of the records from #temp should be deleted befo...more >>

HI all, I am trying to find duplicate records ina database that I have taken over. I am using group bys and count() to find the sups. I also realise that we could have a problem with misspellings and hterefore not have a duplicate as in Joe Soape, Jo Soap, Joe Sope Joe Soap. These couldpossibl...more >>

I'm trying to figure out how to use VB.NET 2005 to "SqlBulkCopy" (a) table(s) from a MySQL database to a SQL Server 2005 database. The part I can't get set up is the "FROM" part. An ADO "recordset" doesn't seem to be the answer as SqlBulkCopy seems to want a reference to a whole database not ...more >>

select * from table1 where stringname in '%^<>:/\|*?,''%' I need to search for all the characters above. How do i do it? Thank you....more >>

How does one query SQL Server using T-SQL for the information on the most recent backup for a particular database? In particular, we need the datetime the backup was made, and the name or location of the backup set, and to return this information to the application. Thanks! -- Jim ...more >>

I am testing a VB.NET application using SQL Server Express (2005) and ADODB. Multiple instances of the application all access the database. In some cases, they access and try to update the same row of a table. I am opening this table with a single row query and ADODB.LockTypeEnum.adLockPessim...more >>

Hi, I restored a wrong copy of a db, since then I that restored the correct version. during the execution of the reports in report services I get an error that read *** 'reportServerTempdb.dbo.persitedstream' ** this message is sparatic, at time the reports are displayed other time I get t...more >>

i have several tables in an old database that i want to almagmate into one table. each of the three tables have 1 record in them but different fields lengths. None of the tables are linked to each other any ideas how i cand get the following select a.*, b.*, c.* from table1 as a, table2 a...more >>

Hi, I'm trying to retreive details from a table that contains unicode characters(NVarchar datatype). The SQL statement I'm running is:- Select AccountID,IssuerID,Name,Surname, Postcode From tbl1 Where Surname Like '%ΠONTOI%' But it returns nothing. If I remove the 'Π' it returns the rec...more >>

Sorry if this is a repost. I was timing out when posting... I have a simple query that returns me the latest date for a company: select companyid, max(enddate) as latestdate from companyresults. What I would like to be able to do is use the latestdate above and also get the latestdate - 1...more >>

Hi, The production database server indexes are rebuilt, usage updated and the server is rebooted once a month. For about two days after this maintenance window, performance isn't as good as it used to be and I've noticed CPU utilization increases by 15-25%. I realize that all SPs have to b...more >>

Hi All, I am trying to copy data from Sql 7.0 to Sql 2005. The structures in both the database is very different. I cannot just map the fileds from one db.table to another db.table I will have to write a script tha would insert data in the new db.table. One table in the new database will nee...more >>

Dear all, How can I make a snapshot in sql25k? When I do click on the option 'Database snapshots' only appears 'refresh'. Thanks for any input, ...more >>

I've written a set of stored procedures that currently use a cursor to pull data from a staging table into two separate tables. The two destination tables are linked by a Foreign Key relationship on the primary key (can't change this design), which is why I need to use a cursor to be able to i...more >>

I have a query statement that will return the difference between 2 fields. But if 1 of the fields' value is null, the difference will be null. What can I do so that the server will treat null as 0?...more >>

Does anyone know if, in an Update or Insert query within a DDQ task, it's possible to have more than one statement in the query, and have them both execute? e.g. update tablex set field1=1 where id=? update tablex set field2 = 2 where id=? Also, on the same subject, can you mix the sta...more >>

is it possible to set the value of multiple local variables using just 1 statement. For example assume a simple table 'E' with 3 columns 'empID', 'empName', 'empToken'. I can get their values with declare @ENAME as char(30), @ETOKEN as int SET @ENAME = (SELECT empName FROM E WHERE empID = 1)...more >>

Hi, I am using the bulk insert to import over 26,000,000 records about 55 bytes long. The insert seems to only want to import 1,780,000 records. I'm not getting any errors and this works fine on smaller files. I have increased my commandtimout to 260000 so I don't think that is the problem....more >>

Hello NG, In my program users should be able to manage their documents. To allow users to search for documents they can use predefined attributes like Title or Description or they can assign their own custom attributes. I'm using the following tables for this. Document (Stores the predef...more >>

Dear All, I need to convert my database from Sql server to oracle.I have tried the tools like SwisSql,SqlWays.But that tools didn,t work properly because they are evaluaation versions. Any body help me how to migrate the entire database from sql server to oracle othen than the a...more >>

I am trying to write a function that returns different values regdaring to a input value. Why can't I write like this. What shoudl I do instead. I want to be able to write a select Item from GetPermission ('user', 're') Regards /Magnus alter function [dbo].[GetPermissions3] ( @UserNam...more >>