....but too difficult for me (obviously). Take the following test trigger: Create Trigger "xxx_Trigger1" On dbo.xxx For update As declare @id int SELECT @id=x.ID FROM xxx x inner join inserted i on i.id = x.id Inner join deleted d on i.id = d.id if update(x2) begin UPDATE xxx set X3...more >>

I've been trying to solve this for a couple of days but have not found an answer. In Access 2000, I would like to call Stored Procdure SP_B, that returns Columns A, C and E generated from Store Procedure SP_A (requires a parameter like @ComputerID='SMITH') that creates columns A, B, C, D, E, F...more >>

I have a problem with a stored procedure SELECT CON_ID, CLI_ID, CON_LastName FROM dbo.Contacts WHERE (CON_ID = CASE WHEN @CON_ID = 0 THEN CON_ID ELSE @CON_ID END) AND (CLI_ID = CASE WHEN @CLI_ID = 0 THEN CLI_ID ELSE @CLI_ID END) The problem I get is that it is possible for ...more >>

Hi, I wanted to know what happens in the following scenario. Please give me an example also. (1) Let us assume I have scheduled a job to run for every 5 mins assuming it takes only few seconds for executing. Suddenly if the job takes longer time to execute ie., 7 mins..what will happen?Wi...more >>

hi, some one nows the best way to monitoring a process....more >>

Hi All. I Have a specialist ODBC Driver named QODBC for the financial package Quicken (Quickbooks actually) What I Am wanting to do is write a stored proc that can insert records into the tables made available via QODBC. QODBC functions just like any other ODBC driver so is there...more >>

Hi, I have a stored proc that searches for books. You can search for books using a variety of search conditions such as Title, Author, ISBN, etc. However, I want to also give users a way of uploading a text file of ISBN numbers that will allow them search for only those books they're lookin...more >>

Hello there I have table that when it updated i need to keep the old record on a history table. For this i used the updated trigger But the update occur always even if no data was updated Is there a way to know if an update realy occur? any help would be useful ...more >>

Hi everybody I have a table like this: 1 pippo.com 2 pippo.net 3. pippo.co.uk I need a query that returs the following 1 .com 2 .net 3 .co.uk I was using this: right(tx.ext, charindex('.',reverse(tx.ext))) in the SELECT but it doesn't work for record nr. 3 Can you give me any help...more >>

Please provide atleast 3 differences between Primary key and unique. thanks ...more >>

i have a database table as below district sub_total purchase_date loc1 20 2004/7/21 loc1 10 2004/7/31 loc1 5 2004/7/25 loc2 20 2004/7/23 loc2 10 ...more >>

Hi, can u give me query for this: List the names of all those cities which has more vendors operating than the number of customers. table structure: CUSTOMER(Cust_id,Name,Address,City) VENDOR(Vendor_id,Name,Address,City) Thanks in advance Rgds, Suresh ...more >>

Hi I have a series of tables linked by a 'Mapping' table. The Mapping table links objects on one table to objects on another. Each row has a GUID as a key. Aside from the key, each row has two more rows. One for GUID1 and one for GUID2. GUID1 and GUID2 represent the keys for entries in ...more >>

Hello, all. We created two tables, tblGeneralPerson and tblValidPrefixes, and then tried to create a foreign key relationship b/w them, such that the prefixID is the PK in tblValidPrefixes and becomes an FK in tblGeneralPerson in the genPrefix field. Somewhere along the way, we deleted the re...more >>

Hi, Anyone know why my Case statement does not work? SELECT * INTO #DialResults FROM DialResults WHERE CampaignID = @CampaignID CASE WHEN @StartDate IS NOT NULL AND @EndDate IS NULL THEN AND StatusTime >= @StartDate WHEN @StartDate IS NOT NULL AND @EndDate IS NOT NULL THEN...more >>

Hi I am running a large batch update of a series of stored procedures on my SQL Server database using the command line OSQL.exe application The problem i have is: In one of the stored procs I concatanate a series of text strings with the character '¬' so that i can split the string when...more >>

I can insert into an existing table with: INSERT INTO #tmp EXEC ('sp_GetOrders') But can I create a table on the fly using the output from a stored proc? ...more >>

These are my columns in table wo_main total, sweeper_total, trackless_total, misc_total, site_id, wo, trackless_wo, sweeper_wo, misc_wo I need to create a select query that does all of the following sums this into one number total where wo = Yes sweeper_total where sweeper_wo = Yes ...more >>

