Multiple Cascade Option In SQL Server 2005

May 26, 2008



Hi All,
Use Case1:
Just wanted to know if there is something like multiple cascade delete option in SqlServer 2005.
My table structure is similar to what is given below

create table test ( id int PRIMARY KEY, tname nvarchar(55))

create table childTest ( id1 int REFERENCES test(id) on delete cascade,
id2 int REFERENCES test(id) on delete cascade, tname nvarchar(55))......

The above creation fails for childTest since both id1 and id2 reference test with ondelete cascade.

As per Books online: The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree containing no circular references. No table can appear more than once in the list of all cascading referential actions that result from the DELETE or UPDATE.

Doesn't look like a circular reference here. Wanted to confirm if it is because test is being referenced twice in the childtable.

My requirement is if a row is deleted from test, it should delete matching rows from childTest(id1 and id2). Since delete cascade is not being allowed for both columns , I have to handle the delete in id2.

Use Case2:
Also can I have INSTEAD OF DELETE and Cascade delete on the same table?
I have a cascade delete from table1 to table2. which means when any row from table1 is deleted corresponding rows from table2 are deleted. and on table2 I would like to have an instead of delete where I would like to delete rows from another table say table3.

Thanks & regards
Sunil

View 4 Replies


ADVERTISEMENT

SQL Server 2005 No Cascade On Delete

Apr 12, 2006

I am developing an application with ASP .NET 2.0 using SQL Server 2005. I have a very detailed set of relational tables I would like to use to store my data, however I would like to use the built  in Membership functionality of .NET 2.0.What I've done is created MembershipProvider that on user creation links the aspnet_Users table to my custome User table.UserFK guid aspNetId      int userIdI then use my own userId to link into other data which uses their PKs to link to other tables, etc, etc like any good relational DB should.My question is when I delete a user using the ASP .NET interface I get a contraint error because my User table references the aspnet_Userts.UserId. I initially wanted to toss Cascade On Delete on my tables which would take care of my problem. I was shocked to find out Sql Server 2005 doesnt support Cascades..  and its suggested that Triggers be used instead (ill durned if im gonna make triggers for all my tables)My question is, what is the best practice in deleting from tabled linked with PK/FK relationships? There MUST be a good way to handle this.thanks

View 5 Replies View Related

SQL Server 2005 - Can't Get CASCADE DELETE To Work

Aug 13, 2007

I'm using SQL Server 2005 (product version = 9.00.1406.00, product level = RTM, and edition = Developer Edition). I have a db with a number of tables; I created a Foreign Key in one table and added a Foreign Key w/ ON DELETE CASCADE to it -- all using Microsoft SQL Server Management Studio. When I delete the record in the table with the foreign key, the record in the other table does not get deleted. I tried doing this with a simple SQL script in Microsoft SQL Server Management Studio and in a simple .Net / ADO (C#-based) program. Only the record in the table that I'm specifically deleting is deleted.


Here's the table that is referenced by the foreign key (I told Server Management Studio to write out the script):

