I've created a CLR function. It may raise exception (Invalid reference) when putting in the select clause. It never raise exception when using in where or order or apply clause. Any tricks to debug the code?...more >>

I'm getting the following error when trying to call a stored procedure with parameters as a table in a select statement: Server: Msg 7357, Level 16, State 1, Line 1 Cannot process the object ....... The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has ......more >>

when would the value of @@trancount <0 ? Thanks KL ...more >>

I would normally put a schema up but I don't know how to easily convert this into a schema for demo purposes. I have 2 SPs that I want to put together into one statement and I tried using a Join but I can't quite get it to work. The first SP is: ************************************** CRE...more >>

I want to implement a dynamic sort in SQL along with being able to page the data... so basically I have a table of 10,000 people... I want to get them back in pages of 50 (this is all theretical, so please dont say why return 50 :) ) anywho... now I want to be able to dynamically sort this dat...more >>

I am this query declare @ExistingText varchar(1000) select @ExistingText = '@uj.edu' SELECT top 100 cu.SCMCUContentUnitId, cu.SCMCUName, --cu.SCMCUUnitStatusId, 'Valid' = TEXTVALID ('SCMContentUnit.SCMCUXML', TEXTPTR(SCMCUXML)), datalength(scmcuxml), pos = charindex(...more >>

Hi. We have a proc that has about 12 Params. Param8 is can be NULL, 0 or 1. application sends Null or 1. the default that application sends is 1. Code handles NULL as 0. with Coalesce. the problem that the proc is chaning behavious according to the first execution. So here are scenari...more >>

I want to change the column type from float to Numeric(38,35). I try the statement, but I get overflow error. Is there a way to not getting this? I do the following to change from current field type, which is float, to Numeric(38,25). ALTER TABLE [dbo].[InventoryTransactions] ALTER COLUM...more >>

The table I am inserting data into has columns of int(s). DataFile has 99 out 100 rows which meet requirement of int. 1 row has varchar RUn using maxerrors: BULK INSERT {dbname}.dbo.{tablename} FROM '{FileName}' WITH (FIRSTROW = 1, MAXERRORS=1000, FORMATFILE='...more >>

I'm a new user to SQL. I am trying to parse a string like this... SELECT * from Table1 WHERE "Sarah" EXISTS ANYWHERE in "Name" Column. I would like the record to be selected if the Name column said "Sarah" and also if the name column said "xSarahx". I am doing this in MS Access and I rea...more >>

I have a column in my database I want to be a computed flag based on an active date.. the Flag column is called active.. basicall I want if the ActiveDate <= getdate() (as in today is after the item was active) then the active flag is a bit 1, else its a bit 0... I tried this as a flat ou...more >>

Hi, I have a Access form from where I am trying to pass two parameters to sql server to run a stored procedure. THe code manipulation is done via ADO. I am gathering two values in Access text boxes and passing those values to the stored procedure. However I am getting error messages. I will ...more >>

How can I make a column table default being a different column value? Table1 {Col1, Col2, Col3 default Col2} ? Thanks ...more >>

I am working on a small startup with a friend of mine and we are running into multiple SQL timeouts all over the place. I am trying to find out how to elimnate them. Here is our infrastructure: WebServer: ASP.NET 2.0 Website ( C# ) Hosted on Server 2003 SP2 ( Single Xeon 3ghz Database: SQL...more >>

Question (about SS 2000): Does it really make sense to use affinity max (on an 8-way) box if you change max degree of parallelism to 1 or 2? In other words, if the threads can't migrate and re-cache themselves, what good does controlling the affinity do? ...more >>

Sorry, but I'm still confused on something. I've got a 8 cpu, 2000 SP4 server with hyperthreading which makes it look like 16 cpus. Now let's say that I turn off HT so that it's a "pure 8-way". My question stems from the fact that this has two instances on it. When I go into the server...more >>

In many procs, I will be receiving @CreatedByLoginName as a parameter and needing to retrieve the associated ID value to use in the rest of the proc. I could write this in every proc: DECLARE @CreatedByLoginID uniqueidentifier SELECT @CreatedByLoginID = LoginID FROM Login WHERE LoginName = ...more >>

Hi all, The following query is working well to identify all the duplicates in my table. *Now* I'm looking for a way to delete all but (1) of the rows in a each group of common ParticIDs. It does not matter which one row gets kept. SELECT PARTIC.ParticID, PARTIC.ParticCodeType, PARTIC.Par...more >>

Hi, I create a trigger that write a few fields form table A to tableB, works fine, but if I delete one record in table A I want delete in tableB too, I wrote this trigger but don't work. Thanks, i Apreciate your help Edgar ALTER TRIGGER [TGRDELETE] ON [dbo].[test01] AFTER DELETE ...more >>

Dear all, I nee an advise on a table structure that i try to denormalize if possible. I have an history table which is collecting REEL information. From that REEL (or spool) I can collect a certain amount of measures. For example the following field represent one reel entry with measure val...more >>

I'm stuck! I'm switching from an Oracle environment to T-SQL and can't quite get looping through a table and puting the email addresses into a cursor and variable. I will make the variable @users equal to the variable @recipient in the stored procedure sp_send_dbmail. Here's what I've got...more >>

I have an elementary question that I cannot find a straight answer to. If I create a store procedure that calls multiple store procedures. Will these stored procs run in order and not start the next until the previous one finishes or will it multi-thread? Example EXEC sp1 ...more >>

Hi I have the following Table Parent Component A B A C B D B E D F D G The Stucture is a follows To manufacture A I require B and C To Manufacture B I require D and E To Manufacture D I require F and G this indicates the the Manufacturing of A is a 3 level Bill of Materail a...more >>

Hi, Simple question but having trouble getting it right... I need to get the latest entry -- based on time stamp. ID -- TransactionTypeID -- TimeStamp 1 -- Deposit -- 20070710 2 -- Deposit -- 20070709 3 -- Withdrawel -- 20070705 I want to get the ID number of the latest deposit... S...more >>

Using SS2005. I have a table with 64M (tblwf1) rows and one with 10M (tblSpring2007NonResponders). I want to update the large table from the small one. I've tried this query: update tblwf1 set source_STA = 'Y' from tblSpring2007NonResponders AS b where tblwf1.addr_id = b.exp_id but i...more >>

I have a stored procedure with a cursor within it. If I call the SP from ASP the sp does not run all rows for the cursor. If I use the exact same statement in QA it runs fine. ??? As a work around I have a job that checks the contents of a table and executes the string in one of the fields(th...more >>

The below works but when I attempt to add in the 2 commented out lines i get syntax error. the desired result is to ensure that when the 1st(biggest) case statement sets (coalesce(r9.rated_driver_code,0)) then it will also check to see if c.tr_single_driver is null and if so set it to be '-'...more >>

when i had executed following statament, i have error message "invalid objectname inserted.Customers" create trigger A on Customers for insert as insert Sales(custid)select Customers.custid from inserted.Customers ...more >>

I see that it is recomanded to resubmit the transaction in case of a deadllock. Is there some example how to do this. I have in a loop to do more sequential updates (100) (one SP is called 100 times with othe parameters...), and now my question any idea, example how to do a resubmit? I thin...more >>

Using SQL Server Agent I am trying to schedule a daily job to run a macro using cmdexe. My syntax is : cmdexe "C:\Program Files\Cognos\cer3\bin\runmac32.exe c:\cognos\macros development\scheduled tasks.mcx" where runmac32 is the programme, and scheduled tasks is the macro I want to run. ...more >>

I have an general question. In a Transaction there is used a select WITH(NOLOCK). The select has also some INNER JOIN... Will be the records from the table not locked? Should a transaction only be used for (UPDATE,DELETE and INSERT) and a SP which has only a complex SELECT statement to use...more >>

Is there an easy way to copy database objects - tables (incl. indexes and constraints) and views - from 1 schema to another? I am working on a project that requires the creation of new schemas with the same objects that exist in the dbo schema. The ALTER SCHEMA command only allows the transfer...more >>

Hi, I have the following issue with querying two tables with a one to many relationship. table a ta_id, int ta_description , varchar e.g. 1, nice product 2, nicer product table b ta_id, int tb_code, int e.g 1, 12 1, 14 1, 25 1, 30 I want to show all the (distinct) ta_des...more >>

Hello there In some cases i need to run dinamic sql statement like: exec('select 1 From <someTable> where <where condition>) and then i just check if it return records and how many records has been return. by this: if @@rowcount = 0 ... the problem is that it present the result of the se...more >>

Hi all I was hoping someone could give me a few tips on what (for me) is a complex query. In "SQL english" I want the query to be something like: Select * from TableA where it doesn't have an entry in Table2 OR if it does have an entry in Table2, Table2.bool = false I can get the firs...more >>

Hey MS Sql Server 2005 Express! I'm working on a database having a table named User. While creating another table I try to set a column to reference one of the columns in the User table: t int not null references dbo.User(id), (I removed some lines of code here just to make this post ...more >>

Hello there I need to build function that get tableName and return the amout of records in it. and it must be function because i need it in select statement Is there a way to do that? ...more >>

Hello there I have database with full recovery model. After hour from the last backup the database corrupted. Afrer restoring the last backup i used the log file to recover the hour before the crash. during that hour there were many new records entered the system. and many of the tab...more >>

I created function like it: CREATE FUNCTION F_LISTKAR_USTAT(@USERNAME VARCHAR(8), @MODE CHAR(1), @THN SMALLINT=0) RETURNS TABLE AS RETURN ( SELECT * FROM V_DAV A WHERE FLAGEXIT=@MODE AND EXISTS(SELECT * FROM THAK_UNIT WHERE USERNAME=@USERNAME AND BAGIAN=A.BAGIAN AND STAT_KAR=A.STAT_KAR...more >>