I have two questions regarding a situation that we ran into (and solved) recently that have got me wondering what's really happening. 1) We ran into a stored procedure that seemed to be exhibiting a parameter sniffing issue (sorta). The stored procedure was performing poorly in that it took a...more >>

Hi! May I know what function can I use, Use to disconnect a particular user from current database ? Thanks and Regards Mark.. ...more >>

Hi, I got the xp_SMTP_SendMail to work, but the server is always default it = to 'smarthost'. Since I know my SMTP server, I override @Server with my = server or something like 'smtp@mydomain.com'. How do I get the SMTP = server from SQL Server? or is there an API call? Please let me know. ...more >>

Hi All I have this TSQL statment that i use a fare amount in my Stored procedures and triggers. DECLARE @lCounter Float(30) SET @lCounter = 0 UPDATE tblTEST SET @lCounter = InstructionOrder = @lCounter + 1 All this does is Change the data in the field InstructionOrder to whole Numb...more >>

Hello, I got a mispelling that sits in my a text column for many rows. Each cell might contain more than one occurence. I haven't been able to find out how to replace them all with a correct spelling. I tried one single UpdateText function call and it didn't do it for me, it only replace th...more >>

Q1) i have a table like this: News_Category News_Title Date 1 bla-bla 23/8/2004 3 bla-bla 22/8/2004 1 bla-bla 17/9/2004 2 bla-bla 16/8/200...more >>

I've got a report that filters out the data that is not linked into an 'Other' category, so I can examine the records, and find out how to handle them. Upon looking, however, many of these Other records should be linked in the join, and put into proper categories. If I actually open up one...more >>

hi, Sorry for this dump question. How am I able to creat a CrossTab Query in SQL Server? CustomerName Date Amount John 5/1/04 100 Peter 6/1/04 200 John 6/11/04 100 Peter 6/10/04 200 to b...more >>

Need to load tables from Oracle. Dates are a REAL problem. I've tried DTS with little success due to different date formats and multi dates within a record. Record-by-record handling programmatically seems inefficient. -- Thanks Andy...more >>

I've got a webpage that calls some stored procedures with input variables. The procedures return recordsets and also some output variables. We're trying to get the values of the output variables. I've done this using the method I found in MSDN, as shown in the code below. The problem is that we ...more >>

I am new to SQL server and I am looking for the best practice regarding the following: The GUI for the database I am creating has a number of questions like: A. Where are you currently living? 1) In my own home 2) In the home of a family member 3) In a shelter 4) On the street However, f...more >>

I'm trying to convert this script to single update command, but cannot figure it out. Script runs 36 seconds against my db I need to speed it up Thank You declare @i int, @tot int, @fr char(4), @to char(4), @dist int update lotusdoormap set workdist =0 set NOCOUNT on set @i...more >>

Hello! I want to do as simple thing as check through a table if the date of a datetime column is today, but I can't figure out a simple way of doing this. The query SELECT * FROM TASKS WHERE RemDate=GetDate() doesn't give me everything for today. I think the time is the problem. Regards...more >>

