Hi all, I am looking for a way where we can use Sequence Table to generate row number for non unique rows . (Sequence is a table of numbers from 1 to (n); this is a common SQL programming trick. ) With warm regards Jatinder ...more >>

Hi, I was told to convert a cursor based stored procedure into set based. The SP has 4 nested cursors, I believe it's causing SQL server to crash or perform poorly. But I couldn't quite figure how to convert it, to simplify I eliminate the non related fields and tables, here are the main ta...more >>

I am invoking a stored procedure from a web page, and the stored procedure correctly inserts a couple records. The calling web page then attempts to retreive a variable created by the stored procedure (called @@GUID) but this fails. The mystery is that the same stored procedure, invoked fro...more >>

Just point me to a source, if you know of one... I get confused about when to use WHERE and when to use HAVING when I set up a GROUP. Any insights would be appreciated. Larry Woods ...more >>

Hi, I have a query like this select * from T_Log Where AccessedDTTM > '05-10-2004' and AccessedDTTM < '05-15-2004' on which ID is a Clustered INdex + primary key I have created a non clustered index on AccessedDTTM . But in the execution plan it always does a clustered index scan on t...more >>

Is it possible to specify a validation rule for columns containing email addresses. Or more generally, can I use a regular expression in a rule ... and if so, what is the specific sytax? TIA// ...more >>

I have a simple User defined function and I want to find values that are equal to 0, Equal to 1, Between 0 and 1, and Null. The data I am using is an Avg on a numeric value. When looking at the average the values appear correct. Like 0, 1, 0.5 etc..., The values apparently are being rounded ...more >>

Hey all, Below you'll see several dynamic sql variables I've concocted. Each one produces an integer value as it's output so I've trimmed out the syntax for shortness sake. I must use dynamic sql (as far as I know) because the user will be passing in select criteria for each when the SP is exec...more >>

How can I do this Create table XYZ ( sid int, rowno int) Insert XYZ (sid) select 1 Insert XYZ (sid) select 1 Insert XYZ (sid) select 1 Insert XYZ (sid) select 1 Insert XYZ (sid) select 2 Insert XYZ (sid) select 2 What I want is SID rowno 1 1 1 2 1 3 1 4 2 1 2 ...more >>

How can I do this Create table XYZ ( sid int, rowno int) Insert XYZ (sid) select 1 Insert XYZ (sid) select 1 Insert XYZ (sid) select 1 Insert XYZ (sid) select 1 Insert XYZ (sid) select 2 Insert XYZ (sid) select 2 What I want is SID rowno 1 1 1 2 1 3 1 4 2 1 2 ...more >>

I'm moving my application from running on W2K with MSA2K runtime to machines with XP SP2 and MSA2K runtime. When viewing from the XP PCs all my stored procedures are suffixed with ;1 when using MSA2K. When viewing the SP's on the same PC but using MSA2003 they don't have the suffix and the app...more >>

Hi I'm trying to delete some records with the following ASP script but I get the following error on rsDb.Movenext() "Row handle referred to a deleted row or a row marked for deletion. " do while not rsDB.EOF 'here i do some task rsDB.Delete() rsDb.Movenext() loop ...more >>

Is there a stored Proc to show me all the users on a database and there role for that database?...more >>

Ok guys and girls here you go I found only partial directions in BOL to do the following task: We want to drop and create an index on a view in order to do thius we need to have the view schema bound. How would one go about in the creation step to schema bind a view?? thanx much BillO **...more >>

Hi, I have a multi-million record database, and so I'm just starting to look into indexing for faster data retrieval. If I query this table like this : "select a,b,c from MyTable where d=5 and e=6 and f=7 order by a", what index should I create? Thanks, Steve ...more >>

Hello! I would like to know if there is any funcion to clear the output form a stored procedure. I would like my stored procedure to return only my last SELECT Statement. What happens is that it returns several VIEWS during the execution of the stored procedure. I need to return only the ...more >>

Hi everybody, Been a while since I've been on here. We have a need to encrypt data, the more secure the better but without being too complex, i.e. too unwieldy to incorporate into applications. So I was wondering what people are using and why? Much thanks in advance, Michael MacGregor ...more >>

I tried creating a view with the code shown below. It does gives me a result pane when I run it. But when I go to save it i get this error: 'View definition includes no output columns or includes no items in the From Clause' SELECT R.Session_Mid, R.Session_Lid, S.Session_Name, 'Less7...more >>

