Hi all, I do have four combination key for eg:1111-1111-1111-1111 the values can range from 0-0, based upon this i can create any number of keys eg:1234-4567-4352-6534, these four values comes from different tables, while joining the four i insert the new combination in a new table ....more >>

if i have a table with a primary key integer set to not null and i have an instead of insert trigger assigned that says if field is null set it to this value. IF i try to do an insert into my table without specifying a key value, why does it blow up? doesnt' the instead of trigger insert t...more >>

Hi all, I want a nonpriviliged owner to attach a specific database. I put the attach command in a stored proc. How do I grant priviliges to that stored proc so the user can exec it? Thanks Frank ...more >>

Please refer to the following code for MS SQL Server 2k. This is an example of an issue that I'm experiencing on a much larger query. Let's say that we can only be guaranteed the type_title while all other data is provided by an end user. For example, the type 'I' could be modified to the letter ...more >>

in an interbase/firebird trigger i can do this for select col1 from table1 into :tmpCol1 do begin insert into table2(col1) values (:tmpCol1); end And it will do that once for every row returned from the select. What is the...more >>

I am sure this has been dealt with many times by others, so I'm hoping someone can provide a clue. Lets say I have a table (SQL 2000) with an identity column. Then I create a view with an instead of trigger. Now I try to insert into the view without specifying a value for the ID column. This ...more >>

Having a bit of trouble with this one. My code currently returns.... Product Num_Accounts ------------------------------ P1 20 P2 21 P3 34 P4 56 P5 12 What I want it to...more >>

How do I set up my query to get data from a 2nd file when there may not be any data? For example, the following select just gets some data from the Position table. The Category Description is in the JobCategory Table. I have the CategoryID in the Position table. Select PositionID,JobTi...more >>

Is there a way to do this insert * into new from inserted insert * into old from deleted so i can then reference them as if (old.column = 1) begin --do something end if (old.column <> new.column) begin set new.column = 10; end insert into myTable select * from new etc., et...more >>

I need a SQL query to get 2 items from each catergory. And if possible, 2 items with price < $100 and 1 item with price >= $100 from each caterogy. Table: tblItems Fields: ItemID, CategoryID, ItemName, ItemPrice A stored procedure with multiple queries also works. Thanks, -- Geevi ...more >>

What is the correct way to write this for sql server. update mytable MT set mt.this = 1, mt.that = 2; Or can you not use aliases for updates? in selects it seems to work select mt.this, mt.thank from mytable mt....more >>

Hello I was wondering if anyone could tell me if it is possible for me to dynamically create transformations in the Transform Data Task. The table I am pumping to excel is created on the fly and can have different columns and therefore different transformations. Any help appreciated ...more >>

Hi, I have two tables Tasks and TaskTeam. One of our employees wanted me to bulk delete all her tasks. We have relationships defined between the two tables to enforce referential integrity. The problem is a very very basic one. I can't delete tasks from Task table because there are referenc...more >>

Hi, How can i truncate tables if there are foreign key constraints? Thnx ...more >>

Can someone help me write a script to do full backups or differential backups for all user databases on a server ? Thanks ...more >>

Hi I am using a stored procedure to write a Crystal report using VS .NET 2003. All the columns show up very well in the report except for nText columns. I ran the stored procedure in Query Analyzer, and found out that the query is truncating the contents of nText columns. My question is...more >>

So, I'd have a stored procedure that is something like: CREATE PROCEDURE [dbo].[itsp_ResourceLock] @LockName varchar(64), @MachineName varchar(64), @UserName varchar(64), @ProcessID int as begin SET NOCOUNT ON declare @iRC INT insert into ResourceLocks (LockNam...more >>

in sql server 2000 i guess there is no on delete set null option for a foreign key I have seen many postings when searching about you have to do this with an instead of trigger can someone provide an example of an instead of trigger that will set teh foreign keys to null and still delete...more >>

Is there a way to have a PK column auto generated while doing a bulk insert? Thanks ya'll!!! T.B. ...more >>

How do I test for the existance of an index with in a specific table? Thanks, Bryan ...more >>

Hi, I have a select statement that gets data from only one table. When I write OPTION(LOOP JOIN) after this query and run it, the execution time is 2-3 times faster than without OPTION(LOOP JOIN). If I use OPTION(FAST 1) the execution time is as fast as with OPTION(LOOP JOIN) Does anyon...more >>

I have a subquery that gets the max of quote_date and requery the result to get the max of quote_id for the same part_id, vendor_id and quote_date. This query is working fine and I want to save the result in a temp table but the first 3 codes won't work. Thanks for the help. SELECT v1.*...more >>

