I have table as follows: CREATE TABLE [dbo].[Table1] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [ValType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [MainVal] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [CodeVal] [varchar] (5) COLLATE SQL_Latin1_General...more >>

Is there a way to tell when a record has been added to a table? And by whom? I have data in a database that is obviously new, as the records do not exist in any of our backups, but the user has dated them with old dates. I am sure someone is entering these records in erroneously, but I canno...more >>

What could cause a SELECT DISTINCT to still show multipule items of the same item in dropdown lists? I use alot of CASE and DATEADD functions but I do not see anything worng. SELECT DISTINCT CATEGORY, DATEADD(m, - 6, DISCONTINUEDDATE) AS SUNSET, DATEPART(YEAR, DISCONTINUEDDATE) AS Year, ...more >>

Hello, I am working on a web site which will use SQL 2005. I am planing my first SQL database and I am looking for advice. 1. There will be two types of users: students and professors. 2. Both users types will have login information. (Username, Password, AccessLevel) 3. The remaining in...more >>

I'd like to build an application that will react to specific changes to data in a set of tables in a database. The application would replicate these data changes to another database. The target database won't be SQLServer. Neither is this simple replication, at times the application will ...more >>

The quarter function returns 1 to 4 but is based by default on a year starting jan1 and ending dec 31 as far as I can tell. I need to find a way to determine in what quarter of a year a date is when a year starts and ends on other dates (eg May 1 and ends April 30) as is the case in financi...more >>

In my database i want list of all stored procedures where i have used a specific field. Is there any way to do this.. any system sp which does this ...more >>

Hi, I need to pivot and sum a resultset (based on a particular column) but I don't know how many cols the pivot would result in. Say you got resultset: [C0] [C1] [C2] 1 100 OO 1 200 KK 2 245 OO 2 244 OO 3 188 KK 4 124 A...more >>

I have a situation where I have two tables, one in Access and one in SQL Server. There are several hundred thousand rows and the tables should contain the exact same number of rows. They may not though and the count could be off by one row or a hundred rows. I need to be able to identify ...more >>

Hello, I created a very simple database with only one table (Records) and on that table only one column (Category datatype nvarchar). I am trying to use the AddNew ado example found on msnd but it always inserts a null value instead of the value I am trying to insert. All my HRESULTs say S_OK b...more >>

Is this possible? I have a column which has data with HTML formatting. Is it possible to remove the tags within a Stored Procedure so that it displays nicely in an Access Report? This is my SP: ALTER PROCEDURE dbo.sp_Phat_Beats AS SELECT fldcat, flddescript, fldtracklisting, fldprice,...more >>

I have a table that I use a DTS to insert a date and cont value each day. I add a computed column called isweekday that gets a Y or N depending in the date. I now want to add another computed column for the mean of the counts over the past 30 days, one value for based on weekdays and another...more >>

hi i'm new to sql server. i'm finding that when a connection sql server is idle for sometime, it gets disconnected ??? is there a session timeout for sql server connections? what's the default value for this? currently, i'm refreshing the connection every 100 seconds with a meaningless...more >>

I was looking for some info on the major differences between a clustered and a non-clustered index, but I couldn't find anything in the MS knowledgebase. Which is more efficient? I am creating reports on an audit table that has approx 20,000,000 rows in it. I indexed 3 columns as non cluste...more >>

Hello Everybody. Not sure how sql server query optimizer works ? if i have a query like following. select * from table where field1 = 'A' or field2 = 'B' or field2 = 'C' so how the above query will execute ? if field1 = 'A' is true, the other part of the query field2 = 'B' or field3 =...more >>

How do you open log files (LDF)? Thanks!...more >>

Can you use case statements in a where clause? if so, how? Thanks!...more >>

Hi, I want to run an update script where a field in a table in a database on a server is equal to another field in a table in a database on a seperate server. Here are the details: Server 1: Server name - Server1\Logi Database - Ascent Table - _customers Field - email Server 2: Ser...more >>

I posted this question yesterday with out good table structure and sample data http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?query=sqlster&dg=microsoft.public.sqlserver.programming&cat=en-us-technet-sqlserv&lang=en&cr=US&pt=261BA873-F3AB-420E-96D6-E3004596A551...more >>

Been working in Visual Studio 2005 and got to the point where I needed to build a native program that interfaces with SQL Server via the bulk copy API. So, I d/l'ed SQL Server 2005, installed it and tried a simple example from MSDN from the bcp_bind documentation. Fails because it is unable ...more >>

Hi I have a large update batch to make on our database which will run overnight when no users are logged in. I always use a Table Variable instead of a Cursor to conserve resources, but in this case resources are not a problem but speed is. Which would be quicker: cursor or table variable...more >>

I have 2 large tables that I'm selecting off of. part of my select is to get the number of stores visited. The table that has this information has the store names as columns. If a store wasn't visited there will be a null value. I need to be able to count the number of fields that don't have the...more >>

Hello - My OS is Windows 2000 Professional. I downloaded files for MSDE and installed them. I was not able to get Sql Server running. There was no uninstall exe and it did not show up in my Add/Remove programs. I then tried installing Sql Server 2005 Express. It did not run either. ...more >>

I have this select query which returns a date. I would like to be able to call this from a stored procedure and have the result appear in the result set from the stored procedure. i.e. SELECT *, CalcDate FROM Table WHERE somedate = @dte The CalcDate field would be the c.dt in the follow...more >>

delete from OrdersAudit where OrderNbr not in ( select OrderNbr from Orders) delete OrdersAudit from OrdersAudit,Orders where OrdersAudit.OrderNbr <> Orders.OrderNbr How much do you think the performance difference will be? There's clustered index on OrderNber for both tables....more >>

Hey Folks, I'm having a problem that confuses me. We use MSMQ to scale our database calls. We have a .NET listener that grabs items out the queue and processes them calling a stored proc. This process has backed up significantly lately and we've been looking for a cause. Yesterday for an un...more >>

I have a table that has 3 fields..there's a clustered index on all 3 fields..... The data in this table can change a lot depending on the changing state of the system. For performance purposes, I can't have a bunch of triggers throughout the system that fire every time the DB state changes...more >>

Hello, Can I put two updates withing an update strored procedure? Is my syntax correct? Why I want to do this: My datatable is a union of two tables. It currently works well except for the fact that I have to update the records of corresponding tables separately. All records that came f...more >>

I would greatly appreciate any help that you can provide. I have the following dml/query (part of a stored proc) that runs perfectly in SQL 2000. However, when I run it in SQL 2005 I get the following error: "An aggregate may not appear in the set list of an UPDATE statement" Here is the d...more >>

Ok gentelemen we have come accross a little issue using the 2005 platform which is a very strange occurance. We can open a stored procedure that has been saved and at the top of the SP we have the following: USE DatabaseName GO Followed by if exist statment etc etc. Well we get the fo...more >>

Hi there! I created this view: select pk, name, ( select count(*) from dbo.Orders ord where ord.cust_fk = cust.pk ) as order_count from dbo.Customer cust When, being in the Design of the view in SQL Server Enterprise Manager, running it, I can make changes to ...more >>

I have learned much about SELECT from helpful replies to my posts, but am stumped on this one. First here is the DDL: SELECT f.FinanceClass, COUNT(*) AS TotalCharges, SUM(dbo.udfAgeAmount (0, 29, DATEDIFF(day, service_date, GETDATE()), units*fee)) AS Amount00, SUM(dbo.udfAgeAmount...more >>

I need to copy a significant amount of data (1-2gb) during work hours to another database for some lengthy numeric calculations and a report. Phantom rows inserted during the copy operation would be a problem but a serializable transaction would impact availability. I was considering using BC...more >>

I have the following table structure: PK_Column1 PK_Column2 IndexedColumn Column_ABC Column_XYZ Does SQL Server 2005 uses the IndexedColumn index to find the MIN and MAX values in the following select statement: SELECT MIN(IndexedColumn), MAX(IndexedColumn) FROM MyTable WHERE PK_Colum...more >>

Dear gurus, I am pursuing for a statement or function which allow me change the collation at database level. Thanks in advance and regards, Enric...more >>

hi all, i have a table with two fields name, date_hired a , 2004-04-16 10:02:31.000 B , 2004-04-18 20:02:31.000 i want a select statement that returns a , 2004-04-16 00:00:00 B , 2004-04-18 00:00:00 -- thanks, ------------------------------------ Jose de Jesus Jr. ...more >>

Hi All, Facing a problem while executing a user defined function (UDF) which resides on another sql server ( we have access on this sql server to execute the UDF). This UDF accepts a varchar parameter with max length 8. When we execute the function as Select * from SERVERNAME.DBName.db...more >>

I need a little help with using parameter values in the where clause with the OpenQuery statement. What would the syntax for the following be if 10 was a variable? And does the syntax change depending on the datasource? In my case I am querying an Oracle database. But I can't get it work on a...more >>

Hi, When I run the job, I am not sure whether it could be done at the table level to log users out. I would not want to keep them out of the whole database...only the tables I are updating. Also, once the job is running, I would like to prevent any users from accessing the tables I am upd...more >>

Hi This is my first post in this newsgroup and I am not sure if I have chosen the correct group. Please advise if a more appropriate group exists. I am planning to set up replication to a standby sql server using RYO log shipping in an application where a move to a backup site may be require...more >>