Attention Harsha Shah: Fix your clock. -- Adam Machanic SQL Server MVP http://www.datamanipulation.net -- ...more >>

Hi everybody, How to rename some of a table's fields. What is the syntax for that? -- TS...more >>

I have been using SQL Profiler to analyse long stored procedures to see which SQL statements are taking the longest to run. This works well, but when I find a complicated statement, is there a way to analyse that to see which parts are taking longer than other ? Thanks, Craig...more >>

Hi, I´ve got a Execute SQL Task which retrieves a sets two global variables from a table. One of the fields is a SQL Datetime containing a full date including milliseconds. I need the entire date set to the global variable because in another task it is a parameter for a query and it must be...more >>

This probably rates a "2" on a difficulty scale from 1-10, so I'll apologize now, but here goes.....I have two tables, call them A and B, with identical columns. I'd like to join them on their unique ID fields so that I get all records in A that are not in B. What's an appropriate SELECT sta...more >>

Hello, I have a stored procedure to update telephone extension numbers The stored procedure should return two tables one for user with one extension, another for users with more then one extensions. Is it possible to return two tables(e.g. put one in the output parameter)? Can someone pl...more >>

Hi, I have a stored proc that builds Dynamic SQL based on input parameters, then Executes sp_executesql. Seems to work good. But one of the lines in the select is calling a UDF. Select o.OrderID, dbo.udfMyFunction(o.orderDesc) AS Desc This of course is all in the @sql string I am bui...more >>

Dear all, I was wondering myself, which method is faster? select col from table inner join (select col from table4 ) as table2 on table.id = table2.id or select col from table inner join table2 on table.id = table2.id ...more >>

Hello everybody, I have one store procedure which is calling second store procedure within it.. What i want to do is, if while executing second store procedure in first one, if any error happens... i do want to rollback entire transaction.. My sample code is as following.. Can anybody pls ver...more >>

when i use ltrim functions only single byte space char are removed how i would be able to trim double byte space char...more >>

Hi, I had 3 table inventtable(itemid field),custinvoicetrans(itemid,qty),salesline(itemid,lineamount) i want to create a procedure with cursor so that it should select one by one itemid from inventtable then that value should pass to the next table and sum the qty from custinvoicetrans and...more >>

Hi, When adding new features is there ever a point whe you say stop normalising because its just way too many tiny tables and much easier just to add a field and use a check constraint to restrict values and enforce ref inetgrity that way. An example. A sale has 1 user, a user has 0..n sal...more >>

Hi, When adding new features is there ever a point whe you say stop normalising because its just way too many tiny tables and much easier just to add a field and use a check constraint to restrict values and enforce ref inetgrity that way. An example. A sale has 1 user, a user has 0..n sal...more >>

Hi, In SQL Server is there is any way to pass the table rows itself as a input parameter to a Stored Procedure. Thanks, Herbert...more >>

Hi there! I've been trying out the built-in replication in MSSQL, but it confuses me too much, and well, it seems like I have to do a lot of reading to figure the system out. So, I'd rather do it in "plain SQL", but which way is it the smartest way to do this? The task is rather simpl...more >>

How to set "Collation" on a database and server system? Because I have some database. a database has a lot of tables. and if i wanna alter it, so must i alter table by table. is there way more efficien? ...more >>

Hi Everyone, One of my database is in Full recovery model. I noticed transaction log is much bigger in Full model than Simple Model.. I assume size is almost 100X greater. (10 gb LOG using FULL model) I WANT TO RESERVE DISK SPACE FOR OUR SERVER. If I switch Full model to Simple mode...more >>

I attach a file database from other computer (copy file). When its database on old computer, it has collation "SQL_Latin1_General_CP1_CI_AS". but When I move its database on new computer, so it has collation "Latin1_General_CI_AS". Why can this alter by itself? ...more >>

Hi All, Is there any way to get machine IP address from within a Stored Procedure. Actually I want to implement a GUID algorithm with custom format. Thanks in advance. Regards, Shahzad Atta ...more >>

hi guys, Is there a way to use GetDate() function and GetUTCDate() to find out time zone I'm in? I know GETUTCDate() is greenwich time (GMT), So I know I'm in GMT- 05:00 (Eastern Time (US & Canada) but How Do I do a query to return "GMT- 05:00 (Eastern Time (US & Canada)" I look...more >>

Hi, I am trying to write my own utility which can read the trace file generated by SQL Server. I am able to interpret most of the binary data in the trace file. But still I am having some problems in interpreting some binary values which are there at the beginning of every trace file. These...more >>