I need to do a subselect where the 1 column that is being returned is concatenated into 1 field with commas separating the values. I need to create a view to use this. Example: the values returned are "one", "two" and "three". I need that subselect to become a field in the main select wi...more >>

I need to extract a substring from the field 'Account' that is always between parentheses, but is not always the first text between parentheses: SUBSTRING(Account, PATINDEX('%(%', Account) + 1, PATINDEX('%)%', Account) - PATINDEX('%(%', Account) - 1) where Account='Patient (A) Name (PAT1...more >>

I supect it can be done but I don't see it scenario: User has Items and the first user is the Default user I want all new Users to have the same list of items as the default user Select * from Users right join Items on User.UserID = Items.UserID where User.LocalID = 0 ...more >>

HI All I am in NewJersy and i am willing tolearn T-Sql Programming,Any body can help me with learning,I am out os studies from long time,i read many books and articles but unable to understand.Please if any one in newjersy ,Ny helps m with this . Thanks ...more >>

In 2pc, after "PREPARE", tm is down. I want know incomplete transaction list. In Oracle , we can find incomplete transactions from dba_2pc_pending table. So, we can use "commit force..., rollback force..". I don't know method In Sql Server. ...more >>

HI, I want to write a sql query in a single line..That must be able to give the last date of that month. for ex. If i give 02/02/2006 my result must be 28/02/2006 please help me regards velmurugan.D ...more >>

Hi, I have a table like the one defined below DECLARE @businessunits table (BusinessUnitId INT, WorkerId INT, ClientId INT) insert into @businessunits values (73, 3702, NULL) insert into @businessunits values (73, null, 17039) insert into @businessunits values (74, null, 12445) i ne...more >>

Hi Guys I Have not been able to solve this problem from quiete a while now. I am using sql server 2005. I have got a table which contains these columns - start date, end date and volumes if the month in the start date is same as that of end date, the volume remains same, else if the...more >>

