Howdy All! I have tableA: id integer date0 datetime date1 datetime date2 datetime date3 datetime date4 datetime with values: 1, '11/1/04 12:56.000', null, null, null 23, '11/2/04 12:56.000', '11/2/04 15:56.000', null, null 45, '11/3/04 10:36.000', '11/2/04 ...more >>

Hi There, can i do this in a single query I've a table with post code field(varchar(8)), and other lookup table for postcode(varchar(8) with area number Now i need to find area number for each record of master table, if there is no match just need a null value Lookup postcode table ...more >>

hai i dont know exactly,there is any way to hide to object script e.g. tables, stored procedures... which is very important in business application like CRM, HR,... who know more strong way? is there tool doing this work available? Thanks, ...more >>

Hi, Unfortunately, SQL didn't provide methods for encrypt out object script e.g. tables, stored procedures... which is very important in business application like CRM, HR,... Exactly the built-in encryption method is quite weak, who know more strong way? is there tool doing this work availa...more >>

Hi all In the SP below im (trying to) do some dynamic sql. As you can see the table to use is set as a variable and the 'exec' method used to run the sqlstatements. My problem is that the 'if exists' method is not doing what i was hoping it could do. The @presql command returns somewhere bet...more >>

Hi, SQL 2000 Attempting to do a mass update to a single column in a table using the following sql via the query analyzer. UPDATE table1 SET table1.Activity_Flag = Left(table1.Activity_Flag, 1) The column accepts NULL values and is char(2). The above sql indicates that it executes but a...more >>

Hello guys, is it possible to rename a table with a SQL statement ? Thanks ...more >>

When I restore the database e.g. Northwind, let say I made a full backup and the Name is called Northwind backup -- default name and 30 mins later I made a transactin log backup and the Name is called Northwind SecondBackup, since from what I know, they are not under the same family name, is ...more >>

The ##temp table can’t be used on multiusers environment. And use with #temp table, make error when Proc called twice In the same session (on connection). Solutions: *Drop table on end of proc *keep call to this Proc from fresh connection those both solution not good for performance. Do...more >>

Hi all Should implement a transaction in both the stored procedure AND in ADO.net code or is doing it in one or the other good enough to protect against concurrency and atomicity problems? Thanks Simon "Mary Chipman" <mchip@online.microsoft.com> wrote in message news:8egpp052lm2jkk...more >>

I execute some sql script files in osql, like this: osql -i inputfile -........ but my inputfile is unicode file, so execution failed. Anybody can tell me, how to deal with this? thanks....more >>

I have a TSQL script that generates a ton of output and logs it to a file. The output is also echo'd to the Query Analyzer window, which I think is slowing things down. Is there a faster way to do this (like getting rid of the echo'd stuff)? Thanks. ...more >>

I have the following statemnet that reads a 10 digit number that has 4 decimals in it. I would like to remove the deciamls and just have the whole number: select yearcollect/iif(numberdays>0,numberdays,1) as averagecost an example yearcollect is 42953.1290 and with numberdays as 10 so I ...more >>

Hi all, I'm using WHERE CURRENT OF and a forward only cursor on SQL 2000 stored procedure, out of laziness really (instead of using ADO code), since all the stored procedure functionality is already in place in our ASP code (with commit rollback etc). I am using this stored procedure to add ...more >>

Hello What should I use for better perfomance since unique constraint always use index ? Thanks Kamil ...more >>

Hi there, My tables in the database have nvarchar data type for most of the columns. Does nvarchar data type has any effect on database/query performance, such as slowness, when doing selects, updates, inserts or deletes? Is there a performance difference if I use nvarchar versus us...more >>

If I execute the select statement I get this result: select * from example: ID Country productID quantity leadTime ---------------------------------------------------- 1 ALL 1 5 NULL 2 ALL 1 4 ...more >>

There have been many schema changes and I want to recompile all functions, views and stored procedures to find anything that broke so I can fix it. Is there a relatively painless way to do that without doing them one at a time? -- Peace & happy computing, Mike Labosh, MCSD "I have no...more >>

Hi, I am working on some replication-type data transportation project which needs to handle data exchange between different SQL Servers. I had created an SQL Server default like 'xxx-' + CAST(NEWID() AS CHAR(36)) and bind this default to related tables related fields which will be PRIMARY K...more >>

Here's the setup: I have two hypothetical tables: CREATE TABLE tst_role ( role_name VARCHAR(10) ) CREATE TABLE tst_user_role ( user_name VARCHAR(10), role_name VARCHAR(10), ins_order_n SMALLINT ) With tst_role data of: 'Admin' 'Super' 'User' and no data in tst_user_...more >>

Hi All I have been developing in MS SQL Server 2000 for about 8 months now so I would consider my self to be really new to this Database. As I am lacking in long term experience I really have no idea about the sizes that MS SQL is capable of. My database started at 0 in size I would say 3...more >>

Ive some how wound up with lots of extra records in a particular table. The records are duplicated except that they have a different storeName and StoreNbr. The one thing good I have going for me is that all of the dup's storeNbr's are 0. So what I need to do is delete any record that has a dupl...more >>

I have a photo table and a people table. However more than one name could appear for each separate coord on the photo. I know its strange but trust me it is what I want! I want to acheive this on the database rather than looping through records on the application. I want to return the foll...more >>

