Hi! I have a table with the folowing fields... ID int, SubID, int Description varchar(30), Stat bit. the table contains 1, 0, This is my test, false 2, 1, this is my test to test, false 3, 2, sub sub to 2-1 rec, false 4, 2 dffdfd, false 5, 0, completely new proj, false When i s...more >>

I inherited a nasty database that contains a table with a field called userNotes. userNotes is of varchar(256) type and obviously should have only been used for text notes. Unfortunately for me, users used the field to enter notes and the important number codes that relate to another table. Th...more >>

hi, i want to knew all the infomtion about COLLATE , pls give me some good link where i do this ...more >>

I can't seem to figure out why this is not working (nothing is returned when it should): declare @id varchar(100) set @id = '''aa'', ''bb'', ''cc''' select * from t0 where tagval in (@id) Those are all single quotes. If I do this: declare @id varchar(100) set @id = '''aa'', ''bb'', ''...more >>

Dear members, I have problem about distributed transaction, there is table in server A, named "tableA", database "dbA" I register serverA at server B through sp_addlinkedsrv in server B, so I can query : "select * from serverA.dbA.dbo.tableA" with connection in server B. I make sure to...more >>

Hi, Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL Server 2005 EE to be able to use whole of memory if required. I have already used "awe enabled" option with sp_configure to change the run value to 1, but it seems other parameters must be configured on OS. What's the...more >>

As of now, I hardcode this last date of 2006. SELECT DISTINCT Family, EffEndDate FROM PlanSetup WHERE (EffEndDate = '12/31/2006') ORDER BY Family How can I get the last date of the current year without hardcoding the value as above? Thanks. -- --------------------- zwieback89 M...more >>

What is the command line syntax to query a .txt file using openrowset? I need to write a script to import some .txt files. PLEASE, PLEASE give some real examples. BOL is woefully inadequate with regards to querying text files. Thanks. Archer ...more >>

I followed the instructions here http://www.4guysfromrolla.com/webtech/092105-1.shtml to create a UDF and use it in a query but I get an error Syntax error converting the varchar value '2, ' to a column of data type int. Where did column of data type int come from? CREATE FUNCTION db...more >>

SQL Server 2005 SP1 Standard & Express ..NET 2.0 and ADO.NET C# Hi, I have a stored procedure that returns results from 3 select statements. When I "ExecuteDataset" from C# code the returned dataset has 3 tables named "Table", "Table1", "Table2"... Quest: Is there any way that I can ...more >>

I need to write a SQL statement to return only orders that share part ID's with other orders from a database. Example: OrderID PartID 12345 12a 12456 12a 23455 11l The statement would need to return the 12345 and 12456 OrderID since it shares a PartID with an o...more >>

If I have a dynamic SQL Statement in a procedure, is there a way to get a set of data (type, precision, etc.) on each of the columns that will be returned by the SQL Statement? Thanks. ...more >>

I have a table with a column defined as varchar(890). When I attempt to insert a string of over that size using a straight INSERT INTO statement, I get a truncation exception (unless I specify SET ANSI_WARNINGS=OFF). If I create a small stored procedure to accept a parameter of varchar(890)...more >>

Hi, Is there any document that explains best practices for database design and naming conventions? Thank you, Max ...more >>

It was a generic procedure that would generate insert statements for the result set of a passed in query as the parameter for the procedure. I have a vague memory of seeing it in the past two months. When I first saw it, I thought "that's clever." Now I could make use of it. Thanks, IanO ...more >>

in sql2k, developer accidently deleted product data, and that database didn't get backed up last nite. the log file (ldf) looked like still holding all the deleted records (probably hasn't hit the check point yet). i detached the database and made a copy of mdf and ldf to a different server. is...more >>

Hi, Can someone please help me with the syntax to restore a database from 2 ..gz files. This is the syntax I have so far but I don't know what I need to modify to correct my syntax errors: RESTORE DATABASE p_dbcon001 FROM DISK = '\\dumps\FRA02.p_dbcon001.dump.20061112_1901_06.s01of02.gz' F...more >>

hello, my table currently looks something like this trxid desc apply 111 hello 111 121 bye 121 131 NULL 111 141 NULL 121 what i'd like, is to run an update query and get it to look like the following. i would need to isolate all the desc fields with a NULL value and place the...more >>

Hi All, How many quetions on 70-444? What is the duration on it, and are we crunched for time on it or? What is a better representation of the difficulty level of the exam? trascender or MS Study Guide? Also I found this one question in the test prep's, The answer ddint make much sens...more >>

I have a join betw two tables. I cannot modify the table values for various reasons. The join is between two fields, first one is numeric, the second is varchar but normally only has numeric values in it. When an alpha character is input into the 2nd field, it causes my SP to fail. Is it possi...more >>

I know modifying system stored procedure is not supported by MS. But I want to modify a system stored procedure temporarily and revert it back to its original. The system stored procedure that I want to modify is sp_rename. I want to comment out any unconditional raiserror. I believe MS sh...more >>

Hi, I have a table that looks like this? Record #1: Account: 1 Address1: A Address2: B Address3: Address4: Address5: Record #2: Account: 2 Address1: C Address2: Address3: D Address4: Address5: Record #3: Account: 3 Address1: E Address2: Address3: Address...more >>

Hi, I am trying to insert records from Dynamics Ax (an ERP from MS) by calling stored procedure directly from Ax. It works great except for date fields. The problem is I am passing dates from Ax as string. When there is no data in this field, Ax sends empty string ('') and SQL interpret...more >>

