hi friends, my table struct is callid line system duration 12 7 1 120 12 7 1 500 12 6 1 240 i want to implement composite pk on this table for the columns (callid,line,system) so first i shd elimin...more >>

Hi All, We almost have 30 developers in our project and we do have admin user rights for a username and that is being given to all the the users, My issue is somebody deletes one object [ tables, SP] How will i track it down [ who has deleted it], is there anything that...more >>

hi friends, my sp is used to eliminate duplicate data from the table dynamic query inside the sp is select @sql = 'SELECT * into ' + @i_errorDb + '.dbo.' + @i_tableName + ' from ' + @i_oldDb + '..'+ @i_tableName + ' as T where ' + @i_PrimaryKey + '= (select min('+@i_PrimaryKey + ') f...more >>

How do I write the following query to return all records where @custid is NULL SELECT * FROM customers WHERE custid = @custid IOW, if @custid is NULL, I want to return all records. Otherwise I want to search on the specified custid. BTW custid is an integer but what if it where a varc...more >>

I'm really looking for a idea here, not an implementation. I want to find the next item up or down in size from a table. For example, let's say an item fits a measurement of 23 inches. No problem creating a table and writing a sproc to retrieve that item. But I will also want to know what t...more >>

I am having a major problem with the following query and view in SQL 2000 sp4: DECLARE @ConsortiumID int DECLARE @SystemID int DECLARE @HospitalEntityID int DECLARE @LoadBatchTypeCode varchar(4) SET @ConsortiumID=6 SET @LoadBatchTypeCode='ANY' SET @HospitalEntityID=NULL SET @SystemID=NULL...more >>

One of my coworkers needs to programmatically ask a bunch of different SQL Servers for the default location of where they are configured to put .BAK files. -- Peace & happy computing, Mike Labosh, MCSD "When you kill a man, you're a murderer. Kill many, and you're a conqueror. Kill ...more >>

Hi, I noticed that SS2K5 doesn't support distinct aggregation with sql99 olap syntax. e.g. select country, count(distinct userid) over (partition by country), ... from .... I know this is also not supported by DB2/TeraData, but supported by Oracle. Conceptually, I don't see an obvi...more >>

Ok I have a SQL query where I need to calculate teh MAX, and SUM based in a where condition but I want to calculate those MAX amd SUM after the 6 row retuned record, any ideas how can I do that; here is my query Select MAX(tsmRAM) as RAMMAX, COUNT(tsmRAM) as RAMCOUNT, SUM(tsmRAM) as RAMSUM ...more >>

Hello, Please excuse me for crossposting to 2 groups, I am getting an error in SQL Server for a custom SharePoint tool so I feel that folks in either group may be able to help me. I am building a 'recycle bin' tool for Sharepoint Document Libraries. All I did was create a new 'RecycledDoc...more >>

I am programmatically importing lots of rows from dBase 5 into SQL and want to get an idea what the optimal approach would be for this scenario. Currently I'm doing the following: 1) Using OleDbConnection, OleDbCommand and OleDbDataReader to read the rows from dBase tables; 2) Putting th...more >>

I have 3 tables.. - unique listing of all employees... - unique listing of all dates where transactions took place - detail listing of all transactions The detail listing of all transactions has a reference to the employee to whom the transaction belongs as well as the date of the transactio...more >>

Hi folks, I have a problem with my sql query in MS sql server.I hope some would help me out of this. I am trying to retrieve hours column(varchar) and to decimal.which contains data like the below. F(coloumn name) 00800 00800 00800- 00800- 00800 And i wrote the query like the below t...more >>

Hi, I am quering a table that has a bit field. The bit field is used to store a boolean and I want to display this value in a data grid. Can I format(i do not want a -1 or 0 displayed in the datagrid) this value before I return the resultset? Thanks ...more >>

