Hello, I have three tables in my existing database, (in a SQL Server 2000 server). - a "Students" table, - a "Classes" table, and - a "StudentIsInClass" table. The "StudentIsInClass" table is the relationship table linking a record in the Students table to a record in the Classes table...more >>

Hi, When I order by a field the sorting is wrong for Norwegian. The Å comes before the A for example. I have tried different collation settings of the database, but cannot find the correct one. Anyone ? Using SQL 2005/SQL 2000 David ...more >>

Hello, i'm (sql programming newbie) goin' crazy with that: I have several tables with a lot (10000 and more) of rows on a SQL2000 server. Before i do a select or anything else on the tables i wanna return the number of rows via stored procedure or a function. How can i simply count the number...more >>

My SP has this code but apparently Crystal Reports doesn't know how to interpret the quotes in @query: fetch next from c2 into @person_Id, @full_name, @charge, @encounters_id while @@FETCH_STATUS = 0 begin select @query = 'insert into #results_table ' + 'select ''' + @person_ID ...more >>

I'm using datepart combined with a count aggregate to count the number of weeks in a certain time period. Problem is, my employer starts each week on Saturday. The T-SQL version of Datepart does not support a StartOfWeek parameter. This defect is screwing up my reports. Does anyone have ...more >>

Is there anything I can do so that I don't have to drop an SP in order to create it again? Thanks. ...more >>

i wish SQL defined a new aggregate operator (e.g. SUM, AVG, COUNT, STDEV, etc) that would concatenate column values as strings. Consider Transactions Table LCTID TransactionDate Amount CurrencyCode ===== =============== ====== ============ 1 3/12/2006 450.00 C...more >>

