In the firm which i am working there is no dba as such so we are facing a big problem with the performance with sql server we have a table which contains lacs of records but when retrieving it takes too much time also ther are 4 to 5 indexes on that tables again adding a new index is a ...more >>

can anyone help me out with derived tables will it be helpful for retrieving data a table of large no of records? is it possible to use derived table in the place of a temporary table ...more >>

i have a table named sauda which contains more than 52 lacs of record the structure of the table is shown below if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SAUDA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[SAUDA] GO CREATE TABLE [dbo].[SAUDA]...more >>

Ok, I am trying to get this to work: SELECT TOP 4 Address, ABS(Zip) - 90401 AS Expr1 FROM HotspotLocations ORDER BY Expr1 I think that it is evident what I want to get - the nearest 4 locations to the zipcode I will enter. The problem is that as long as I have that ORDER BY ...more >>

Hi all, I have a Products table and a Prices table. There is a one to many relationship between Products and Prices as a single product may have multiple prices based on the quantity being ordered. CREATE TABLE Products ( product_id INTEGER IDENTITY (1, 1) PRIMARY KEY, produ...more >>

What I wanted to do here is to optimize performance by using getstring. But I wanted same record to listed once and also listed in order as well. But this code result in error, I think the problem is to do with the item I use for order by doesnt get selected. I wonder how I could fix this? sS...more >>

I have the following sample data and am wondering how to query it to get the below result. ID Month Value 1 2 100 1 3 100 1 4 200 1 7 300 2 1 400 2 2 500 2 6 ...more >>

i would like to build a master .sql file that builds up my database from scratch ... 1. runs various .sql files each which creates a table 2. runs a .sql file that creates various foreign keys 3. imports various .txt files into the various tables how to do this ? ...more >>

Hi, vb6+mssql2000 i want to disable updates by set cn.mode=adModeRead. inspite of this, i can do modifications without error... any tip? what should i check? ...more >>

declare @str varchar(50),@lev1 varchar(50),@lev2 varchar(50), ....,@levn varchar(50) set @lev0 = 'AAA' set @lev1 = '123' .... set @levn = 'BCD' declare @counter int set @counter = 1 while @counter < n begin set @str = '@lev' + cast(@counter as char) select @str set @counter = @counter +...more >>

Hi There, I am trying to return the sum of qoh,s7days,sstd,rprice columns and the distinct value of colour,colourway,size but I'm not sure how to do it properly. I have tried using unions but It returns the data underneath the column I grouped the results by. is this possble to to in native sq...more >>

Hi All, I am trying to return a parameter to a web page thru an SP. I keep getting an error: Server: Msg 201, Level 16, State 4, Procedure GetPermissions, Line 0 Procedure 'Getlogins' expects parameter '@permission', which was not supplied. My Code is: Create Procedure GetPermissions ...more >>

I've a stored procedure which I changex a while back because I am convinced it was causing me problems. I've just come back to it and am trying to understand why I changed it and can't remember why. Here is what I used to have... SELECT Field1 FROM [MyDatabase].[dbo].[TableName] WHERE (...more >>

I am new and struggling and dont know if this is even possible but here goes: How can I modify the following query to have the display show the number of items sold for EACH between a date range? The way it runs now is that I get a line for each item with the total sold between the dates. w...more >>

I recently imported several thousand records from Excel files into our system but encountered a problem with Employee IDs. Our employee IDs are usually numeric in nature, but stored in nvarchar. Unfortunately during our import, we found that many of the employee IDs were converted to scientifi...more >>

SELECT @recordCount AS RecordCount, @contactCount As ContactCount, @matchedRecordCount As MatchedRecordCount, @matchedContactCount AS MatchedContactCount FROM SampleSourceProfiling I'm just not seeing it. All four variables are declared as INT, and have been assigned ...more >>

I've read some posts on the Internet about a possible overflow condition that can occur using @@CPU_BUSY after the server has been up and running for a while. Some posts seem to indicate that the number of days hovers around 25, but I assume this number is related to the CPU activity on the serv...more >>

Hi, I have a DB that is having some performance issues. I got a new machine that has 3 separate SCSI drives. I want to store the data on 1 drive, the transaction log on drive 2, and the indexes on drive 3. This machine is also a multip-processor machine. I need some assistance here. I want ...more >>

Hi All How to View Sal Server Stored procedure query after sending parameter any idea please help me ASAP I am using adobe Command object thanks from khurram ...more >>

Hi I am trying to loop through a series of tables and then in each table I wish to go through each recods, looking at the IsDeleted column of the record for occurances of 'True' I have 21 tables in one database. The DB is called ipvc_ee and each table begins with 'ipvc_ee_'. Examples...more >>

Hi, I need to write a SQL script to create a login in SQLServer and assigned some permissions to certain tables. I am using then "sp_grantlogin" stored procedure to create the login, but if the login happens to exist, the script fails with an error. I need to be able to determine if the login al...more >>

hi i need to select distinct on the first coloumn of a table of 2 coloumns SELECT DISTINCT id, num FROM #tmp this is my data, i need just 2 rows, one for EH and one for SH EH 32 EH 33 EH 34 SH 1 SH 2 SH 3 Thanks a lot Carlo ...more >>

Hello. I am writing an ASP.NET application and I am currently display a list of records. However, I want to display only X number of records and provide a paging mechanism so the user can step through the records. The user is also able to sort the records by any number of fields. How can I str...more >>

Hi all, My question is about triggers. I've procedure that inserts some records to the table. This table has trigger on itself. In my procedure I would like to check whether the trigger executed ok or wrong. I know that I can use Raiserror and Rollback inside the trigger but I'm looking ...more >>

Hello all, I was wondering if the following is possible (this example doesn't work, but it should show what I'm looking for): declare @intScale int declare @intPrecision int set @intScale = 5 set @intPrecision = 3 select cast('3.3' as decimal(@intScale, @intPrecisi...more >>

Hi All. The Visual Studio help says that I can use the Server Explorer to create/edit stored procedures. The example says to right click the server explorer database "stored procedure" node and a popup menu will enable the creation of a new stored procedure. When i do this - i only get "re...more >>

For an audit trail: I have to be able to acquire the new value and the old value for an ntext field that may be changed. It has to be done within a trigger. There is no way to get the data from ntext/image fields from within a trigger because it resides in the inserted/deleted tables. I a...more >>

Hello All, I am very new to the SQL world and I need to do something that I've found in pieces but I am not getting it put together correctly. We have 2 tables that I need joined together and exported to Excel. I can create a table, I can do the join, and I can export to Excel. How do ...more >>

Hi All ! Can someone please verify if it is just me or sql server ? select IsNumeric('-') returns 1, but select cast( '-' as numeric(3) ) gives me an error basically saying that it is not possible to convert from varchar to numeric. While select cast('-' as integer ) results i...more >>

Hello, Does someone has a sql statement for me how to count the records in tables by using the view information_schema? I know that all view names are stored in information_schema, so i want to see how many records are in each table/viewname. Thnx ...more >>

Hi All Application Background: I have written an application that will have 10 users. It has 2 main function. To save and edit records in a MS SQL Server 2000 DB. And to read these record and use the information in them to perform actions. Each record is an individual instruction. The most im...more >>

Hi! > > Please kindly help me on this, Is there any code of > removing the trailing zero's in sql just like for > example I have my data.. > > 89.890000 the output I would to see is 89.89.. > 89.00 the output I would like to see is the same as 89.00 > > and the data type is numeric 18,6...more >>

Hi sql gurus, Basically i am a developer working on vb.net/sqlserver I am working on sql server almost 2 years in the meantime i got very fascinated to sql server so i want to move to sql server dba. can you please guide me through your experiences.. how to move to dba. wat skills and...more >>

Hi, Is there any sql command that assign the ownership of all database queries to DBO? Thanks, Alan ...more >>

Hello everybody out there i looking for information regarding the performance monitoring of sql server.. what is required to do is to find the No of Transactions per day for a particular database. what i know is that there are @@trancount system variable and tool available in OS like Perform...more >>

Ok I have a zillion stored procedures with meaningful names, wow! I can now set user level permissions, double wow! Ok there does not seem to be a easy way of doing this in the enterprise manager. The base way I have found is to open the user name and then click each stored procedure "exe...more >>

Hi All, I have a procedure in which I am doing an operation create procedure foo (@csv varchar(1024)) as SELECT * FROM <tbl> where <col> not in (@csv) Returns error!!! I was wondering can we do the above operation TIA ...more >>

I have a table that contains anywhere between 4-5 years worth of data. Basically, it's soldto, shipto, dc info on it. One problem I've run into, is that over time, the dc field has changed from one thing to another on the source tables (the table I have is built from 4-5 others). For some c...more >>

I have a SQL server running on a SAN, and 16gigs ram. Every morning the machine runs out of locks - how would you recomend I go about sorting this out? ...more >>

I have been tasked with importing about 8 different Excel files into SQL. Due to the typical Excel methodology, the user added columns where we would need the data in multiple rows. Of course I can write my sql to extract the columns using something like select ID,column1 from Excel UNION ...more >>

Can anyone tell me how to modify my query to subtract one day from the "and left(a.dateentered,11)= Left(getdate(),11)" line? Thanks! SELECT type, a.clmno, batchnumber, returned, a.dateentered FROM dukeaccount..tbledipporepricingtracking a join dukeaccount..clh b (nolock) on a.clmno ...more >>

Hello there I have query that runs on sql On that query i would like to create an identity column How can i do this? ...more >>

When a job fails, where do I view the failure reason ? ...more >>

I have pair of batch processes continually and concurrently accessing the same table. One is doing inserts of "unprocessed" records, the other is selecting these freshly inserted "unprocessed" records. Immediately after each select, the records in the result set are updated as "processed", using...more >>

Hi, Please could somebody help with the following. I have a table which contains information on a list of tables within my database. These tables are created at runtime by my desktop application. I want to know if its possible to create a trigger on this table so that in the event of a row be...more >>

Hi, I need to restore a test database everyday with fresh data from a production inviroment. My problem is that I want to restore the backupfile created with the maintenanceplan. But this backup file has a filename who changes every day beacuse sql puts " _db_yearmonthdaytime " in the ...more >>

Dear Experts I am running following Query from SQL 2000 UPDATE [14.15.93.25].[CORPDB].[DBO].[ItemMaster] SET ItMas_IsReffered = 1 FROM [14.15.93.25].[CORPDB].[DBO].[ItemMaster] INNER JOIN ItemMasLocal ON ItMas_Id = IMM_MasId Go While Executing It is giving me following error "...more >>

Hi there, I have a table that includes the fields: UserName, Title, CourseCode (all varchars) EventNum (smallint) & EventVal (smallmoney). The primary key is a combination of UserName, Title & CourseCode, EventNum is a number up to 99 and EventVal is Cumulative (ie if Event 1 = £50 and Eve...more >>

I'm trying to write my first stored procedure using transactions. First of all, can anybody tell me if I am going about it the right way, and secondly, if I need to return the result of the transaction to the calling application (C# in this case) would I just declare an output parameter and set...more >>

Dear Experts I am running following Query from SQL 2000 UPDATE [14.15.93.25].[CORPDB].[DBO].[ItemMaster] SET ItMas_IsReffered = 1 FROM [14.15.93.25].[CORPDB].[DBO].[ItemMaster] INNER JOIN ItemMasLocal ON ItMas_Id = IMM_MasId Go While Executing It is giving me following error "...more >>

Hi gang, I’ve almost got this working but not quite. Maybe I described the problem incorrectly. Here is the code to produce and populate my test table CREATE TABLE [tblEntitlement] ( [strLogonName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [intYear] [int] NOT ...more >>