I'm trying to build a WHERE clause parameter for an integer column. Basically its so i can do a query such as SELECT * FROM Brands WHERE BrandID IN (12,31,41,120) I'm trying to do the following: DECLARE @Brands varchar(100) SET @Brands = '12,31,41,120' SELECT * FROM Brands WHERE BrandID IN (...more >>

I have been tasked with producing and Entity Relationship Diagram (ERD) = on an existing DB. The DB has about 70 tables. There was not even one = foreign key relationship - nor any constraints to speak of - no primary = keys or unique keys. There are about 165 indexes. =20 I was thinking...more >>

Hi Does anyone know a fromula where if you passed in -1 and 1 you would get -2 and -1 respectively? For eaxample: -1 ??? = -2 1 ???= -1 I also need a similar formular where you can get 1 and 2 from -1 and 1. -1 ??? = 1 1 ??? = 2 Kind regards, Carl Gilbert ...more >>

I am in need of using the word "Size" as a column name and am wondering if this is a bad idea. If I were to do this then the correct way to write the tsql would be [Size] right? What is everyones opinion on this? Regards, Aaron...more >>

We currently have a production environment where all stored procs must include some standardized statements to read a debug table, and if the debug table has an indicator set, then the stored procs will insert some debugging messages and error codes into some other debug table. The goal of this ...more >>

does anyone know if it's possible to create an order by clause dependening on the parameter passed in the stored procedure. ------------------------------------------ @orderby char(10); AS Select * from JOBS where JOBactive=1 order by @orderby go does anyone know if that's pos...more >>

Hi, I have relatively less experience to SQL. I had a question. Say I have to display certain records on a datagrid. this datagrid is dependent on these parameters. suppose a user enters a partial value in a text box. eg: "12" I make use of "like" feature(...partID like '12%') in the query ...more >>

Is there a way to set the program name for a connection so that it is a bit more descriptive than ".Net SqlClient Data Provider"? Thanks, Charles ...more >>

Hello folks, I have a .Net application that executes a store procedure, the code used to execute fine until I added a clustered unique index to one of the view .This view uses the base tables that I am trying to insert and delete during my execution of Store procedure. It throws up and erro...more >>

Hi Can I turn of a trigger in a stored procedure. I don't want it firing if I am running this one stored procedure. But i do want it to fire for the rest. Can this be done and how? Thanks, Ian ...more >>

Hello there I am trying to run xp_sendmail with an attachment. I have two attachments file1.txt and file2.txt, but I'd like implement in the xp_sendmail a condition where if file1.txt does not exist still attaches file2.txt and send the email Here is the sql i wrote so far, and is not q...more >>

Hi, Do anyone know how to created calculated field in stored procedure. A field need to be returned whose value depends on other field in the row. simple example Table fields: name, sex, DOB Expected result: name, sex, age where age is the calculated field based on DOB field Any...more >>

I need to be able to generate the "CREATE PROCEDURE" SQL scripts from within a C# (or VB.NET) executable for all of my SPs. The result should be the same as if I had gone into Query Analyzer and done a "Script Object to File as: Create" For all of the user-defined SPs. Is there a way...more >>

I have two servers running on Windows 2003/SQL Server 2000. On one server - I am able to load my extended stored proc. But on my second server - the exact same code will not load. I keep getting the "216 - Unable to load code module" error message. Any thoughts on what might be dif...more >>

Hi, I have posted several questions to: microsoft.public.sqlserver.reportingsvcs But I can't see them. Is there any registeration required for using that newsgroup? Is that newsgroup available for public? Thank you, Alan ...more >>

Hi There! I have found some thing strange today and wanted to share with you guys. I wonder if you have any explaination for this behavior. In the following query, in the @sysname field I would expect to get a value if there is any if not, i should get BLANK. To my surprise if the query do...more >>

I use the Diagrams to print out the design of tables. What can I use to print out the design (col name, etc.) for a View? Thanks. David ...more >>

ID Month Value 1 2 100 1 3 100 1 4 200 1 7 300 2 1 400 2 2 500 2 6 1000 Results should be ID Value 2 1300 Month IN (1,2,3,4,5,6) Sum(Va...more >>

Hello, Is it possible to have the SQL server pick up messages from MSMQ and store it in a table, if so - how? Thanks in advance, Christian Davidsson ...more >>

Given a list like "11,12,13,14,21,22" is there a way to retrieve a list element given an element number (2 would retrieve the number 12 because it is the second element in the list.) Also I would like to retrieve the element number given the element (22 would return the number 6 because 22...more >>

Hi I'm having a issue with join. This is how my tables look Table1 EmployeeNr Key , EmployeeName and many other fields Table 2 EmployeeNr,ActivityNr(Keys), Start date, End date If an employee has started an activity then an entry is made into the Table 2. An employee does say 5 ac...more >>

Let's assume I have this docuemnt [copied from a message by another user w/out permission]: <Rows> <IDNumbers> <ID value="4"/> <ID value="19"/> <ID value="42"/> </IDNumbers> <IDNumbers> <ID value="5"/> <ID value="14"/> <ID value="2"/> </IDNumbe...more >>

I have an SQL Server 2000 SP3a running on Quad Proc (3.2 GHz), 4 GB RAM. This machine has only SQL Server 2k and Virus Scan installed on it. This SQL is used as a reporting SQL Server and the requests are coming via WebFOCUS, ASP etc. CPU is most of the time in high nineties (96% or so) ...more >>

We had a table M_Position With position as Primary Key, In that table we also have one column as grade. The table M_Position had foreign key in couple of other tables. Which was working fine. Now we had to change the primary key of the M_Position table to Grade and Position To Achive this we r...more >>

Hi, I'm recieving this error when I run a Query, using the Query Analyzer [Microsoft][ODBC SQL Server Driver]Unknown token received from SQL Server [Microsoft][ODBC SQL Server Driver]Invalid cursor state [Microsoft][ODBC SQL Server Driver]Unknown token received from SQL Server [Microsoft...more >>

I would like to add 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED' to my Select stored procs. When I alter the stored proc, I refresh and reopen and it is not showing up in the stored proc. Can someone please give me an example of the syntax for this? How do I check if the Isolation level ...more >>

How can i have a AutoGenerated field in a View ` i.e if a have table with eno , ename 200 'abc' 300, 'xyz' and if i have a view on it create view emp_vw as select eno , ename from emp the output i want is something like this sno eno , ename 1 200 abc 2 300 xyz sno i...more >>

I've been using C# transactions for a while and had no problems with them. Using try catch blocks I can trap basically all possible errors and rollback all necessary data. Over the last few days I've been trying to convert some of this code to SQL Server stored procedures, but it seems to lac...more >>

hi, i have a query that goes something like : select StringA as str from tableA inner join(s).... where..... union select case aNumberVariable when 0 then stringB when 1 then stringC else stringD end as str from tableA i don't mind about the order of the 1st select records but i ...more >>

What is wrong with this statement , i'm getting an incorrect syntax near keyword 'into': select into temp_cont_role from (select '1' roles,'!' t2, 'r25' t3,getdate() t4, petjtle_orgn_title t5, 1 t6,'2' soles from temp_roles,control where not exists (select 'xxx' from cont_...more >>

Hello All, I have a problem with this query: SELECT a.col1, a.col2, b.col3, b.col4 FROM tableA a INNER JOIN tableB b ON a.id = b.id INNER JOIN (SELECT * FROM tableC c WHERE c.type = 1 AND c.col5 < b.col4) temp ON temp.id = b.id In this query it is not allowing me to refer b.col4 insi...more >>

Hello Again Newsgroup I have created the script for an Update Trigger, which includes the following Statement: - IF NOT EXISTS (SELECT ytd.TASK_ID FROM McAlpineReports.dbo.YearToDateFigures AS ytd WHERE ytd.TASK_ID=@TASKID) BEGIN INSERT INTO McAlpineReports.dbo.YearToDateFigures (...more >>

I have one table with the 'parent' set of data for which I need counts (tbl1). The table containing the results (tbl2) may not have rows matching every item in tbl1, but I'd like to show 0 if there's no match or null. There's gotta be a way to do this, right? Any assistance greatly ap...more >>

I have a stored procedure that I am trying to use transactions with. Can anybody tell me how to trigger a rollback when the stproc is running so that I can test my code is OK? I tried to remove the records to be updated from the relevant tables but this does not cause a rollback. CREATE PROCE...more >>

Hi Experts, I changed the sa password and this contains strange ascii characters, when I'm trying to change the password of the register server (using Enterprise Manager), it does not accept it. I copy the pwd and paste it, I don't know if this cause a problem. Can I Re-register the se...more >>

Dear All I am running a DBCC INDEXDEFRAG command, when I noticed the the 'Pages Scanned' was zero. My syntax is DBCC INDEXDEFRAG (MyDatabase, MyTable, MyIndex) Can anyone tell a solution ? Thanks Julie...more >>

Hello All, I have a table like this: SELECT employee, colA, colB, week, Year FROM tableA employee colA colB week Year 100 a11 b11 52 2003 100 a11 b11 4 2004 100 a12 b21 5 2004 100 a22 b25 6 2004 200 a11 b16 2 2004 200 a19 b28 5 2004 200 a25 b25 10 2004 300 a41 b06 2 2004 300 ...more >>

Dear all: In specifying a unique field that may have multiple Null records, the null is a treated as a value and therefore the constraint is violated. How can wwe specify that unique values are excluded from unique constraint checks...more >>

Dear all: How can we specify that a trigger fires for each row? Thank you MAS...more >>

Dear all: Is there a way to have a trigger fire before event. Thanks MAS...more >>

Hi, In hash joins, how the hash value is computed? For example in this query: SET SHOWPLAN_ALL ON select c.customerid ,o.orderid, o.shipcountry from customers c right outer join orders o on c.customerid=o.customerid and o.shipcountry='germany' How the fields those appear in HASH:() ...more >>

Good Morning NewsGroup At the end of this message is a section of Code I have written for an UPDATE Trigger. The problem I am having is with the "NOT EXISTS" Sub Query, when I attempt to compile the Trigger I get the following Error: - Server: Msg 156, Level 15, State 1, Procedure U...more >>