Given a Microsoft Access "client application" (.mdb file with only forms, reports, queries, and business logic - but no data). 1. What *specific benefits* are to be gained by having it connect (link) to a SQL Server database (as opposed to connecting to another MS Access database on a file ...more >>

Hi all, I have scheduled a job to run everyday at 3am. But when I right click on the job and select "View Job History...", I get this message - "There is no history information for this job.". The Sql Server Agent service is running and the job is enabled. So I don't understand what else c...more >>

I'm using Visual Studio 2005, C#, and SQL 2005. In Visual Studio, I've created a Database project where I've written some simple CLR Stored Procedures. I can deploy and call the simple CLR Stored Procedures from my host WinForm application. This all works great. I'd now like to write a CLR ...more >>

Hi, I wanted to know if theres a fast way of knowing which columns in a row have changed. I have 2 versions of rows-old and new. My table is as follows CPK compositePrimaryKey---combination of 3 keys versionID uniquidentifier param1 int param1 char(10), param3 bit --etc my new ...more >>

Hello: How can I query a table for the nearest values (i.e. next record above and next record below) to the query parameter? For example let's say that I have a table with the following colums: Pressure - 120,115,110...50,46.4,42.8 Temperature - 0,10,20...1200,1400,1600 Value - 1,2,3...10...more >>

Can someone explian why I get a return of not a valid date when using activeX vbscript for DTS: Select (Date()) Case (IsDate(Date()) msgbox "OK" Case Else msbox "I cant figure this out" End Select msbox "I cant figure this out" pops rather than msgbox "OK"...more >>

I am trying to create a cursor that gathers a tag value and column name for use in a sp_execute sql statement. The text is as below SET @qry = N'SELECT @tag_value_out = ' + CONVERT(nvarchar(25),@column_name) + N' FROM FSFORMULA WHERE formula_id = @formula_id_in' But when I use PRINT on t...more >>

I accidently changed the directory sub folders orders in the c:\Program Files\Microsoft SQL Server\MSSQL.3 folder (i believe it's for reporting service) now the reporting service won't start. can anyone tell me what the sub direcotries should look like under that directory? thank you!!! ...more >>

Hello, I created a simple table for data entry where I can manually edit/add data to the table in Enterprise Manager. But when I open the same table in an Access it is locked. I cannot edit/add data to the table. Does anyone know what I need to do to make the table editable in the Access...more >>

Hi All, We're porting from one forum package to another. In the old system, there was no table for forum Threads; instead they seem to be implied in that a new posting is given a new ThreadID in the Posts table, and all subsequent reponses have the same ThreadID. In the new system there is...more >>

what is faster : 1. select count(*) 2. select count(pk-name) ...more >>

This is probably trivial but I am missing something. I have a table with Name | Alternate Name | ... Is it possible to have a SELECT statement that returns Name except when Name = "Alternate" then it returns the Alternate name? Name | Alternate Name Joe | Null Bryce ...more >>

I need to store the time in a table ("10:00:00 AM") and then compare just the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL. I know this is simple as heck but because I am a newbie I am stumbling. Can someone provide me with a sample of this? Thank you. ...more >>

Using: SQL Server 2000 Database Designed by: StoreFront 6.7 (LaGarde) In StoreFront's design, each order that is recorded to the database has a OrderNumber (not the primary key) and is foreign to several other tables like OrderAddresses. The OrderAddresses table records a unique ID, the Orde...more >>

Hi, I ahve the followign trigger. In this I want to set the 2 Insert statements at the bottom, to execute concurrently. How can I do that? CREATE TRIGGER [Identity_Trigger] ON dbo.WyethDataCard FOR INSERT AS DECLARE @CompOrderNo VarChar(30) DECLARE @Sno int DECLARE @EndUser varc...more >>

Here is what I have that seems kind of verbose using Case When Month < 10 and day < 10 or Month < 10 and day > 10 or Month > 10 and Day < 10 or Month > 10 and Day > 10... Is there a less verbose way to do this? declare @d datetime --set @d = '10/17/06' set @d = '3/17/06' select case whe...more >>

This is actually an MSDE question. I have a small (12 MB) database. I don't think it's full, but in any case it's set to grow without limit as necessary. It is accessed only by MSDE on the same machine, and then only by one application, via ODBC. Everything is local to this machine. Eve...more >>

How does one query tables from two different databases and join them?...more >>

I used to have an Access 2k query that formatted this date field: Format([PrDateStart],"yyyy/mm/dd"" 00:00:01""") AS ProjectStartDate That gave me the date as this: 2006/03/30 00:00:01 Recently, we moved the data to a SQL 2000 table, so now I need to create a view that gives me this dat...more >>

Hi, We have a SQL Server 2000 database on an Win2k server. I'd like to offer the facility for our customers to add records to our database from their programs. They just want to make one function call to create a new record on our database. This call would return a success or failure code...more >>

How to include a Count? I have a table A, B C TableA TableB TableC idA idA idA I need to query against the TableB where TableB.dt_Date = "date" returns me the all fields including idA. Here its fine. But, I want to know how many idA related with the results from the que...more >>

I have a client application written in C++ to takes an array of doubles and stores it into a SQL Server 2000 database as an image data type. We just upgraded to Visual Studio 2005 and SQL Server 2005. Can the Reporting Services take this image data and convert it to an array of doubles so th...more >>

When you issue an alter table statement to add columns it will add them at the end of the table. Is it possible to specify a column id and tell it where you want the column added? Say you have a table with 10 columns and you want to alter the table and add a column into the middle of the t...more >>

A little SQL problem I'm having – I want to update the MaxInd and MinInd columns of a table to indicate which rows have minimum or maximum event date for a given URN. It is important that only one record per URN be marked as the Maximum or Minimum but it is possible that a single event ...more >>

Hi, Simple insert of one table into another, but I was to add a Date into the second table. I retrieve the date as @ratedate but I can't seem to add the date into the query below so it will insert into tbl_pre. So when I try to run this it tells me that the query does not match the column...more >>

ok i know i shopuld really put up all the code, but was hoping this would be a simple one i have a table called table 1 on db1 that contains fields named sortcode, accountno, licence on DB 2 i have two tables tb2 tb3 , tb2 has a field called sortcode and a fiedl called id tbl3 has a fiel...more >>

Hi there, I need to copy data from MSAcess to SQL Server (from within SQL). Does anyone have a script that I can use to do this? I only need to copy certain tables from the Access db to a SQL db. Thank you for your help. Regards,...more >>

Hi I am using vb6, ADO 2.8 and SQL Server 2000. Each time I run a stored proc (updates and inserts) I find that I get a 20% increase in execution time, both through VB6 and Query Analyzer. If I Disconnect and reconnect before executing the performance remains the same. Any ideas where I sho...more >>

Heres an extract of a stored procedure creating a column. Path = FileLen([CacheServers].[CachePath]+ (left([DOCUMENT].[PHYSICAL_DOC_GUID],6))+''\''+[DOCUMENT].[PHYSICAL_DOC_GUID]+[DOCUMENT].[FileType]) Reult: \\comp-ap-70c\Imxxs$\data\docs\70393C\70393CE0EC6D11D8BB64000D568A4637.tif Th...more >>

I have two processes will continue to get the number from a table. After each get, the number should increment by 1, and the two processes need to be getting a unique number. Is it possible to avoid the two processes get the same number? ...more >>

Is It possible that, if there are 5 rows selected out, each row will automatically have the number from 1 to 5? for example, after select query, results: --------------------------------------- 1 Tom 100 2 May 93 3 Apple 87 4 Johnny 72 5 Noel 67 ...more >>

hi Guys, I've tangled in serious problem, Is there any way in which we can autoincrement in varchar value like i've one column in my table called PayCardId that should be in 9 digit say '900001@@@', now requirement is this to increment in same format but...more >>

I am taking data from a form and passing it to a stored procedure to insert into a table. If there is nothing entered in the field I receive the following error message: "Parameter object is improperly defined. Inconsistent or incomplete information was provided" The stored procedure is: ...more >>

Long story to short. Basically, we have two sites, one is Oracle for OLTP and the other is MSQL for reporting. Every night, the MSQL will pull the last 5 days through, process and delivery report. The mechanism works fine for new and updated data, yet cannot address deleted data. The script, s...more >>

Hello, How can I stop and start my SQL Server 2000 remotely from my c# code? At first, I was going to use WMI, but there is no SQL WMI provider installed on the server machine (and I can not install it). So, any other sugestions? ...more >>

Dear all, Does anyone know how to migrate .PST files into Sql Server? Is there any odbc, driver or something like that? I think that in sql2k there was no like that but I was wondering about Sql25k Thanks for any suggestion, -- Please post DDL, DCL and DML statements as well as any error m...more >>

I have a case when the same query returns two different results, depending on the generated exec plan. I managed to reproduce it on different servers and databases. Heres the code, the trigger should generate new number max(Broj)+1 but the result here is 1 because join in the selec max() part...more >>

I use below mentioned template for creating sps,=20 if exists (select * from sysobjects where id =3D object_id(N'[<SP = NAME>]') and OBJECTPROPERTY(id, N'IsProcedure') =3D 1) drop procedure [<SP NAME>] GO SET NOCOUNT ON GO SET QUOTED_IDENTIFIER ON=20 GO SET ANSI_NULLS ON=20 GO CREAT...more >>

I was given the following question on a quiz the other day. Actually, the intent of normalization is not performance gain and, depending on the specific data model, the degree or normalization does not necessarily impact performance. Given the available options, I chose 3NF. "What is the ma...more >>

can T-SQL allow table variables to be passed into user defined functions for processing? i'm trying to work a function with that but sql2k doesn't seems to allow it ...more >>

I'm trying to find out what's the fastest way to execute such a query. Presumely I have 1 denormalized table (TableTest) with 2 columns A and B A - Nvarchar(50) B - smalltimestamp I have 10 records in this table out of which column A has 2 distinct values (Test1 and Test2) and B has 10 dis...more >>