Hi All, I have a table T1 with 2 columns SenderID varchar(10) CustomDate Datetime I want to display following outputs, I am planning to write a single query and avoiding temp tables etc... SenderID Total Month A 50 July 2005 B ...more >>

I do have an instead of trigger on a table. If it produces an PK error or another exception, it rollbacks the transaction that my app started (on Delphi 6) and i am unable to continue making statements on this transaction, because when i will do the commit, it says that there is no active transa...more >>

Hi, Current I have a query below that rotates a table the way I wanted. But now it gets more complicated. Instead of the first query returning one row, I don't know what to do if it's needed to return multiple row for a sub-query to grab the needed data. This is my current current w/ the d...more >>

I have a group of lists that I want to merge, and purge the duplicates. Question is how to do this? Can someone point me in the right direction? Any and all Tips welcome. Thank you, Kim ...more >>

Is there a fast way with TSQL get the numeric characters out of a varchar string? i.e. If I have a value of 'AB1234-56789_OLD' I want to get '123456789'. ...more >>

Could someone help me to understand the results below which is what I see when I run sp_who2. I don't understand why I get multiple entries for the same SPID and why serveral entries are blocked by the same SPID? Can anyone explain a likely scenario for this to happen? SPID Status Login Hos...more >>

I have some lingering questions to a previous thread called "XQuery and Hierarchical Data". Previously, I needed to know who the descendants are for each active property in the hierarchy and the corresponding allocation percents. Mark was very helpful in providing a good example. However, the...more >>

Hello, This is the structure of data in a SQL Server 2005 table. OrderDate Price Account UniqueId 1/1/2006 $5.5 1001 A11 1/2/2006 $6.6 1002 Z12 1/2/2006 $5.5 1001 Q13 1/4/2006 $2.2 1003 R14 1/1/2006 $1.1 1002 C15 1/6/2006 ...more >>

I did not write this stored procedure but have been asked to edit the code and display new data. The column name is H.BilletUOM. It only contains only three values. B71,B72, and B73. I was able to select the column in the stored procedure easily so the data is available for the report but the ...more >>

I'm fairly new to SQL Server 2005 so this is probably an easy question for someone. All I want to be able to do is create a stored procedure that can insert default values. I have a testing table as defined below: ------------------------------------------------------------------------------...more >>

Hi! i m using following query select * from Mytable where convert(datetime,report_date) between '10-Nov-2006' and '11-Nov-2006' it is returning me the error Server: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range ...more >>

Here is my code, please help me understand why this is happening, and ways to overcome this ===================================================================== set CONCAT_NULL_YIELDS_NULL OFF declare @fncmd nvarchar (150) declare @segfilename nvarchar (150) select @fncmd = N'select filen...more >>

Hi, I've a sql 2005 server. it's default installation (so it looks like) stores dates in mm/dd/yyyy format. my app and the rest of my country uses the dd/mm/yyyy how do I make the database to work in dd/mm mode ? right now I get errors when I use INSERT statements with an incorrect ...more >>

Hi everyone I have a table with numbers in multiple columns . I want to write SQL that gives the max number at each ROW ex: id,value1,value2,value3 thanks /Nettan...more >>

hi I dont know how to construct a query to calculate sub totals.. i ve a table called sales sno product_name quantity 1 prd1 5 2 prd2 2 3 prd1 1 4 prd3 3 5 prd3 1 6 prd2 5 7 prd1 2 8 prd1 1 I dont know how to construct a query to bring a recordset that gives a result like ...more >>

this is the most bizarre thing i've ever seen. I have a stored procedure that does a final select on a number of different tables. I have amended this and inserted another join like so: JOIN (select tab1.id,tab1.id2,tab2.col4 FROM tab1 LEFT OUTER JOIN (tab2 INNER JOIN tab 3 on ...more >>

All, I have a tsql question I have been puzzling over for a few days and would like you help! I have a table tblA: pkid CaseID StatusID StatusReasonID StartDate Preceedence 1 1 5 4 2005-01-01 d 2 1 2 4 2006-01-01 d 3 1 6 6 2006-11-13 c 4 1 8 2 2006-11-13 b 5 1 3 7 2006-11-13 a 6 1 4 3 2...more >>

Hello, I've a problem like this. A table with the following cols: PolicyNo kat Premium Premium2 Year Dola 1 A 1110 1100 2003 2004-12-01 1 B 1000 1000 ...more >>

Is there a way to have the results of an SELECT statement saved to a file? For example: SELECT field1, field2, field3 FROM MyTable FOR XML AUTO -- Here I wanted to save this to a file. Thank you. -- Peace in Christ Marco Napoli http://www.ourlovingmother.org ...more >>

Hi all, I'm primarily a middle-tier coder who's also administrating the database. I'm not writing any rollback/transaction/error handling code in my sp's. Should I? What are the advantages of inserting this code? What are the dangers of disregarding this code? Does inserting this code re...more >>

Hi all, I'm primarily a middle-tier coder who's also administrating the database. I'm not writing any rollback/transaction/error handling code in my sp's. Should I? What are the advantages of inserting this code? What are the dangers of disregarding this code? Does inserting this code resu...more >>

hi all, i want a case senctive query like this how can i do this select * from table1 where name='vipin' if in table there is name like Vipin then query return no row how can i do this ...more >>

Hello! I have a table with a field Date (Datetime). Everytime when I insert a new row into this table, the field Date inserted in this format 11/13/2006 12:45:59 AM . But I dont want the part "hh:mm::ss AM" to appear here. Because in some cases when I am trying to get report for some period (...more >>