Can I please have help with code for the following. We have the same client companies based in a number of states. I have to provide a report for our sales manager that allows him to select which state to report on for a particular client. In the event he does NOT enter a state code, (the...more >>

I want to fetch a list of SPIDs and Last T-SQL Command Batch with a single script. However, my T-SQL skills are not very advanced, so I'm having a bit of trouble figuring this out. Below is my script, but it's not working as I desire. It's only half way complete. Can anyone suggest how I c...more >>

Hi guys, I have posted this in 2 groups hoping to catch up on some difference knowledge. I am making an application in c# with a SQL 2000 back end. I am trying to use the new dataset available in VS 2005 to auto-magically provide my data access layer. Unfortunately, in order to take adva...more >>

Thanks in advance. I'm in need of a way to figure out the max number of consecutive days an = employee worked and that time frame. Table looks like this: EmpID DateWorked Hours 1 9/15/06 10 1 9/16/06 8 2 9/10/06 7 2 9/11/06 8 ...more >>

I have a table with 3000 Client_id's. I have another table with 56 different order types. The order types primary key is two fields (search_id, item_id) I need to insert into another table each client_id, along with each unique instance (56 instances) of search_id, item_id. So the new ta...more >>

How would I convert these types of dates (Strings) using SQL or would I need to convert them before bringing them into a SQL DB. 1060828 1051107 1060828 1060828 1051107 1051107 1060828 Thanks in advance for your assistance -- Message posted via http://www.sqlmonster.com ...more >>

Hey guys i am doing something that is against everything i was ever taught but i can't find a better way so i ask your advice. I have a situation where a client can connect to my server and on disconnection must be reconnected back to where they were when they return. It is a game scenar...more >>

I want the lastest date result from same stock numbers, and distinct command not working with sql query, is anybody has solutions for this, because heres the millions of records thx. in advance.. -------------------------------------- stock - date - price --------...more >>

Hi, I am making a openrowset() call using "sqloledb", and I need to prevent it from trying a distributed transaction when I call it inside a normal transaction on my end. Distributed transactions fail, and I do not have any control over the remote server. In any case, i don't need a transa...more >>

Hi Guys, I have following data in table ID Month Year Hotel Number Revenue 1 1 2005 xxxxx $1234 2 2 2005 xxxxx $1235 3 4 20...more >>

How can I use a stored procedure that takes parameters as the @query parameter for xp_sendmail? I'm trying something like this at the moment and it's not working... SET @QueryString = 'sp_UWDailyReport @BeginDate = ''' + @Yesterday1500 + '''' EXEC master.dbo.xp_sendmail @recipients = @R...more >>

I have a report which has a field containing an "&" and I am trying to create a subreport off of this field which will show me the details for this one row. But when I click on the hyperlink, instead of passing this field to the subreport, it is passing the wrong parameters because the "&" is...more >>

I have the two tables below and my SELECT needs to return: BranchID ParentCompanyID CompanyID ParentCompanyName (company name corresponding to the ParentCompanyID in the Branch table) CompanyName (company name corresponding to the CompanyID in the Branch table) CREATE TABLE [tbl_Co...more >>

I'm working on a project in which I need to display a table of items and its properties. The difficulty is that the properties needs to be dynamic - as in, an admin can add new properties, delete, etc. Given that, I can't create a flat table in sql server with the schema corresponding to the p...more >>

Hi, I am working on migrating SQL 2000 to 2005. One of the SPs, which passes a temp local table to another SP, keep throwing error "Incorrect syntax near ','.". The same proc works fine on SQL 2000. I tried to run the called SP directly with the same input, it works as well. It just doesn...more >>

Hi guys, I have a reports table in my database where i am storing revenue collected for each particular month. Table structure ID Int(4) Hotel Number char(6) Month char(1) Year char(4) each row can be something like 1001, 1894, 1,2006 I need to writ...more >>

Hi! the Studio seems to run slowly. Specifically, when you try to update a table, it seems like it is hybernating. there is no HD activity or such think. after about 20 seconds, it comes back and performs the function. in an earlier post, someone suggested to go into IE and turn off a fla...more >>

hey all, is there anything in SQL Server 2000 Enterprise Manager that generates a backup and restore script for you? Sql Server Agent? thanks, rodchar...more >>

Is it possible to select a range of values for a SELECT WHERE IN statement? I want to do something like: SELECT val1 FROM table WHERE val2 IN (select val from table2) Thanks, David ...more >>

I am beating my head on the desk trying to figure this out. Can anyone help? Here is the Table: unitinfo Loccode idrma ups shipdte ascname ETC...... 1 a 11 ... ... ... 2 aa 22 ... ... ... 3 aaa 33 ... ... ... 3 ...more >>

I posted this already, but it appears have disappeared into cyberspace. Anyway, hopefully it won't appear twice. I've got a SS 2000 server that has a sqlagent.out, sqlagent.1 and sqlagent.2 file on a drive that I need to retire. I can find that indeed the location is pointed to that drive ...more >>

Hi I have an auditing functionality on a table implemented with an after trigger. The trigger fires and updates a field in the table with datemodified and copies old data to an audit table. Is there a way to write somethign similar to: Update table (don't fire trigger) set field = 'blue'...more >>

Hi, Sorry for the re-post, but my original post appeared in the wrong thread. So here it is again. ----------------------------------- I need to remove duplicate rows from a table. The process that I am using, which is shown below, is extremely slow - it takes about 3...more >>

Hello , Can some on help me with the subquery select part_number, AttributeValue >From PartAttributes Where convert(Int,AttributeValue) <= 5 in (select AttributeValue from PartAttributes where isnumeric(attributevalue)=1 and AttributeValue <> '-') here is the error I am getting ...more >>

Hi, I need to remove duplicate rows from a table. The process that I am using, which is shown below, is extremely slow - it takes about 30 minutes to process 2000 rows. Can anyone point out any obvious reasons that my technique is slow? - or perhaps propose a better technique? ...more >>

I've got the above file (on SS 2000) on a drive that I don't want it to be on. I found in the Properties of the Sql Server Agent in Enterprise Mgr that it is, indeed, pointed to that drive. But I can't find in BOL or on Google how to change it. Is this a property of the service, i.e. is it ...more >>

I am designing a new table. The final output from the application needs to look something like: Patient (Med1) (Lab Value1 for Med1 ) (Med2) (Lab Value 1 Med2) ...(Med N) (Lab Value 1 for Med N) (Med1) (Lab Value2 for Med 1) (Med2) (Lab Value2 Med 2) ...(Med N) (Lab Value 2 for Med N) LE...more >>

We are going to have a table that store about 300 bit fields (answers to about 300 yes/no question for a client). I am curious as to whether I need to split them based on the size of each row. How are mulitiple bit fields in a table stored in SQL server 2000? Also if each field allows null ...more >>

Hi, I have a table that has the following columns id int, Name Char(20). When I insert a row into this tanle i get the max value of ID and increment it by 1 then INSERT a new row to the table. If I use a begin Transaction with the INSERT what would be the value of ID if one user is inserting...more >>

Here's my situation: Table ID NAME --------------- 1 Mike 2 Sam 3 Jim Two queries: 1. Delete from Table with (rowlock) where ID=1 2. Update Table with (rowlock) where ID in (2,3) Problem: Update statement is waiting for delete to complete!!! Why??? I spec...more >>

The Microsoft OLEDB provider for DB2 (version 7.0.2413.0) is available for download in the "Feature Pack for Microsoft SQL Server 2005 - April 2006." The installation routine, however, is restricted to enterprise edition or developer edition. Is DB2OLEDB no longer supported on standard editi...more >>

I am trying to create a priority field to organize a list of tasks. The following 3 fields are being used: EventID (PK, int, not null), Name (nvarchar(50), not null), pri_ss (int, null). It would be nice if the code did several things at once: 1. Automitically add items to the list with the ma...more >>

Hello , Here is the SQL I am trying to run against our database. ***************************************************** SELECT * FROM Part WHERE (Company_Abrv = 'TSBI') AND (Catalog_PartType = 'Component') AND (TypeName = 'Generic') AND (Version = 'Space Holder') AND (KeyComponentName = 'S...more >>

I have a numbering system in the business that allows values such as 01,02,1,2,3,1.1,1.2, 1A,1B, 1.3A, 1.3B. To cater fior this I have used varchar(5) as the data type. You dont need to be an expert to work out the logical order that these codes should be displayed in, it is exactly as you wou...more >>

Hi, I tried to execute the following code but it doesn't seem to work: "SELECT * FROM dbo.udf_data( udf_GetPreviousDate('20060812') )" The statements "SELECT udf_GetPreviousDate('20060812')" and "SELECT * FROM dbo.udf_data('20060811')"" work perfectly on their own. Any advice would be ...more >>

Hey all Can somebody tell me why im getting the error message ON UPDATE CASCADE ) * ERROR at line 9: ORA-00905: missing keyword .. The coding is as followed CREATE TABLE SerPriceHistory (PartNum CHAR(6) CONSTRAINT SerPriceHistoryRequired NOT NULL, PriceHistoryNo INTEGER, P...more >>

Hi I have a single table with non relational data that i want to query, most often the query will only contain data for the last week(s) but sometime the query needs to return several years of data. The quantity is differnt for diffrent implementations the standard customer has about 500...more >>

Hi, pls. see this code: DECLARE @SQL nvarchar(4000) SELECT @SQL = 'SELECT * FROM CUSTOMER WHERE CustomerName = N''Trần Tấn Hận''' PRINT @SQL The result is this always: SELECT * FROM CUSTOMER WHERE CustomerName = N'Tr?n T?n H?n'...more >>

Hi, I've just started working with the SQL Server 2005 and i'm using the UNPIVOT function, which i think is a really powerful feature. My problem is that i'm trying to create a SP which takes a parameter containing a comma-delimited NVARCHAR with the columns which i want to extract. Here is ...more >>

Morning, I am trying to put together what should really be an easy select statment and the only way that I can do it is rather messy, below is what I am after in a simplified version I have a table with a list on numbers in it from 1 - 80 i.e. Num 1 2 3 4 5 and so on The nu...more >>

Hello, I'm trying to see how to migrate from oracle to SQL server 2005 and I have a problem with an sql query basically this is how it looks like in oracle select count(*) FROM Table1 eu, Table2 ec, Table2 ec2 , Table3 esp, Table5 we WHERE ec.CommSeqNo = '24' AND eu.UserSeqNo = ec.UserSe...more >>

Hi I am trying t use TEXTCOPY.exe to import small photos into an IMAGE column. Most of the photos are JPGs. When I run textcopy, it inserts data into the IMAGE column, but this then appears as long binary data and does not display in my form's bound object frame. Am I missing somethin...more >>

Hi, If my team of 5 developers each has a sql server 2005 developer licence, does that mean we can all connect to the same instance on a server (basically as though it was SQL Server 2005 enterprise pre-production). Or put another way, is there a limit on the number of developers that can co...more >>