Hi, For some reason or the other I just can't remember how to get the correct result. I am sure I have done similar queries hundreds of time, but today I can't. SELECT intDrugNameID FROM tblClinicalTrialDrugs WHERE (intClinicalTrialID = 8075) returns 59 140 When I run query SELE...more >>

Hello I have 3 tables CREATE TABLE #Players ( Player char(1) ) CREATE TABLE #Scores ( Score smallint NOT NULL, Player char(1) NOT NULL ) CREATE TABLE #Knockout ( Player char(1), Player_Opponent char(1), Player_Winner char(1) ) INSERT INTO #Players(Pl...more >>

Hi, I have a need to insert and update 50K to 100K records at once in database tables. I am getting deep in to performance issues. Its taking a long time to insert and upate records through ADO in VB 6 application. Can anyone help me? Any tips... any alternatives... should I use SQL DTS Dat...more >>

Hi all - I know that this is going to make me look like a dunderhead, but my t-SQL is rather rusty. I am using SQL server 2000 I have a simple table (Fee Details)with 3 colums([Item Number] INT, [Billing Code] nvchar,[Fee]money) The Item Number refers to the service supplied, Billing Code what ...more >>

Is there a way to write an update trigger so that it changes the a column in the current record? I want to datestamp records but don't want to do it in my front end application and would rather implement it at the server. Thanks dlie...more >>

Hi, I have to log changes to certain fields in my table as the values of these fields change, but I want to make it flexible so that end users could specify the field names that they want to track changes and they would write these field names into a table, I would have then a trigger on a da...more >>

I have created one trace with Security Audits event only and about 10 data columns and no filter. Which runs fine SQL profiler. I just want to trace users activity on server. But I want to keep this trace running on the server. How can I setup to run trace on server from client. I have admin rig...more >>