I'm doing join of Table A with Table A That have 3 records every record has an | ID | Transaction Date | Transaction Time | Movement | Counter | Client | 1 2005-04-18 12:00 IN 1 MS 1 2005-04-18 12:30 ...more >>

I am trying to create an sp that will be put into a report that allows me to pull information based on the last day to day year. E.g. today is 4/20/2005 and I would like to be able to pull info between 4/20/2004 and today, but I want this to be dynamic so that it will pull the data tomorrow fo...more >>

I am a seasoned veteran of MS Access, and have recently begun migrating my skills over to SQL Server. In an MS Access parameter query, if you want to retuen all records if no criteria is specified, you would set the criteria as " Like [VariableName] & *". If the user provides a value, onl...more >>

hi, I try to shrink a file the original size is 50000mb I want to shrink it to 31000mb DBCC SHRINKFILE (Data_2,31000) but it's running for 2 hours already..why is it so slow? is dbcc shrinkdatabase faster? ...more >>

I hope I can get this across clearly. I have a table that needs to be broken into 3 tables. Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col1 and Col2 need to go into LookupTable1 Col3 and Col4 into LookupTable2 If Col5 is twice the width.... haha just kidding... so Col5 and Col6 go into LookupT...more >>

The running of my bcp (with queryout) is aborting, with the message below. At first I identified that it happened only with column with NULL value, but now I realize this occurrence happened in other field without NULL value. Anyone has suggestions ??? Thanks a lot My format ...more >>

I'm writing a stored procedure that will look for 'string' in the 'n'th column. For instance, exec QueryTable 1,'aString' ....should look for the string 'aString' in the first column of a specific table. Does anyone know how to do this in a generic way, so that I don't have a big IF stat...more >>

As a test I tried the following simple stored proc using one table CREATE PROCEDURE sp_test AS DECLARE @locationTankId INT select * from LocationTanks where IdLocationTank = @locationTankId GO Couple of questions, 1- If I don't use the word DECLARE for my parameter I get an error message ...more >>

background: sql2k on win2k3 i was told it's best to keep data files (mdf) on raid5 (i.e. f:\data\) for economic reason, and log files (ldf) on seperate mirror drive, raid 1 (i.e. e:\logs\) for write performance and dedudant reasons... i've got two questions: 1. if i were to adopt the appro...more >>

I have a table with 4 fields making up a unique key. I have an application suite (comprising millions of lines of code, so I want to avoid changing it if I can) which needs to retrieve a row from the table which is the next in key sequence equal to or after a given row. The table layout is ...more >>

Hi All, I'm trying to figure out how to use a variable within a stored proc to be used as the table name. I am passing in the name of a table to a stored proc and getting the table name form sysobjects. This works fine; but when I go to use that name in a query, inside the same Stored Pro...more >>

Here is my problem. I have an inventory table that contains the following records Item Lot DateSeq DateRecd Qty A test 1 02/02/05 100 A test 2 02/02/05 150 A test ...more >>

can I change my SQL2k desktop server to a named instance after install? ----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups ----= East and West-Coast Server Farms - Total Privacy...more >>

Hi, I have a sproc that needs to run, the only thing is, I only want it to = always be executing non-concurrently, i.e., this sproc should never be = running more that one time at any given time. (This sproc is making = external calls which will break if ran concurrently). Does anyone have = ...more >>

Hello, Within a view I've created, I have combined 2 fields to make 1. dbo.TABLE1.STR_STRATUM + N' ' + dbo.TABLE2.STR_LAYER AS StratumLayer This is for display (to populate a listbox in .NET). The problem is, if there is nothing in the STR_LAYER field, the whole field is blank. Is it p...more >>

I need to pull a PDF file out of a blob that I have uploaded it into. We need it available on the web. I can't think of how to utter a statement that will allow acrobat reader to know that it needs to fire and open what is in the blob. Ack....more >>

Greetings, How do I insert the XML document returned from the usage of FOR XML Explicit into a different table within a Stored Procedure. I have been able to use the FOR XML Explicit to generate the XML document but I need to insert it into a different table as a Text value. TIA, KOY...more >>

Hi all, I have an sp which works fine on a server, but fails on my desktop engine with the following error code: Server: Msg 8626, Level 16, State 1, Only text pointers are allowed in work tables, never text, ntext, or image columns. The query processor produced a query plan that required ...more >>

