I have two tables, Foo and Bar. Foo contains a foreign key to Bar's primary key (bar_id). Bar is structured to allow a parent/child relationship to itself through a foreign key (bar_parent_id) to another record in Bar. This relationship is limited such that any Bar record that has a parent cannot itself be a parent. However, any given pa...
i am using sql server 2000 i want to create a table having id as primary key and one more column having unique key constraint. ...
I am hoping that someone has attempted this before and I can get some advice before I go any further. I am looking to produce something similar to a crosstab query in sql-server 2000. I have a table structure similar to the following: Item Item_Parameter Parameter id item_id id desc parameter_id ...
I need to set up this question a moment. Let's say you've created a database called ParameterTesting and created a table and populated it like so: IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StarWars' AND TABLE_SCHEMA = 'dbo') BEGIN DROP TABLE dbo.StarWars PRINT 'Dropped Star...
Hi I am using ASP Classic and SQL Server 2000 to create dynamic websites. I am a bit confused about when to use a recordset object and when to use a command object when querying the database. I was told that if the stored procedure would be returning records from a SELCT statement then I should use a recordset, however if I am up upda...
I've perused the threads here on migration from SQL 2000 to SQL 2008 but haven't really run into my question, so here we go with another one. I'm building a strategy to move specific SQL 2000 databases to a new SQL 2008 R2 instance. My question comes with regards to the best method for transferring the schema and data. One way I know ...
I have a query that queries a linked SQL server 2008 database and joins data from a table in that database to a table in SQL server 2000. It was working find until one of the columns in the SQL server 2008 database was changed to varchar(max). I received an error and I fixed it by using CAST(varchar(max) column AS varchar(50)). Now my ...
Please consider the following scripts. Create Table: IF OBJECT_ID('Colortable') IS NOT NULL DROP TABLE ColorTable CREATE TABLE Colortable (Color VARCHAR(32)) GO Insert some values: SET NOCOUNT ON INSERT Colortable SELECT 'red' INSERT Colortable SELECT 'orange' INSERT Colortable SELECT 'blue' INSERT Colortable SELEC...
Hi all I'm looking at an execution plan from a troublesome query. I can see that 45% of the plan is taken up doing a table scan on a table with seven (7) rows of data. I am about to put a clustered index to cover the columns in my query on a table with seven rows and it feels...wrong. How can this part of my query take up so much o...
I have set up a stored procedure for inserting a single record into a table. The reason for this is to create a general interface for people to use so I can change the table structure that it points to without anyone noticing or having to change code on their end in the future. The problem occurs when the user has to insert many records...
I have very simple query that calls a UDF which splits a field by comma. The query is select top 10 * FROM Emails e WHERE EXISTS(SELECT TOP 1 1 FROM dbo.fn_Split(e.committees,',')) When I run/parse it, I get: Msg 170, Level 15, State 1, Line 4 Line 4: Incorrect syntax near '.'. I think it must have something to do with SQL 2000. If...
Hi! I've been browsing the forums a while trying to find a solution to my issue. I have a table in SQL Server 2000 called result with the following data; City Price DepDate RetDate Barcelona 145 2010-05-15 2010-05-20 New York 400 2010-06-20 2010-06-20 Barcelona 160 2010-05-17 2010-05-22 New York 325 2010-05-...
I have two tables that I want to join into one table and use a TypeID to differentiate them. Let's say the types are A and B. The Tables are A_Level and B_Level A's Table looks like Level Level_ID Description B's Table looks like Level Level_ID Level_Desc A's Level_ID is referenced from Table C as Level_ID B's Level_ID is...
I have created a stored procedure in SQL Server 2000 and within the stored procedure I have created a number of variables that I want to return to the asp classic script. I have tried declaring the variable like this: DECLARE @output int OUTPUT But SQL Server then says that I cannot declare an output variable that way. So instead, I...
I'll try to avoid describing the background here. I now have a query result (not a table) which contains rows like this: ID SP1 SP2 SP3 SP4 SP5 SP6 SP7 SP8 1 null null 2500 1400 700 null null null There may be leading and/or trailing null values around a section of non-null v...
We're building an application for a client with the assumption that they'd be upgrading to a minimum of SQL Server 2005 from SQL Server 2000. We're finished our application, built on 2005, and are ready to integrate. Turns out that they're not going to upgrade their DB server. So, now we're stuck with trying to sort out what will break...
I currently have a code table containing a list of types (Type_ID, Description), but they are saved in another table as ID;;ID;;ID...etc I am looking for a script that will take those ID's and place them in a relationship table corresponding to there Type ID For example in table A the Type_ID entries could look like: 1;;2;;4 1 ...
This is a 2 part question. Question 1: I am trying to create a foreign key on a table where I need to turn off the "Check Existing Data on Creation or Re-Enabling". I know theres an option visually but I'm looking for a way to do it programmatically. Is there anyway to do this? Question 2: I have a code table and two tables A and B tha...
Please can someone tell me how to connect to sql server 2000 with asp page using vb? ...
I'm trying to update a date dimension table from the accounting years table of our ERP System. If I run the following Query: SELECT fcname FYName ,min(fdstart) YearStart ,max(fdend) YearEnd ,max(fnnumber) PeriodCount FROM M2MData01.dbo.glrule GLR GROUP BY fcname I get the following data: FYName YearStart...