Theere are two sql server in different city,I connect then with VPN, then I use a stored procedure that does an insert to both local SQL Server 2000 table and linked server.Sometime it succeed, but sometime it return error mesage (The current transaction could not be exported to the remote pro...more >>

How do you check when was the last time an index was recreated or created? Is there a information_schema.xxxx view I can use? Here is my query: SELECT s.name, o.crdate FROM sysobjects o, sysindexes s WHERE s.id = o.id AND s.name = 'IDX_ProductID' TIA ...more >>

.... that's the question. I'm using SQL2k. In the past I have heard different opinions about using the datatype bit in SQL. Some people suggested using char(1) to replace bit data type since the sum or average aggregate operation cannot take a bit data type as an argument. (like a group by ...more >>

Hi, I have created a view that uses union all for different fiscal years. example: select * from table1 union all select * from table2 I have create a check constraint on fiscal year for each table. These tables get dropped and reloaded on a weekly basis. When I query the view it will do...more >>

I'm attempting to run a FULL OUTER JOIN between a table holding budget records and a view holding various transactional data records. Even though I'm using Full Outer Join syntax SELECT * from FY05_Bud_Dollars Full OuterJoin Firm_office_dollars_vw on Bud_Loc = FY05_Location WHERE Period...more >>

Ok to all the gurus? I have a text file that is coming in in rows that start with an id then have data in the delimited fields. not all the fields are populated and not all of the pipes are in the same place. I need to break break down the fields into the following 2nd format,from this format ...more >>

Hi I'm using this sql instruction to get records that match in their names from two tables: SELECT CE.RFC AS CE_RFC, T.RFC AS T_RFC, CE.ApellidoPaterno + ' ' + CE.ApellidoMaterno + ' ' + CE.Nombres AS CE_Nombre, T.ApellidoPaterno + ' ' + T.ApellidoMaterno + ' ' + T.Nombres ...more >>

Is there anyway to get the current date in a view?...more >>

How do you check for the existence of an index on a temporary table without querying sysindexes directly? Brandon...more >>

In a traditional parent-child table which is self-joined, like a manager and employee, we would have a table such as this tableName: Employees employeeId int PK not null worksFor int null references Employees ( employeeId ) This works well where an employee works for one person. But w...more >>

Hi I have a DB called 'test'. Its primary filegroup PRIM has two datafiles. These data files exist on diff hard drives. I want to restore database 'test2' over database 'test'. Database test2 contains only 1 data file. BUT, as part of the restore I need to restore the data to both data...more >>

I am running an Inventory Application which records daily receipts and issues of supplies in a industry. The issue of an item is recorded with the average cost at the time of issue. The problem is that if the wrong price is entered while recording the receipts, all later issues of that ite...more >>

Hi all, I have 2 questions that arose from the same issue while i was trying to create an indexed partitioned view: You will find a repro script with comments below. 1. When I try to create an indexed view that aggregates a nullable column, I get error 8662: "An index cannot be created ...more >>

Here is the query I currently have Use WinPayment GO SELECT pos_condition_code, convert(char(11), retrieval_reference_number) RR, message_type, authorization_identification, convert(char(8), card_acceptor_identification) SN, convert(char(25), transaction_...more >>

I am stuck in how to calculate a person's salary based on hours worked and rate. To calculate the salary on an hourly basis per day, I need to calculate the hours worked based on start time and end time rounded to the bottom hour. Then the hours worked is multiplied by the hourly rate. I am...more >>

I have a query connecting various tables, but I need the number of specialtyID to be the same (but it can be dynamic depending on the tblID I need). I've tried various things, but would like advice. Query: SELECT TOP 100 PERCENT t.tblID, t.orderID, r.specialtyID, SUM(r.nbr) AS nbr, MAX(r.n...more >>

Hi, I have a question. In Enterprise Manager, when you right click on a table name you see a 'Cut' option in the menu. It seems that when we 'cut' a table it doesn't do anything. Is that right? Is it just a command similar to 'copy' which also gray out the icon next to the table name without ...more >>

Hi, I have wrote some error handling into some SP worknig pretty well when use through the Query analyzer, but not when use through an automated job. From my understanding, it looks like once the first error occurs, the jobs stops and exit woth failure without giving a chance for the code to...more >>

Hi, I have 2 tables: tableA(ID) and tableB(ID) let say tableA have 3 records with ID = 1, ID = 2 and ID = 3. TableB has 1 record with ID = 9, and ID = 10 I want to setup a subquery to get max(ID) from tableB which is = 10, then use this max value to run update on tableA to make tableA(ID...more >>

Hi all. Let's say I have two tbls: tPerson and tPlace: tPerson: PersonID PK int (clustered index) PlaceID int PersonName nvarchar(50) .... tPlace: PlaceID PK int (clustered index) PlaceName nvarchar(50) .... Obviously, there must be a c...more >>

Hello, I was asked by my colleague regarding a project he is involved with. The project entails a Party model where everything is combined into a bucket and identified by identity id (ie. person, organization, prospect etc). The name field is a calculated column where it will hold eit...more >>

I have a table with 2 columns: An id field and a personInfo field: I want to get col1 and col2 and concatinate them, Like: First Name1, Last Name1 First Name2, Last Name2 First Name3, Last Name3 First Name4, Last Name4 col1 col2 1 First Name1 2 Last Name...more >>

I'm using sql2k. At the beginning of stored procedures, I've seen the following: SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO And I've also seen: SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO Can someone tell me which one is a better coding practice or SP standard? (I'l...more >>

Can you execute a cursor from DTS Package? HOW? Thanks, Usha...more >>

Lets say we start with the release of S2k.For the last five years users have be encourage to send their wishes to sqlwish_@microsoft.com and/or http://register_.microsoft.com/mswish/suggestion.asp Now that's gotta be a lot of data.I confess to being a bonehead because I can't find any summary...more >>

I would like to remove the column headers from the return set of a stored proc. I can format the output within the proc to match what is needed by the receiving process but the column headers and dashes are messing it up. Is there a way to remove these with some type of option....more >>

Hi all, We are implementing a new Database review process in our development cycle. Currently this will be limited to schema reviews. While, we are developing our own template based on standards and best practices, I was wondering if anyone here has experience with such. What templates do you u...more >>

When you are in Enterprise Manager and you right-click on a table, select design table, and change something, then click on the Show Code button. All of that SQL code is created. Is there a function within SQL Server that does that for you or would you have to do that each time? What I want to...more >>

Hi all, I have a trigger that will not allow certain types of inserts into a table. I need to be able to insert into the table, but I do not want to go into EM drop the trigger then re-create it. Is it possible to disable he trigger and re-enable it in a procedure? Thanks, Joe...more >>

-- How do I excute string/formulas expressions? DECLARE @exp as varchar(50), @r as int select @exp = '(1+2)*2' select @r = --HERE I NEED TO GET @exp RESULT -- Can you help me? -- Thanks!...more >>

Hello, I am in the process of creating a test database for a client. The database will be created out of the actual data in the production database, just mangled a bit. My algorithm is simple: I want to offset various fields in each column so the mangled data looks nothing like the original. ...more >>

Hi all, please help me out here: I have a very simple view: select tableA.fieldA + ': ' + tableB.fieldB from tableA Inner Join tableB On ... When tableB.fieldB is Null, instead get "tableA.fieldA: ", I get nothing even tableA,fieldA is not Null I tested Select 'ABC' + ': ' + '' will...more >>