Hi All, I am sure this has been posted in past but i am not able to find out a clear answer on this. i want to select and update from a table i one sql statement .is this possible? so something like this.. 1)select Item from ItemsTable 2)Update ItemsTable set IsItemInUse = 1 where Ite...more >>

Hi, I have the following table that is updated by 2 depts trxdetid amt shp crdt status 123 2 1 1 closed 123 1 Pending 124 2 2 closed 125 2 2 closed 126 3 Pending 127 2 1 open 127 1 1 closed I want to place a trigger on the table to execute after update and to only execute ...more >>

A colleague after much angst while developing a DB found he could not delete records from a table. The code below is a watered down version of the problem where: - Running on SQlServer 2000 SP3 - Two tables with the second referenced to the first and Delete Cascade set - The second table has 2...more >>

Hi friends Can someone help to write a stored procedure that will parse a comma separated text file and populate the same into a database table. There will be n number of records with two column values like.. 1, Me 2, You 3, We Thank you in advance Scotty....more >>

Gentlemen I need to find ou how much processing time has elapsed from the begining of a query to the end of a query and update a datetime database field with the results. DateDiff() would give me each part ( hours, min, sec) but it will not give it all together. H...more >>

I am having a problem running my selectes when I do a test like: select * from table a, table b where a.somedate = b.somedate The problem is the dates are the same, but the times are different. a.somedate = 2005-03-10 09:41:59.000 b.somedate = 2005-03-10 09:41:50.000 How do I do the t...more >>