USE [CHNOPSDb]
GO
/****** Object: Table [dbo].[tblDeviceContainer] Script Date: 08/13/2007 16:47:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblDeviceContainer](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DeviceContainerTypeID] [int] NOT NULL,
CONSTRAINT [PK_tblDeviceContainer] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


Here's the table that has the foreign key to the above table (again, I told Management Studio to write out the script):

USE [CHNOPSDb]
GO
/****** Object: Table [dbo].[tblNode] Script Date: 08/13/2007 16:46:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblNode](
[ID] [int] IDENTITY(1,1) NOT NULL,
[NodeName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NodeTypeID] [int] NOT NULL,
[UnitID] [int] NOT NULL,
[pDeviceContainerID] [int] NOT NULL,
[NodeIndex] [int] NULL,
CONSTRAINT [PK_Node] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [CHNOPSDb]
GO
ALTER TABLE [dbo].[tblNode] WITH CHECK ADD CONSTRAINT [FK_tblNode_tblDeviceContainer] FOREIGN KEY([pDeviceContainerID])
REFERENCES [dbo].[tblDeviceContainer] ([ID])
ON DELETE CASCADE


I then perform a delete using the following:


Use CHNOPSDb;

delete from tblNode where ID = 1;


It deletes the tblNode record but doesn't delete the tblDeviceContainer record that is referenced by tblNode.


Any help?

Thanks,
Bill

View 4 Replies View Related

Cycles Or Multiple Cascade Paths Error

Sep 11, 2006

Hi there.I've been searching for this error specifically but I haven't found anything yet.I have these two tables (USERS and REQUESTS):USERS ( [LOGIN] [varchar] (10) NOT NULL , [NAME] [varchar] (20) NOT NULL)where LOGIN is the primary key.The problem comes when I try to create the "REQUESTS" table.In these requests there's one user who types the request. After one or two days, there's other user who aproves the request. The problem is that I need two foreign keys referencing the table "USERS".CREATE TABLE REQUESTS ([ID] [numeric](5, 0) NOT NULL ,[DATE] [datetime] NOT NULL ,[NOTES] [varchar] (100) NOT NULL ,[TYPED_BY] [varchar] (10) NOT NULL ,[APROVED BY] [varchar] (10) NULL) ON [PRIMARY]GOALTER TABLE REQUESTS ADD CONSTRAINT [PK__REQUESTS__07DE5BCC] PRIMARY KEY ( [ID]) ON [PRIMARY] GOALTER TABLE REQUESTS ADD CONSTRAINT [FK__REQUESTS__TYP__15702E88] FOREIGN KEY ([TYPED_BY]) REFERENCES [USERS] ([LOGIN]) ON UPDATE CASCADE ,CONSTRAINT [FK__REQUESTS__APR__12742E08] FOREIGN KEY ([APROVED_BY]) REFERENCES [USERS] ([LOGIN]) ON UPDATE CASCADEAnd SQL returns:Introducing FOREIGN KEY constraint 'FK__REQUESTS__APR__12742E08' on table 'REQUESTS' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.Could not create constraint. See previous errors.Ok, after that, I tried creating a new table to store aprovals (Table with two fields: "REQUEST_ID" and "APROVED_BY").So, I removed "APROVED_BY" field from "REQUESTS" and its FK constraint.The same error comes up.I don't think this structure goes into "cycles" or "multiple cascades".How can I do this?Thanks in advanceRegardsRoland

View 3 Replies View Related

Seems Unavoidable Multiple Cascade Paths. How To Avoid?

Jul 20, 2005

Hello,There are three tables:OS-GroupOFCompanies (Table1)GoC_GroupOFCompaniesID (PK)OS-Organization (Table 2)Org_OrganizationID (PK)OS-UnitAddress (Table 3)Unit_UnitAddress (PK)Scenario:(1)GoC_GroupOfCompanies has -one to many- relationship with Org_OrganizationID.(2)GoC_GroupOfCompanies has -one to many- relationship with Unit_UnitAddress.(3)Org_OrganizationID has -one to many- relationship with Unit_UnitAddress.Following Error message appeared after trying to save the relationship (3) described above.'OS-Unit-UnitAddress' table saved successfully'OS-Organization' table- Unable to create relationship 'FK_OS-Organization_OS-Unit-UnitAddress'.ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing FOREIGN KEY constraint 'FK_OS-Organization_OS-Unit-UnitAddress' on table 'OS-Organization' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.NB: ON UPDATE is much needed.How to manage the situation?Please guide.ThanksSuryaPrakash****************************************** This message was posted via http://www.sqlmonster.com** Report spam or abuse by clicking the following URL:* http://www.sqlmonster.com/Uwe/Abuse...f935efcabb55ee9*****************************************

View 3 Replies View Related

May Cause Cycles Or Multiple Cascade Paths Error With Only Two Tables

Apr 11, 2008

Hi,

I have two tables called a and b. a has one row called aId where aId is the PK. b has bId, aId_1 and aId_2 where bId is the PK and aId_1 and aId_2 both allow nulls.

If I have a relationship between aId and aId_1, and another relationship between aId and aId_2, where I set the delete rule for both to SET_NULL, then I get the error -

"may cause cycles or multiple cascade paths"

But its completely reasonable that I might wish to do this.

The funny thing is that under Visual Studio, I can create a Data Set with these tables and using the designer, I can set the both foreign key relationships to Delete Rule Set Null and everything works as expected.

So, I'm unsure now if I just need to set the Delete rules using the Dataset designer and not bother with them in the database itself.

Any comments?

Thanks,

Barry.

View 8 Replies View Related

In SqlServer Management Studio Express, Server Type Option Is Greyed Out, Also Publication Option Missing

Apr 27, 2008

Hi everyone In my SqlServer Management Studio Express, on start up it shows the server type option, but greyed.So that value is fixed to database engine. ( I'm trying to work on an SqlServer Compact Edition database through the SSMStudiothat's why I'm trying to get this to change.)Besides, after I connect i go to the Object Explorer, expand the server node, and go to Replication.When i expand replication, i get the "Local Subscription" option, but nothng for Publication.( I want to work on Merge Replication, that's why I desparately need Publication to work)Am i missing something here? I did not install SqlServer separately, I only have what comes bundled with the Visual Studio  2005 Setup.

View 2 Replies View Related

Cascade Deletes In SQL 2005

Jun 14, 2006

I have a logins table, a loginroles table (intermediate), and a roles table
When I delete a login from logins I need to delete the roles for that login from loginroles. I know I have to use cascade deletes, but I cannot find the option in sql 2005.
Any help would be appreciated.
Thank You,Jason

View 3 Replies View Related

Sql Server 2005 Service Local User Option (web Hosts For Commercial Development)

Apr 18, 2008

1Is it possible to choose local user,(instead of local system) in sql setup service login and then use mixed mode
default configuration in report server or choose configure server later. I will be using a third party host using vista, unfortunately I do not have my own server ( I do not have a domain yet)
vista sql 2005 standard vs 2008. ultimate
2 What is the best configuration for a new aspiring asp.net ssrs developer.
3 Do I need to configure the server if I choose local user?
4. what is the best way to configure sql?

By the way what items
should I look forward in choosing a web host,(who is willing to give alot of help to developers) my interests include ssrs 2008 sql 2008 sql 2005
asp.net mvc, crystal reports, wcf infragistics. I am really interested in showing
examples in sql reporting services, crystal reports wcf , web services , infragistics
asp.net mvc intrigues me.......
I would like to get on a track to eventually use sharepoint services
Thanks again, I know I ask I alot of questions........

View 3 Replies View Related

Not Able To Create A DB Diagram In SQL Server 2005. No Menu Option For Creating A DB Diagram??

Apr 23, 2007

I have created a database with several tables. I want to create a database diagram to show the relationshipbetween the tables. Below are the steps from the SQL Server 2005 documentation on how to create a database diagram. The problem is that when I right click on the Database Diagrams folder I am only given two options. They are:"Working with SQL Server diagrams" and "Refresh"There is no menu choice to create a new database diagram. Can anyone tell me what the problem here is?
 
To create a new database diagramIn Object Explorer, right-click the Database Diagrams folder or any diagram in that folder.
Choose New Database Diagram on the shortcut menu.
The Add Table dialog box appears.
Select the required tables in the Tables list and click Add.
The tables are displayed graphically in the new database diagram.

View 1 Replies View Related

Is There On Delete Cascade In Sql Server?????

Aug 4, 1999

Hi everyone,

I would to make a cascade deletion. I dont know how to do it in SQLServer.

Thanks very much.

View 2 Replies View Related

Does SQL Server 7 Support Cascade Delete Referenctial Action?

May 31, 1999

Hi, every guru,

I am new to SQL server 7. Recently I need to design a 400-or-so table database. I need an automatic method to delete some relative data in all those table. Can anybody tell me: Does SQL server 7 support cascade delete referenctial action?

Any clue will be highly appreciated.

Thanks in advance.

Alan

View 2 Replies View Related

Where Is Export Import Option In 2005

Sep 6, 2007

hi all,
i didn't found the export import option in sql server 2005. experts, please guide me where can i found this?

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 12 Replies View Related

SQL 2005 SP1 Not Support Process Update Option?

Jun 16, 2006

as title.



Thanks.,

Kelvin Jor

View 1 Replies View Related

No Compatibilty Level 90 Option After Upgrade From SQL 2000 To 2005

Jul 28, 2006

I did an in place upgrade of my SQL 2000 server to SQL 2005.

Now all the system and user databases are set to compatibilty mode 80 and in the options tab there is no compatibilty mode 90 option.

How can I set the databasbes to compatibility mode 90?



Thanks!

View 1 Replies View Related

Is There Any Option To Set Auto Fit To Cell Size Of A Table In SSRS 2005?

Dec 28, 2007

Is there any option to set auto fit the cell size of a table in SSRS 2005?


Thanks

View 7 Replies View Related

Lock Pages In Memory Option On SQL 2005 STD 64-bit With W2K3 R2 Standard 64-bit - Does It Work?

Nov 9, 2007

Hey Folks -

I'm setting up a new production SQL 2005 64-bit server running on a dedicated Windows 2003 R2 Standard system w/8GB of RAM.

I've enabled the "Lock Pages in Memory" option (http://msdn2.microsoft.com/en-us/library/ms190730.aspx) but when I run dbcc memorystatus all of my AWE counters show 0.

According to http://blogs.msdn.com/sqlprogrammability/:

"On 64 bit machines, execute 'dbcc memorystatus'. If the AWE Allocated memory is 0 then lock pages in memory privilege has not been granted to the service account or it has not taken effect. "

Does the "Lock Pages in Memory" option work on W2K3 R2 Standard? Just searching across the web I find different answers. In some places it clearly says that Lock Pages in Memory only works on W2K3 Enterprise systems. In other places it says that it should work on any Windows 2003 64-bit OS.

Does anyone have any ideas? Assuming we cannot "lock pages in memory" due to the Windows 2003 Standard OS, are there any other memory management recommendations out there?

thanks!

View 2 Replies View Related

Passing Parameter To Another Report Using Jump To URL Option In Reporting Services 2005

Sep 10, 2007

Hi,

I tried to pass parameter from one report to another report. I can send the parameter using following option:
I used jump to url option and write the following expression:


="javascript:void(window.open('http://hpsi-dev/Reports/Pages/Report.aspx?ItemPath=%2fNextGen+Reports%2fMAUA%2fSales+Order+Detail&rs:Command=Render&SalesOrderNumber="+Fields!SalesOrderNumber.Value+"'))"

and it shows me the following in browser url

http://hpsi-dev/Reports/Pages/Report.aspx?ItemPath=/NextGen+Reports/MAUA/Sales+Order+Detail&rs:Command=Render&SalesOrderNumber=SO43667

now the problem is how to get this ordernumber in my report any option ???pls urgent...any javascript function to take this no into my another report

View 37 Replies View Related

Import And Export Wizard: Transferring Multiple Tables From SQL Server 2005 To SQL Server 2000

Jun 15, 2007

Hi!



I just used the SSIS Import and Export Wizard to copy 50+ tables from SS05 to SS2K.



I found that the wizard created a package that I could not figure out how to edit, e.g., to change whether or not it had to CREATE a table, or just use an existing one. (I created some problems by manually editing the receiving table names to be ones that already existed -- but the original names it had did not exist, so it knew it had to create them. What I should have done, and eventually ended up doing, was scroll through my list of tables in the "receiving" box; I just figured editing the name would be faster, not realizing what problems I would create for myself.)



Anyhow, now that I see the complex package that the wizard creates, with a LOOP over the 50+ tables, I would like to know how/where in the package it is storing the information about the tables to copy.



Basically the wizard creates the following Control Flow tab entries (in processing sequence order):

an Execute SQL Task: NonTransactableSql
an Execute SQL Task: START TRANSACTION
a Sequence Container: Transaction Scoping Sequence, which contains
an Execute SQL Task: AllowedToFailPrologueSql
an Execute SQL Task: PrologueSql
a Foreach Loop Container, which contains
a Transfer Task with an icon I did not notice in the Toolbox
an Execute Package Task: Execute Inner Package
an Execute SQL Task: EpilogueSql
an "on success" arrow to
an Execute SQL Task: COMMIT TRANSACTION
an Execute SQL Task: PostTransaction Sql
an "on failure" arrow to
an Execute SQL Task: ROLLBACK TRANSACTION
an Execute SQL Task: CompensatingSql

Where, and how, can I look within this package to see the details about the tables I am transferring? I see that one of the Connection Managers is "TableSchema.XML" -- but it points to a temporary file on my hard drive, that I presume is populated by the package. Where does it get its information?



This is certainly much more complex than the package I would have written, based on my limited knowledge of SSIS. I would have been inclined to create 50+ Data Flow tasks, one for each table.



So now I'm trying to understand why the Wizard created this more-complex package.



Any help will be appreciated, including references to non-Microsoft books/websites/etc.



Thanks in advance.



Dan

View 17 Replies View Related

Multiple Sql Server 2005 Express?

Dec 16, 2006

is it possible to have sql server 2005 express in one computer? 

View 4 Replies View Related

Sql Server 2005 - Having Multiple Editions

Sep 17, 2006

Dear All

I would like to install and run visual studio .Net 2005 and sql server 2005 (may be enterprise or developer edition)on the same system.

What is the best approach that I should take ? should I install sql server 2005 first ?

Since visual studio .Net 2005 installs mobile edition and express edition will this cause a problem ?

Could multiple sql server editions run on the same machine with no problems ?



Thank you,

Daniel

View 1 Replies View Related

SQL 2012 :: Configuring Memory Per Query Option And Index Create Memory Option

Feb 10, 2015

So I started a new job recently and have noticed a few strange configurations. Typically I would never mess with min memory per query option and index create memory option configuration because i just haven't seen any need to. My typical thought is that if it isn't broke... They have been modified on every single server in my environment.

From Books Online:
• This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.
• The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.

View 3 Replies View Related

Running Multiple Version Of SQL Server On SQL 2005 ?

Jul 12, 2007

During customer engagement, I was told that there are Microsoft add-on to be installed on SQL 2005 and the SQL 2005 can be simulated as SQL 2000, SQL 2000 SP4, SQL 7.0 in different SQL instances.



Is this true? Are there URLs about this topic?

Thanks a lot.



Po-Shan.

View 7 Replies View Related

SQL Server 2005 Express Edition - Multiple Users???

Jan 16, 2008

Hi, i just need some help?? just some info?is it TRUE that SQL Server 2005 Express Edition (free) is capable ONLY of SINGLE user?i mean if i have a data driven page, if someone is using or editing the data in my page, does no one can access my data base simultaneously??i hope you could enlighten my mind, thanks 

View 2 Replies View Related

Multiple Versions Of SQL Server 2005 - What Order To Install?

Jun 15, 2007

I currently have three SQL Server instances installed on my notebook. One is v8, which I believe is used by an accounting application. The other two are as follows:-

SQL Server MSSMLBIZ

SQL Server SQLEXPRESS



The second one (SQLEXPRESS) fails to start generating the following error:-

The SQL Server (SQLEXPRESS) service terminated with service-specific error 17058 (0x42A2).



Other services listed in SQL Server Configuration Manager are:-

SQL Server Integration Services - which runs apparently normally.

SQL Server Analysis Services (MSSQLSERVER)

SQL Server Reporting Services (MSSQLSERVER)

SQL Server Browser



The last thre of these are run under LocalSystem.



I have been trying to install the SQLServer SP2 to the SQLEXPRESS instance for some number of days now, but each time it runs, it fails to install.



I am beginning to wonder if there is a sequence of installation for each of the above instances that should be followed. Can anyone give me any suggestions, or answers to the above problems?



Thanks,

View 3 Replies View Related

Inserting To Multiple Tables In SQL Server 2005 That Use Identity Specification

Feb 20, 2007

Hi, I am having a bit of hassle with trying to enter details to multiple tables in SQL Server 2005.
I have four tables, an
Attendance Table (AttendanceID(PK Identity specific), MembershipNo, Date)
Resistance Table (ResistId(PK Identity specific), Weight , Reps, Sets)
Aerobics Tables(AerobicsID(PK Identity specific), MachineID, Intensity, Time)
and a linking table for all of them.... ExerciseMaster(AttendanceID,ResistanceID,AerobicsI D)

My problem is that I can insert data to each specific table by itself using seperate insert statements.....eg....

//insert an attendance record to the attendance table
string userID;

userID = Session["User"].ToString();

SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();

pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Attendance] ([MembershipNo], [Date]) VALUES (@MembershipNo, @Date)";
pgpDataSource.InsertParameters.Add("MembershipNo", userID);
pgpDataSource.InsertParameters.Add("Date", txtVisitDate.Text);

int RowsAffected = 0;

try
{
RowsAffected = pgpDataSource.Insert();
}

catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}

finally
{
pgpDataSource = null;
}


//insert an aerobics record into the aerocibs table

SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();

pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Aerobics] ([MachineID], [Intensity], [ExerciseTime]) VALUES (@MachineID, @Intensity, @ExerciseTime)";


pgpDataSource.InsertParameters.Add("MachineID", rower.ToString());
pgpDataSource.InsertParameters.Add("Intensity", txtRowerLevel.Text);
pgpDataSource.InsertParameters.Add("ExerciseTime", txtRowerTime.Text);

int RowsAffected = 0;

try
{
RowsAffected = pgpDataSource.Insert();
}

catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}

finally
{
pgpDataSource = null;
}
//same code as above for the resistance table

However, i am facing the problem where this does not populate the link table(ExerciseMaster) with any information as i am unable to write the relevant IDs into the table that have been auto generated by SQL Server for each of the subTables.
I have read several forums where they recommend using something called @@IDENTITY but i have no idea how or where to use this in order to fill my exercise table...
Any help would be so much appreciated.... Also, hopefully what i have said all makes sense and someone will be able to help me...oh and one more thing...this is an ASP.NET page coding in C#
Cheers
Scotty

View 8 Replies View Related

Got An Error When Trying To Import Multiple Tables From One SQL Server 2005 Database To Another

Oct 1, 2007



We just upgraded from SQL Server 2000 to 2005. In the past, when I ran the import/export wizard to copy multiple tables from one database to another with SQL Server 2000, I had no problem. Now when I used the import/export wizard to copy multiple tables with SQL Server 2005, I kept getting an error. For example, when copied three tables, the first table might be copied fine and I got an error with the second table and the whole thing stop. Sometimes I could copy two tables. However, when I ran the import/export wizard to copy each table one at a time, it worked.

The error that I got was "Cannot insert duplicate key in object..." I selected the options to "Delete rows in existing destination tables", and "Enable identity insert". What am I doing wrong?

R. Jiwungkul

View 15 Replies View Related

Installing / Running Multiple Instances Of SQL Server Express 2005

Jan 3, 2008

My client has a server running MS 2003 Server for Smal Business Server, and it has SQL Server Express 2005 installed (I believe it is installed 3 times, as part of SharePoint, Veritas Backup Exec and ACT! applications).

I am trying to implement an application that uses SQL Server 2000, but the Server 2003 SBS does not support that version. So I need to launch an additional instance of SQL Server Exp 2005.

To do so, do I need to download and install SQL Server Express 2005 software again? Or can I just run SQLSVR.exe from an existing instance, but with a different instance name (sqlservr.exe -s<InstanceName>?

The reason I need another instance is that these existing instances limit DDL statements.

I am new to this and compare it to launching MS Word multiple times - you don't need to reinstall Word to run it multiple times.

Is launching from the command line a bad idea - and if it is, what is the better way to ensure that it launches under the local system context a service?

Thanks in advance for any help and insights. It is much appreciated.

View 9 Replies View Related

SQL Server 2005 SELECT MAX Function For Multiple Columns On The Same Record

Aug 25, 2006

Hello,

I am trying to figure out how to use the select maximum command in SQL Server 2005. I have already created a database and I have it populate it with multiple fields and multiple records. I Would like to create a new column or field which contains the maximum value from four of the fields. I have already created a column and I am trying to figure out how to use a command or SQL statement which is entered into the computed equation or formula in the properties for this field/column.

Any help you can provide will be greatly appreciated!

Thank you,
Nathan

View 17 Replies View Related

SQL Server 2005 - How To Create Multiple Password Policies For SQL Authenticated IDs?

Sep 12, 2006

We have a need to have separate password policies for different groups of logins. For example: Those IDs that have greater privileges should be rquired to have more complex passwords that expire more frequently than IDs with lesser privileges.

It appears to me that SQL Server pulls the password policy from the default Active Directory domain group. Is there a way to create/utilize multiple policies for SQL Server authenticated IDs?

Thanks for your assistance.

View 4 Replies View Related

SQL Server 2005 Connection Crashes With Multiple User Clicking Same Link.

Mar 16, 2008

Hi,
I just developed a ASP.NET website with SQL Server 2005 as database. I am having connection crash problem when multiple user click on same link to fetch data from the database. If users click on different links or there is few seconds of time gap between the data access, then it works fine. But the connections crash problem only occurs when 2 or more users click same link at same time.
I am using the following kind of Datalayers to access the data from database:
public static ContentInformation GetContentForUpdate(int ContentId)
{
ContentInformation result = new ContentInformation(); ;
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.StoredProcedure;
command.Connection = Connection;
command.CommandText = "Content_GetContentForUpdate1";
SqlParameter parameter = new SqlParameter("@ContentId", SqlDbType.Int);
parameter.Direction = ParameterDirection.Input;
parameter.Value = ContentId;
command.Parameters.Add(parameter);
try
{
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow);

if (reader.Read())
{
// read the data
}
reader.Close();
}
catch (SqlException ex)
{
Trace.TraceError(ex.Message);
throw ex;
}
finally
{
command.Connection.Close();
}
return result;
}
I would be really grateful if someone can help me out with this problem. Waiting for the soonest reply. Thanks.

View 7 Replies View Related

Microsoft SQL Server 2005. Selecting Multple Columns From Multiple Tables

Mar 23, 2008

Im just curious how i would take multiple columns from multiple tables.... would it be something like this ???
table: Products COLUMNS ProductName, ProductID
table: Categorys COLUMNS CategoryName, CategoryID,ProductID
SELECT Products.ProductName, Categorys.CategoryName,Products.ProductID,Categorys.CategoryID,Categorys.ProductID
FROM Categorys, Tables
WHERE Products.ProductID = Categorys.ProductID
 

View 1 Replies View Related

64 Bit SQL Server 2005 Process Is Only Utilizing One Processor Whereas In 32 Bit Environ. Uses Multiple Processors

Oct 9, 2007



Hello all,

We've had a problem for a few months now that has completely stumped us. We are running a heavily cursored massive data manipulation process on a 32 bit SQL Server instance running on a virtual machine, running ontop of VMWare, with the following specs

Processors: 2x2674MHz processors
Memory: 4GB
RAID 10 disk config

When we run our process on this machine, in total it runs in 30 hours.

When this process is run on another 32 bit server with the following specs

Processors: 8x3658MHx processors
Memory: 8 GB
SAN w/ RAID 5 disk config

It runs 25% slower

But here is the real kicker. When this process is run on a 64 bit server with the following specs

Processors: 8x3658MHz processors
Memory: 8 GB
SAN w/ RAID 5 disk config

It runs 75% slower.

This process consists solely of stored procedures written in TSQL. The weird thing is that on our smaller server, the CPUs' % utilization are evenly balanced (at 20-30%) when this large data manipulation process is running. However on the bigger servers, SQL Server latches onto a single processor and doesn't load balance across other processors. Such that what we're seeing is that only one processor out of the eight will be utilized and it will be throttled at 90% while the other 7 are at zero.

The default configuration settings in all three places.

Has anyone ever seen any behavior like this, where only one processor gets used by SQL Server during processing? Granted our processes are single threaded b/c they are using cursors but, it seems that the single thread shouldn't be restricted to one processor.

Any thoughts?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved