Hi We created a view on two tables and then created a Instead Of Insert trigger on the view. Inserting into the view on the local server works correctly and the trigger fires. However, if we attempt an insert into the view across a linked server, it appears as though the trigger does not fi...more >>

I have a simple 2 table database that I access from VBA in Excel 2003. One table has an OwnerId as well as a ReporterId which refers to a UserId in the second table. In addition to the UserId, the second table has user permissions and a VisibleName. I would like to write a query that will ...more >>

For reasons that are not relevant (though I explain them below *), I want, for all my users whatever privelige level, an SP which creates and inserts into a temporary table and then another SP which reads and drops the same temporary table. My users are not able to create dbo tables (eg dbo.tb...more >>

Hi, I am selecting data from one column (COL2) and would like to insert it display it as COL1 COL2 1 A 2 B 3 C COL2 is the column selected but I want COL1 to dispnay as incremented....more >>

HI! I'm using something like query in vb.net 2005 connected to SQL Server 2000: Select field1, field2 from (select subField1 as field1, subField2 as field2 from table1) and I can't run this query from vb.net using a dataset, this query works fine in sql server, but, when I execute this q...more >>

Hi, I would like to output the contents of a table to an XML document. I have a book that says that the ROOT keyword will tell SQL 2005 to make a well formed XML document. My SQL statement looks like this... Select * from Table1 FOR XML AUTO, ELEMENTS, ROOT('XMLTable1') ...more >>

SQL 2k. Given the following: CREATE TABLE Customers (GroupingNum INT, CustomerName VARCHAR(50)) INSERT INTO Customers (GroupingNum, CustomerName) VALUES (0, 'Customer Name') -- this is a row of column headers ALTER TABLE Customers ADD ID [int] IDENTITY (1, 1) NOT NULL -- for the sake o...more >>

Hi, How come I can't find something similar to SQL 2000 in a table --> All Task --> Generate SQL Script in 05? I want to generate a script for a table but I remember in 2000, there are so many options like Scripting the Indexes, Foreign Keys, etc along with the table. where are all those ...more >>

Hi all - I am trying to search my sequel server sql database depending on the values selected in drop down combo boxes and text input into a web based text boxes. I would like to define a single sql statement which will search the database whether all of the parameters or only some of the input ...more >>

Hello, I have the following scenario: two Tables A and B Table A contains about 4 000 000 records. Table B contains about 32 000 000 records. I try to delete all unreferenced records from table B. (Table A references table B) A.bid -> B.id Table A and B both have primary keys. Tabl...more >>

Hi, is there any easy function to get me the position of the letter in the text... something like select instr ('asdfg','d') result 3 thanks in advance Z...more >>

How can I stop a SQL Server 2005 job that is hung programmatically? Or is there an advanced setting. For example, I have a process that normally runs in 2 hours pulling data from a DB2 data source, and it it takes more than 4 hours, I have a strong suspicion that it is hung and would like to a...more >>

Hi. I am admist SOX as many are. What I am looking for is NOT the date a proc was created but the last time it was changed. Any ideas? I looked and test sysobjects but that didn't work. Thoughts? Craig ...more >>

I have installed 4 installations on 4 seperate machines of SQL 2005 and applied service pack 1 for SQL2005. On 2 of these machines, if I use the copy database wizard, it works fine whether I schedule it or run it imead. On the other 2 machines, the wizard errors out (both computers) at the c...more >>

Hello - I need code to handle the case of Divide by Null or Zero for the following: SUM(CASE WHEN l.DispositionID = 0 THEN 1.0 ELSE 0.0 END) / SUM(CASE WHEN l.LoanTypeID < 3 THEN 1.0 ELSE 0.0 END) (I saw the previous post regarding null and zero, however I don't understa...more >>

Hello guys, I have a unique requirement to take the backup of a database using a BATCH file. Once the backup is taken, I need to restore it using the same BATCH file. Do you know how to write the script for these batch files? Any help would be greatly appreciated....more >>

Hello, I have not seen anything that describes exactly what I am trying to do (one person's question hit it on the head but had no answer). I basically have list with many columns. I have sorted this list so that all ID's are grouped together and so that the first record in each groupi...more >>

Hi All, I have a note field varchar(1000) containing extra data that I want to strip out like <div> </div> <strong> </strong> How can I clean this? It's an easy way? Any links? Tks JFB ...more >>

Hello, I have not seen anything that describes exactly what I am trying to do (one person's question hit it on the head but had no answer). I basically have list with many columns. I have sorted this list so that all ID's are grouped together and so that the first record in each groupi...more >>

Hello again, everyone! I'm currently attempting to rebuild some of our old SP's that use xp_sendmail for SQL Server 2005. While doing so, i've managed to get them all to work! However, I have a problem: Whenever I pass a @query parameter, it always returns the row(s) affected from th...more >>

SQL 2k Is it possible to export a temp table to a tab delimited text file from a stored procedure? I know I can use bcp.exe (which works nicely and seems to be very fast) but it would be more convenient in my current situation to export directly from a procedure if it's possible. I tried to us...more >>

Hii Guys, I have question about IP address. I have a column containing IP Address, Domain(ie, aa.aa.com) and other data. Now I want to write a sql statement to check if it is IPaddress, then do something, otherwise do others. Can somebody have the easier way to check if it is IP address? Tha...more >>

I'm trying to delete records fromt the following table (activitym1): thenumber - varchar(50) number - varchar(60) type - varchar(60) datestamp - datetime operator - varchar(60) description - varchar(2000) negdatestamp - datetime sysmodtime - datetime sysmodcount - float sysmoduser - varc...more >>

I am trying to CASE a phone number where it might be blank and/or NULL and this syntax is NOT working. CASE APPRFEE.phone_pre WHEN '' WHEN NULL THEN '' ELSE '(' + RTRIM(LTRIM(APPRAISR.phone_pre)) + ')' + RTRIM(LTRIM(APPRAISR.phone)) END AS 'Appraiser''s...more >>

I have heard there exist code generators for standard crud like stored procedures. Can anyone recommend some good ones I can introduce into a build process? Most of the stuff being done here is straight forward CRUD processing and would like to do it through stored proc's. -- Ed...more >>

I have a table with data as follows: Date Amount Date/Previous Dates Amount 11/18/06 25.00 X 11/15/06 100.00 Y 11/13/06 23.00 Z In the third column I want to divide the current date's amount ...more >>

How can I concatenate data to a ntext column? In this case, I do not want to do it in the front end as I am writing the dataset to an XML file. select id,name,'<CDATA[[' + customer_page + ']>' from customer * customer_page is ntext....more >>

Can anyone tell me why I keep getting the following error on one particular database when running the index tunning wizard. "The workload file does not contain any events or queries that can be tuned against current database." It works fine againat my other databases but for this one I have...more >>

If I run sp_lock and it shows for a process the tempdb as the database with 0 for objectid and Indid with pag for Type and resource '1:87' and 'X' for mode and status 'grant'. How do I figure out what this lock is if there is no objectid No. showing. Thanks for any help. Sammy ...more >>

Hi, Please can you help,. all I want to do is to alter a table so that an existing field is set as the primary key. The code is: ALTER TABLE iras.dbo.USys_tbl_Temp_Site_Output_SQL ALTER COLUMN Client_Site_ID ADD CONSTRAINT Client_Site_ID_pk PRIMARY KEY; Can you see anything wrong with ...more >>

I find that when I divide 2 variables of the numeric data type I loose alot of precision. When I execute the sql bellow I get 0.33333300000000000000. Shouldn't the answer have 20 3's behind the decimal? I don't seem to get any loss of presicion with multiplication for example. declare @a num...more >>

Hi friends, I would like to know about the SP(Stored Procedure) or a SQL Query which will copy data from: DB1 -> tbl_alm3 TO DB2 -> tbl_alm3 Table Structures in both the Tables are as follows: EmployeeID | EmployeeName | Car ----------------------------------------...more >>