I have the below SP that requires a datetime to function. Everything works, except that I need it to default to '%' if no date is submitted. rsNRList__Datte = "11/18/2004" <-- this works rsNRList__Datte = "%" <-- this does not - I get the following error How do I specify that if no date is sub...more >>

I would like to know the proper backup plan the previous company that I worked, they backup all the database into two folders with the same drive... Z:\SQL\Backup\Users Z:\SQL\Backup\Systems In each folder, they also contain five different folders called "Monday", "Tuesday", .... to ...more >>

Sorry. Not strictly a SQL Server question, but could not think of a better place to ask. Need to populate a SQL Server contact database with test data. Is there anywhere I can download a csv file (or similar) containing last/first names, addresses, and phone numbers? Preferrably with hu...more >>

sir i want to knew about one thing. how to see a last inserted row in a table (without using datetime or indexing) suppose i knew which row is inserted in last because i am inserted the last row but if another person using this. then they how to know which row is inserted in ...more >>

Hi, I am new in SQL server world, and this might be easy task but I couldn't figure out. I have 2 servers registered in my Enterprise manager. I am using one as development server and other as production server. I have one exact same database on both server except # of users are different ...more >>

I have a field that contains anywhere from 20 to 30 characters. The last 11 characters are not needed for any reporting reason. Is there a way that I can select all but the last 11 characters? Thanks in advance for the help...more >>

I have a table with two columns. I need help with concatenating the second column. Below is sample data. code textdata 40201 the cat in the hat 40202 needs his cheese 40203 for delight 40301 dogs and cats 40302 fight like 40303 c...more >>

We are using COLLATE Latin1_General_CI_AS in our databases. Is there any documentation to be found on this collation sequence as to how it compares specific characters and strings? I ask because we have found some "interesting" behaviour - for example, it seems to ignore the character ...more >>

I first need to apologize for my lack of knowledge as to how to present this. Below is what I hope will at least show what it is I am trying to do. If someone would not mind helping me get on the right track to doing this, I would greatly appreciate it. SELECT skCompetitors.MemberID, skC...more >>

SELECT productID,sum(quantity)/count(quantity),sum(leadTime)/count(leadTime) as leadTime FROM .... With SELECT statement I get table like this: productID procent leadTime ---------------------------------- 1 15 20 2 35 1...more >>

I know this stored procedure looks like am just playing around with redundant data, but it's necessary; however, can anyone tell me why the following stored procedure enter the same first winner twice, then enter the same second winner twice, then enter the same third winner twice, and on and ...more >>

How do you grab one instance of a value in a table. Example I am grabbing the Accountnm from a table and there could be 3 or 4 instances of this value but I only need to grab one instance. -- Ken Hamilton Consultant ...more >>

Hi group, I'm creating a web portal where customers can search for inventory which originates on remote servers/databases. I want to physically copy this data to the portal server rather than have partitioned views... I've written a stored procedure for each source database that selects the row...more >>

Can you pass arrays to a stored procedure? if so, how? Please explain....more >>

Hi, I have a function defined that takes a comma delimited list that is passed to a sp as a parameter, this then creates a temp table that I can use in my sp that is calling the function: CREATE FUNCTION Split (@List varchar(1000)) RETURNS @Results table (Item varchar(1000)) AS ...more >>

Hi !! I need to create a trigger which call a web service. How can I make it ?? It is possible to do ?? Thanks in advance. ...more >>

I am attempting to use a SQL Agent job to BCP data into tables. Once my development effort is complete, I would like to be able to script out my job and execute it on my QA/Production servers without changes. In order to get some portability, I would like to use environment variables. For...more >>

I'm new to SQL and stored procedures and would be grateful for your help with the following. I have the code: CREATE PROCEDURE sp_test AS SELECT test1, test2, [test3] / [test4] AS [test5] FROM test GO How do I ensure that a zero is returned in [test5] if [test4] is zero. At the m...more >>

Hello All, I am working with a query on sqlserver 2000 to solve an issue. Problem: I have to retrieve data in a table in hierarchy. Every parent will have may have max of two children one on left and other on right. So at any point of time max one parent has two children. So the data would be ...more >>

How can I create dynamically a table, or a column in a table, without to know explicitly his name? Something like this: declare @X as char(3) set @X = 'ABC' create table tblT (@X int) Unfortunately, it doesn't work. Please help....more >>

I have two tables with 200,000 rows in one table and 100,000 rows in another table. I am joining two tables and getting 2000 rows by a condition. It is taking greater than 15 Sec. to retrieve 2000 rows. Why is it So...? Can any help me out... I am attaching the table scripts along with inse...more >>

I currently have code that returns x working days back from a date. e.g. 19/11/04 - 20 working days back = 22/10/04. It is using TOP to return the value, but what I need is a UDF that I can pass two parameters in: Date and No of Working Days and return a working Date. The code I currently u...more >>

I have a case column called A. When I try and use the column A to compare with one of the other columns B it says that A is invalid Invalid column name 'A' I need to list a number of columns from multiple tables, carry out a calculation using the CASE to create a new column and then compare th...more >>

Hi friends! I have a doubt.. I dont know if it is possible or not, but I will try to explain.. I want to order a query by a parameter... for example: ------ @tmpORDER AS SMALLINT SELECT ID, DATE, NAME FROM AUTHOR ------ if @tmpORDER=1 then ORDER BY ID if @tmpORDER=2 then ORDER BY DA...more >>