Does anyone know why an agent would in the agent error log turn itself off? This is an interesting situation as all backups and all log backups and integrity checks are failing. There is about 17 gig free on the system and the largest database is 4.5 gigs the total of all the data is 26 gigs a...more >>

Hi, I am doing alter table and updating Nvarchar datatype to Ntext. AS there are almost 54,09,873 records installation is taking more than 3 hours and log/database files are getting increased beyond limit. Now there is hardly space left and still installation is not completed. Tasks ...more >>

Hi I have a stored procedure , wherein i create a ## table and get values in it, i have 3 more procedures which i call from the original procedure which use the ##table. When i execute the stored procedure for first time i get the desired results. when i re-run it again it says the ## t...more >>

I am trying to create a delete trigger on one table that deletes from another and cannot figure out why I am getting a syntax error. Can anyone help? Below is trigger: DELETE ClientWorkerStatus FROM deleted, ClientWorkerStatus WHERE deleted.ClientID = ClientWorkerStatus.ClientID AND delet...more >>

I know this is a basic question for database design but Donald Trump said 'know a little about a lot' so here's my question. I have two databases. db1 has a table with two fields which if updated I want a table in db2 to also be updated. How do I do this? I tried using a trigger but don'...more >>

Hello, I have a processes which access a SQL Server 2000 in Brazil, Japan, China.. and a few other places. I would like to return the localtime of the linked server. I cannot find anyway to do this, and can't find a setting in any of the tables which shows the current timezone. Any help ...more >>

Server = SQL Server 2000 SP3a Client = Access Data Project (2000 SP3) I have an access report with sproc_b as its record source, on opening the report it fails to return any data, with this message: "The stored procedure executed successfully but did not return records" sproc_b = create ...more >>

Hai , I want to get the column names along with the rows of a table as a record set is that possible? Thanks, V.Boomessh...more >>

I have a decimal amount in c#: decimal amounT = 62.75; and I need INSERT it into a SQL record as a money value. string strSQL2 = "INSERT INTO trvlDetail(amountfield) ""VALUES ('" + amounT + "') "; Should I use a CONVERT in SQL (if so, how?) or what would be the equivelent in c#? Than...more >>

can we install SQL server 2000 and SQL server 7 on the same machine. is it possible? any help will be appreciated thanx in advance -- ssaud ------------------------------------------------------------------------ Posted via http://www.codecomments.com -------------------------...more >>

Hi i have created a new database and a new linked server that points to an AccessDB using an ODBC DSN. Now inside that new sql db i have create i need to created a new view so i open EM went to views and paste the following select * from openquery (AccessLinkedServer,'select * from myt...more >>

okay I have found the table name that I want dropped using the dynamic sql below. I'm not quite sure how to populate @TableToDrop for the actual drop statement though. Can you help? I hope the indentation is not too bad and you can read the Statement ok Set @lnvchCommand = ' IF EXISTS ' + ...more >>

Hey, Struggeling with a query, maybe someone can point me out. Table structure : per contract per observation year the total payments Contract Observationyear Paiments A 1999 10 A 2000 4 A 2001 3 A 2002 2 B 1999 3 B 2000 1 B 2001 8 B 2...more >>

I will start out by saying I am a beginner so it might explain my question.... I have a stored procedure where I want to select all records that have a flag set to 0. This select statement is returned and I work with it in my application. Straight after this statment I need an update statem...more >>

Hi. I am trying to write a crosstab or pivot table, but I don't think the syntax that I'm writing is very efficient. It is using a union statement to add an overall total, but I think this is the problem that is inefficient. Does anyone know of a better way to write this query to cut down o...more >>

Hi All I m creating a sp in which i hv created a database then use select into clause with OPTION (KEEP PLAN) but it give me the error msg . "Internal Query Processor Error: The query processor encountered an unexpected error during execution." . I am sending you the part of my code . pls ...more >>

Hi, Can I Call MsgBox Function From SP ? ...more >>

Hi all, I ran into situation where domain integrity is implemented through table CHECK constraints with UDFs. But I have some concerns about how it works. Please follow my short test case below(my questions are after it). -- START GO USE tempDB GO IF EXISTS ( SELECT * FROM dbo....more >>

I have a piece of code which produces the following results........ Product Primary Age Secondary Age P1 58 58 P2 56 56 P3 ...more >>

While debugging a code on the sql server 2000 debugger, I have encountered a strange behavior while calling a user function from whitin the sum aggregation function. I have reproduced this behavior with the following code: CREATE FUNCTION Test1 (@prm decimal(18,2)) RETURNS decimal(18,2) ...more >>