I need to select different sets of records based on the value of the variable collected at run time. Something to the effect of: create procedure Test @BuyerType varchar(20) as Select * from QueriesAndReports..USDataServ where (case when @BuyerType = 'A' then left([Buyer Last Name], 1) = ...more >>

I want to delete the index: How to run the output of: select 'alter table ' + b.name + ' drop constraint ' + a.name from sysobjects a inner join syscolumns on syscolumns.id = a.parent_obj inner join sysobjects b on syscolumns.id = b.id where syscolumns.name = 'rowguid' and objectprop...more >>

hi all, I have a query that gets records from a table. (SELECT col1 from Table X) I would like to be able to add another column in my query that increments with the results so I get results like the following: 1 some value 2 another value and so on... Any help? ...more >>

Making a program that gives tickets for events. I have a stored procedure that checks to see if there are any tickets left. I want to select a group of events where there are still tickets left. The stored procedure has @eventid int, @result int output as the variables. I want to call this from ...more >>

I have a table that now holds data about operator actions. The table is updated each night from a .csv using DTS. The problem is that the rows do not include all the columns I need. I need to caculate a column from two other rows and add it to the first. For Example, I am trying to add "New Co...more >>

Does anyone know of a decent, free SQL formatting tool? Thank you. ...more >>

hey all, let's say i have the following records: Name, Inv#, Desc --------------------- Cust1, null, Desc1 Cust1, null, Desc2 Cust2, null, Desc1 Cust2, null, Desc1 Cust3, null, Desc2 How would I make it: Cust1, 1, Desc1 Cust1, 1, Desc2 Cust2, 2, Desc1 Cust2, 2, Desc1 ...more >>

Hello everybody. I was wondering, if anybody knows how to send records from a cursor which i declared in a stored procedure in SQL Server 2000 to my VB program. Actually, I had a query which was very slow, I decided to run this query in a cursor and get the records one by one and add them t...more >>

Hi I have a backup file. How can I find that to which database this backup is related? I want to do it programmaitcally. thanks in advance ...more >>

Is there a reliable way to order a result set by a column not in the SELECT clause? The logical syntax seems to work correctly in the majority of cases but I found a few in which it does not. Take for example the following pseduo queries: SELECT A.AID,... FROM A JOIN B ORDER BY B.BID ......more >>

I'm looking for an efficient way to export data from an application. The starting point will be a user adds a history record that fires a trigger. In the past I've used this to call DTSRun, however I've always had security issues. This new client is extremly locked down and I don't want to h...more >>

Does anyone know if a .Net DLL will work similar to a VB6 DLL using sp_OA_Create? TIA JeffP.... ...more >>

I am attempting to set up source code control using the SSMS. Following the directions in BOL, I go to Tools | Options | Source Control. The documentation now states that I should see an Environment page. However, the only option listed on my system is Plug-in Selection. On that page, ther...more >>

is really bugging me... Found out that some date fields wont' be allowed on sql server so my syntax is select * from openquery('oracleserver', 'select column1, column2, to_char(date, 'yyyy/mm/dd') as date from table') this works fine when selecting but when inserting this result into a ta...more >>

sorry for posting again but i still have not found the answer. i have a string with a column name. i want to select the column from a table using the string DECLARE @ColName varchar(30) DECLARE @Value varchar(8000) SET @ColName = 'UserId' SET @Value = SELECT @ColName FROM inserted this...more >>

Hi, We have a website on which you can sell and buy products. You can post ads for free and you can search on a variety of parameters as ... - price range (eg between 50 and 100 EURO) - region (eg 10, 15, 20 mile from a certain city or town, ...) - a piece of text (eg bike) - only ads wi...more >>

I have several indexes on large tables that are very selective when I update the statistics manually with a full scan but are much less selective when a small sample size is used (which seems to happen when the statistics are automatically updated). Is there a way to guarantee that when statis...more >>

Hi, I am about to start system optimization and I have a general question about how to proceed. I am a coldfusion programmer and working on a lagre system with a lot of db interaction. Recently, our isp upgraded to SQL 2000 and I started using functions quite extensively for validation a...more >>

Hello, I have a developer who developed the following stored procedure. My question is since it started with a Begin Transaction and if the record exists in the EXISTS statement and it will return an ID without commit / rollback, does he need to either commit or rollback the transaction? ...more >>

Why do the following two select statements not return the same value when run in SQL server 2000? declare @x varchar(2) select case @x when null then 'null' else 'not null' end select case when @x is null then 'null' else 'not null' end ...more >>

Hi, I need to implement paging using stored procedure in SQL Server 2000 In the net i found articles suggesting to use ROWCOUNT for the better performance. but what i see in the samples is that query is executed thrice First to get the count of total number of rows which can be divided by...more >>

I have a problem when the foreign key in a table points to the primary key in the same table. Here is the script to create the tables: CREATE TABLE [Folder] ( [FolderID] [int] NOT NULL , [MasterFolderID] [int] NULL , CONSTRAINT [PK_Folder] PRIMARY KEY CLUSTERED ( [FolderID] ) ON [...more >>

Hi, Consider the following query: INSERT INTO MYTABLE(COL1, COL2, COL3, ... COL28) SELECT COL01, COL02, COL03, ... COL028 FROM MYSECONDTABLE WHERE ID = @ID where I want to copy 28 columns from MYSECONDTABLE into MYTABLE. This SQL statement runs successfully under MSSQLSERVER Desktop Editio...more >>

Hi all, I have a requirement to store hierarchical data in a SQL database. I've seen several different solutions posted here in the past on how to retrieve hierarchical data. In an effort to simplify the storage and retrieval of this data, I was thinking of initally inserting the data i...more >>

I first observed the following behaviour in Access ADP forms and tables but then reproduced in Enterprise Manager. This is variation on a recent post to microsoft.public.sqlserver -I'm posting here because I've further refined the problem. To preempt some advice, the following behaviour wouldn...more >>

hi, i got a problem about update/insert large amount of my data to my database which is also big, more than 2 million records. this transaction is kind of batch and every transaction has at least 1,000-10,000 records. the transaction records are in a csv file and read from there and upda...more >>

Hi Is it possible that two databases have same login name user but with diffrent password?? thanks ...more >>

Hello all! Is there any way to protect the content from the database (tables, user functions, stored procedures) so that no one can see it through SQL Enterprise Manager? Thanks in advance, Hugo Madureira...more >>

hi, can any one tell me the pros and cons of BCP, dts, BULK INSERT regards Aneesh ...more >>

I'm trying to track down a problem with a very simple sql insert statement. INSERT INTO tblRecordingId SELECT 82417, MAX(RecordingId)+1 FROM tblRecordingId This statement has recently begun to cause timeouts (but not all the time). When it does work, I get this plan: Table Insert(OBJECT...more >>

Hi, I have a view set up with an INSTEAD OF UPDATE trigger specified. When I perform an update on certain records, i.e.: UPDATE mytableview SET field1 = 1 WHERE userid = 1234 I am finding that *some* user id's result in the follwing error: "Arithmetic overflow error converting expressi...more >>

Hi, I have a Stored Procedure that Change Event Status to closed. When I run the Job with user (sql_svc) it failed. sql_svc has system admin rights on both servers. I don't understand why it's failing. when tested the script on Development box it works, but on Production it fails. SP Script...more >>

Hi, I have been pulling my hair out for ages on this one, so please help. I am trying to connect to a SQL server throu IIS using impersonation. I am sure I have done 99% of what is needed to do this and still can not get it to work. So what have I done. I have a pure 2003 domain I ...more >>

Hello, I have a database where each record has the following fields: [id], [datetime], [name], [price] I have an array with 1000 records which I need to save in the array. When placing an array record, in the database, I need to check if there is already one with the same datetime. If ther...more >>

Hi i was hoping someone could help me. If i have the following trigger defined: CREATE TRIGGER mytrigger ON mytableview INSTEAD OF UPDATE AS UPDATE mytable SET field1 = ISNULL(inserted.field1, 0), field2 = ISNULL(inserted.field2, 0), field3 = ISNULL(inserted.field3...more >>

Hi I'm working with SQL server 2000,and I want to grant one of my login to have following permission to DB1: login1 should be able to "create table",read from all tables in DB1 and insert and update and delete just for 3 tables(for example: customer,books,teachers) once I selected(...more >>

Hi all Can I set a password for a mdf file for if someone transfer that file to another instance of SqlServer can't open it? ...more >>