Hi, I am using MFC class with ODBC to connect to SQL Server. Below is my partial code, when I do update(), I get an error "Invalid attribute/option identifier". -------------------------------------------------------- .Open(CRecordset::dynaset,"SELECT * FROM tblcomponentsInStream WHERE...more >>

Hi: "view" is a virtual table and can take data in different columns of different table. However, "Select" statement itself can do the same thing. For example, in my program, I can directly create a "select" SQL script and create a connection, a command, retrieve the data from the "select...more >>

Is it possible to take a parameter that comes into a sproc that looks like this: 'abc,xyx,ggg,ddd' and put the elements which are separated by commas into an array? Or is there any other method to take action on 'abc' and then 'xyx', and so on. Thanks, don sql 2000 or sql 7.0 ...more >>

hello, i am creating a trigger which gets executed when a new row is created in a table. when the row gets added i would like to query from a second table, and get a string value, and use it in my new row created. i can create the trigger, get the new row and put a hard coded value into the...more >>

my head a splode. so i'm finalizing the ugly transformation query, and i think i'm almost done. there's only two problems that i'm having trouble with. first, some ddl simplified samples for what i'm working with (please assume foreign keys and such by context where possible, i want to keep th...more >>

I have a sproc that does an http post and I am trying to add a datetime variable to the string, but it is giving me a terrible headache. Here is the declaration ........................... SET NOCOUNT ON DECLARE @vchURLToPostTo varchar(1000), @vchHTTPVerbToUse varchar(10), @iEntityID i...more >>

A developer I'm working with just gave me a stored procedure that uses three cursors, two of which are nested into the first. These cursors retrieve data from tables one row at a time to build temporary tables, which are used to build a result set. I told him not to use cursors because they...more >>

Hello, I have a question regarding one-to-one relationships that I have been debating with myself about for some time. I hope someone can finally shed some light on this because I keep going back and forth on this issue. I have a customers table which stores several columns related to inv...more >>

When I try to run sp_refreshview on a view that accesses a table on a linked server (after that table's structure has changed), I get the following error: Server: Msg 7391, Level 16, State 1, Procedure vwTest, Line 3 The operation could not be performed because the OLE DB provider 'SQLOLEDB' ...more >>

Hi as part of a dts package I'd like to check the count of some rows, and if a threshold is reached then (report success) and move to another task. It like to do this as a SQL task, is this possible if ((select count(*) from aTable where someCritiria) > 5) -- somehow report succe...more >>

Hi Im using ASP to insert value into SQL Server Code ----- Set CON = Server.CreateObject("ADODB.Connection") CON.open sqlConString Set adRS = Server.CreateObject("ADODB.Recordset") adRS.LockType = 3 adRS.CursorType = 3 adRS.CursorLocation = 3 adRS.Open "select * from PANTHER_U...more >>

Hi All, I have question about how to do select on below table. This table has 3column, Col1->Account Id[Values 1, 2, 3, 4] Col2>Code [Values 1,2,3] Col3>Amount[Values 100,200,300,400] Each code maps to debit, credit, balance.... I want to do a select which can return me debit, credit and ba...more >>

Is this a valid command? Select distinct empid, 'Altadd1' = (select b.add1 from address b where empid = b.empid and b.addtype = 'Alternate address') from emp inner join address a on empid= a.empid where a.addtype = 'Home' I used this in sql Analyzer works gre...more >>

Hi, I am querying SQL Server 2000 using ADO. I have a join on two tables each having fields with the same names. MS Access will include the table name where there is a confilct. Is there a way in SQL of forcing SQL Server to return the fully qualified field names to the ADO collections for ...more >>

Platform: Windows 2003 Server Background: App Server with a COM+ package exported. The exported package has been installed on a database Server. When using sp_OACreate pointing a a ddl/method in the package a negative error response is received, it evaluates to Access Denied. My question...more >>

Hi, I'm having a problem where a CREATE PROCEDURE call with syntax error in it is not being reported when included in a large script. If I create a script of the individual CREATE PROCEDURE call on it's own and run it, the syntax error is reported. I am running the scripts using Query Ana...more >>

I have 2 tables that I am trying to join and end up with 1 row that contains 1 row from the first table and multiple rows from the 2nd table. Something like: ClientID PayDate Code#1 Hours#1 Amount#1 Code#2 Hours#2 Amount#2 Code#3 Hours#3 Amount#3 Code#4 Hours#4 Amount#4 ...more >>

I have two fields called orderdate_d, clinetname, how can I retrieve clientname whose order date is from the 10th of last month to today? Thanks,...more >>

Since all the mums in their 4x4's bought all the petrol yesterday, I had to cycle to work which meant that my SQL Super Bible is still on my desk at home and of no use to me. I need some help creating a trigger for insert, update and delete. Firstly I want to populate a field in the curren...more >>

Hi all, This is a bit of a novice question so please bear with me. I have three tables tbCustomerDetails tbServiceReport tbPartNumbers The tbCustomerDetails table has a unique ID as its primary key, in the table tbServiceReport there is a field called flCustomerName that references...more >>

Here's a strange scenario. I'm using sql server 2000 sp4 on windows 2000 sp4. I've written a pretty hefty stored procedure I run the stored procedure once in query analyzer using the execute statement (ie... EXECUTE p_myproc @var) to build the execution path... same as running sp_recom...more >>

Hello group, I looking for idea how in one cell display many information. In my DB I have to connected table: TabGroup (idGroup PK, GroupName) : ex. records{(1, G1), (2, Team A) (3, Managment)} and TabPeople(idPeople PK, idGroup, FName) with ex. records: { (1,1,Tom), (2,1, John), (3,...more >>

You have a row of data set up like this a,b,c,d these are the columns. The values are a=3, b=1, c=5, d=4 and I need these put in descending order but this is one row and I need it to come back looking like multiple rows. How would you suggest I go about this? Shawn ...more >>

Dear All, I know how to parse this in ACCESS but not clear how to do this through stored procedure. I Googled with different search criteria but didn't get any useful hit. Any one have something handy? I would like to parse: "strFullName" listed as Habibullah, Mohammad I to three fields...more >>

Hi, We have a SQL 7 Server that someone has tried to put in to Single User Mode. They put the startup parameter as -M instead of -m The event log states that this is an incorrect parameter. Does anybody know how I can get the server back in to Multi User Mode? I can't start the SQL Ser...more >>

First off- thanks for everyone's help - this board is a great resource. I have the following query: SELECT COUNT(TRAN_ID) as Total, CCDORPPD FROM TRANSACTIONS WHERE STATUS = 'CH' GROUP BY CCDORPPD ORDER BY CCDORPPD Currently I get this as a result: Total CCDORPPD 82 ...more >>

I know cursors are controversial and can cause performance, etc. issues. The question I'm about to pose is because of performance concerns so please hear me out. I'm trying to get a better understanding of what the impact of round trips from the client to the server are, not the impact on t...more >>

Hi All I want to see how long it takes for my query to execute in QA. How do i do that? Thanx in advance...more >>

Hi, I have a developer who has written a view. Local execution of the view against local SQL Server resources in QA - Query Pane takes 13 seconds. Right clicking on the view in the Object Browser and selecting Open takes < 2 seconds. Yes both the Query Pane and the Object Browser are usin...more >>

Hi. I have a problem of sorts when selecting a set of records into a temporary table using variables. The variables are of datetime type specify a start and end range which are used against a datetime column in the database. Select statement is : SET NOCOUNT ON SET DATEFORMAT dmy dec...more >>

Hi, I have what seems to be a simple issue, yet I can't get it to work: I have a table that I have imported into SQL via a .txt file , one of the fields holds a date (which was originally in the format of '122499'). I added, based on certain criteria a '19' or '20' in front of the year to...more >>

Hi All, We have a table which looks like the following: ID (int) Status (int) Elgiblity (int) EligibleTime (datetime) Counter (int) Flag (bit) Typically our queries look like SELECT TOP 1 * FROM TABLE WHERE (Status IN 1000, 2000, 3000) AND Eligibilty = 2000 AND EligiblityTime < '0...more >>

Hi group. Where can i find in the system tables of the SQL 2000, all the propreties of an index. For example, if a create an index unique but not constraint and 'Ignore duplicate key' checked?. Where the SQL 2000 store this information? Thanks Nuno Teixeira Portugal...more >>

Hi every one I have this distributed transaction set @Exec = 'set fmtonly off;exec [SERVER].[CREDIT].dbo.sps_getAbonos '+ cast(@Camp_id as varchar(3)) +','+ cast(@int_zon_id as varchar(3)) +','+ cast(@int_sku_id as varchar(8)) +','''''+ cast(@str_Tipo as varchar(1)) +''''','+ cast(@int_p...more >>

Is there a way to set a text qualifier on data (let's say double quotes around the field data) that is exported with bcp? If so, which switch would I use to set the text qualifier? Thanks, Ron...more >>

Can I convert this IF-ELSE statement into a CASE statement? Thanks for your help! declare @source varchar(150) declare @destination varchar(150) if (select @@servername) = 'A' begin set @source = 'folder1\file.txt' set @destination = 'folder2\' end else if (select @@serv...more >>

Hi all, I am trying to launch a xp_cmdshell from a stored procedure to execute a BCP. This error occured when my sp is called : System.Web.HttpUnhandledException: Exception of type System.Web.HttpUnhandledException was thrown. ---> System.Data.SqlClient.SqlException: A severe error occurre...more >>

SQL SERVER 2000 Hello I have a table with a field like this 4 5 6 10 11 15 I want to get a result set of consecutive numbers like this (start range - end range) 4 6 10 11 15 15 Thanks in advance for your help -- Gil...more >>

Hi, I am running the following query: declare @SiteLogId int set @SiteLogId = 2833 SELECT PCT_RPT_Mashines_V.MashineRemarks, PCT_RPT_Mashines_V_1.MashineSDPDescription PCT_RPT_Mashines_V_1.MashineRemarks PCT_RPT_Mashines_V.PerId, ...more >>

I imported a DB table from Access to SQL Server 7.0. One of the columns in the Access DB stores date information but the datatype of that column has been assigned as Text. After importing the table to SQL Server, I tried to change the datatype of this column from nvarchar to datetime but SQL Ser...more >>

I have created a stored procedure in which the statements are EXEC ('CREATE TABLE #t(co1 int))') /*some statements*/ EXEC ('select * from #t') in same procedure But when i execute this procedure i am getting the error message as "invalid object name #t" I found where it went wrong....more >>

Hi friends, How to search all the SP's in the database to find if a particular work appears in any of the SP. Example: I want to find out what are all the SP refers "tblemployee" table. Thanks vanitha...more >>

Hi, I'm using SQL Server 2005 for a new project where we use the new XML features in the database. We would like to use FullText search to search the XML and devide the resultset using Row_Number(). I'm getting a strange error when combining these. Example: WITH TestRows AS ( SELECT...more >>

hello everybody, i have the following issue: - given a table of items, each item having a value - given a table of divisions for the items i need to obtain a set of divided items using this algorithm for each item: from item.value is removed the maximum number of the biggest division. from...more >>

Hi everyone, I have a categories table which has the following main attributes CategoryName, CategoryID <- Identity , ParentCategoryID What I need help doing is constructing a Procedure/ SQL query where I can show the expanded relationships for each record in the table. e.g. If I have thr...more >>

Hi there I'm trying to setup a proxy account for my sqlserver installation during the procedure listet below, but I keep getting stocked with the error message: The system cannot find the path specified. --- EXEC master.dbo.xp_sqlagent_proxy_account N'SET', N'MYDOMAIN', ...more >>

Hi all, I've got a couple of queries that I can't figure out exactly how to build them. Both go over the 'sys' tables. All my tables have the primarykey called 'id_no'. So if I have a table 'customers', the identity field is called 'id_no'. If I have a second table 'orders' with a foreign k...more >>

Dear Aall, How can we search for all the key words? Is it possible to write a single query with Freetext or Contains? Example: if I search for 'Microsoft has released a new Operating System' I should get the result which contains all the Key words in the phrase we search. Here in this examp...more >>