By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,686 Members | 2,770 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,686 IT Pros & Developers. It's quick & easy.

Non updateable view access 2000

P: n/a
I am using sql 2000 (mdb) with linked tables on SQL 2000. I need to
use views to limit users records, instead of direct table access. I
setup a single talbe view in sql and tested update and inserting the
view using query analyzer. But when I add that view as a "linked"
table in access (windows db security dsn-less). Access does not allow
me to edit the data from view. Table has Pk have tried indexing view
but we use several ntext fields which does not work for clustered
index

Have tried creating view "with schemabinding" and "with viewmetadata"

Can not get this to work. Is it the lack of index on view?

REATE VIEW dbo.vuCIRSFILTER
AS
SELECT SoldTo, CIRSID, FLNumber, CustomerIDNumber, CustomerName,
CustomerAddress, City, State, ZIP, ContactName, ContactPhone,
ContactCell,
OnSiteContact, ContactEmail, OnSitePhone,
OnSiteCellPage, OnSiteEmail, SalesRep, Division, PM,
InstallUpgradeDate, Notes, RFASID, RFASIDVers,
RFATrans, PBXSystemType, PBXVersion,
PBXDialUpModem, PBXPassword, PBXLoginID, VoiceMailSystemType,
VoiceMailVersion,
VoiceMailDialUpModem, VoiceMailLoginID,
VoicemailPassword, CMSSystemType, CMSVersion, CMSDialUpModem,
CMSLoginID, CMSPassword,
MAPD, MAPDDialup, MAPDLogin, MAPDPassword,
CTIInformation, CSUDSUSystemType, CSUDSUVersion, CSUDSUDialUpModem,
CSUDSULoginID,
CSUDSUPassword, AdditionalEquipment,
PWMContract, SupportNotes, OE, CREATED_BY_USER, CREATED_ON_DATE,
MODIFIED_ON_DATE,
MODIFIED_BY_USER
FROM dbo.tblCIRSMain
WHERE (CREATED_BY_USER = 'company\auser')

Thanks
CREATE TABLE [dbo].[tblCIRSMain] (
[SoldTo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CIRSID] [int] IDENTITY (1, 1) NOT NULL ,
[FLNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CustomerIDNumber] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CustomerAddress] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIP] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ContactPhone] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ContactCell] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ContactEmail] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSiteContact] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSitePhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSiteCellPage] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSiteEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[SalesRep] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Division] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[PM] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InstallUpgradeDate] [datetime] NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RFASID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RFASIDVers] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[RFATrans] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[PBXSystemType] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXVersion] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXDialUpModem] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXLoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[VoiceMailSystemType] [nvarchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoiceMailVersion] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoiceMailDialUpModem] [nvarchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoiceMailLoginID] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoicemailPassword] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CMSSystemType] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSVersion] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSDialUpModem] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSLoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MAPD] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MAPDDialup] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MAPDLogin] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MAPDPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CTIInformation] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CSUDSUSystemType] [nvarchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CSUDSUVersion] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CSUDSUDialUpModem] [nvarchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CSUDSULoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CSUDSUPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[AdditionalEquipment] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PWMContract] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[SupportNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[upsize_ts] [timestamp] NULL ,
[CREATED_BY_USER] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CREATED_ON_DATE] [datetime] NOT NULL ,
[MODIFIED_ON_DATE] [datetime] NULL ,
[MODIFIED_BY_USER] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [CIRS_SOLDTO] ON
[dbo].[tblCIRSMain]([SoldTo]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblCIRSMain] ADD
CONSTRAINT [DF_tblCIRSMain_(suser_sname())] DEFAULT (suser_sname())
FOR [CREATED_BY_USER],
CONSTRAINT [DF_tblCIRSMain_CIRSRowCreateDate] DEFAULT (getdate()) FOR
[CREATED_ON_DATE],
CONSTRAINT [DF_tblCIRSMain_CIRSRowModUser] DEFAULT ('') FOR
[MODIFIED_BY_USER],
CONSTRAINT [aaaaatblCIRSMain_PK] PRIMARY KEY NONCLUSTERED
(
[SoldTo]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [CMSLoginID] ON [dbo].[tblCIRSMain]([CMSLoginID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [CSU/DSULoginID] ON
[dbo].[tblCIRSMain]([CSUDSULoginID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

CREATE INDEX [PBXLoginID] ON [dbo].[tblCIRSMain]([PBXLoginID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [RFASID] ON [dbo].[tblCIRSMain]([RFASID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [VoiceMailLoginID] ON
[dbo].[tblCIRSMain]([VoiceMailLoginID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

CREATE INDEX [ix_ILNumber] ON [dbo].[tblCIRSMain]([FLNumber]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
With an MDB, Access must be told which field or fields in the link comprise
the primary key. This can be done when you create the link through the menu,
and Access asks you to select the key fields, or on an existing link, by
executing an Access DDL query to "create" the "primary key" on the link. If
this has been done successfully, you should see the key symbol next to the
field or fields when you open the link in design view.

On 1 Sep 2004 11:56:22 -0700, ab*************@yahoo.com (abefuzzleduser2)
wrote:
I am using sql 2000 (mdb) with linked tables on SQL 2000. I need to
use views to limit users records, instead of direct table access. I
setup a single talbe view in sql and tested update and inserting the
view using query analyzer. But when I add that view as a "linked"
table in access (windows db security dsn-less). Access does not allow
me to edit the data from view. Table has Pk have tried indexing view
but we use several ntext fields which does not work for clustered
index

Have tried creating view "with schemabinding" and "with viewmetadata"

Can not get this to work. Is it the lack of index on view?

REATE VIEW dbo.vuCIRSFILTER
AS
SELECT SoldTo, CIRSID, FLNumber, CustomerIDNumber, CustomerName,
CustomerAddress, City, State, ZIP, ContactName, ContactPhone,
ContactCell,
OnSiteContact, ContactEmail, OnSitePhone,
OnSiteCellPage, OnSiteEmail, SalesRep, Division, PM,
InstallUpgradeDate, Notes, RFASID, RFASIDVers,
RFATrans, PBXSystemType, PBXVersion,
PBXDialUpModem, PBXPassword, PBXLoginID, VoiceMailSystemType,
VoiceMailVersion,
VoiceMailDialUpModem, VoiceMailLoginID,
VoicemailPassword, CMSSystemType, CMSVersion, CMSDialUpModem,
CMSLoginID, CMSPassword,
MAPD, MAPDDialup, MAPDLogin, MAPDPassword,
CTIInformation, CSUDSUSystemType, CSUDSUVersion, CSUDSUDialUpModem,
CSUDSULoginID,
CSUDSUPassword, AdditionalEquipment,
PWMContract, SupportNotes, OE, CREATED_BY_USER, CREATED_ON_DATE,
MODIFIED_ON_DATE,
MODIFIED_BY_USER
FROM dbo.tblCIRSMain
WHERE (CREATED_BY_USER = 'company\auser')

Thanks
CREATE TABLE [dbo].[tblCIRSMain] (
[SoldTo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CIRSID] [int] IDENTITY (1, 1) NOT NULL ,
[FLNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CustomerIDNumber] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CustomerAddress] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIP] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ContactPhone] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ContactCell] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ContactEmail] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSiteContact] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSitePhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSiteCellPage] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSiteEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[SalesRep] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Division] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[PM] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InstallUpgradeDate] [datetime] NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RFASID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RFASIDVers] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[RFATrans] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[PBXSystemType] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXVersion] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXDialUpModem] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXLoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[VoiceMailSystemType] [nvarchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoiceMailVersion] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoiceMailDialUpModem] [nvarchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoiceMailLoginID] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoicemailPassword] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CMSSystemType] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSVersion] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSDialUpModem] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSLoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MAPD] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MAPDDialup] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MAPDLogin] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MAPDPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CTIInformation] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CSUDSUSystemType] [nvarchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CSUDSUVersion] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CSUDSUDialUpModem] [nvarchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CSUDSULoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CSUDSUPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[AdditionalEquipment] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PWMContract] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[SupportNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[upsize_ts] [timestamp] NULL ,
[CREATED_BY_USER] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CREATED_ON_DATE] [datetime] NOT NULL ,
[MODIFIED_ON_DATE] [datetime] NULL ,
[MODIFIED_BY_USER] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [CIRS_SOLDTO] ON
[dbo].[tblCIRSMain]([SoldTo]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblCIRSMain] ADD
CONSTRAINT [DF_tblCIRSMain_(suser_sname())] DEFAULT (suser_sname())
FOR [CREATED_BY_USER],
CONSTRAINT [DF_tblCIRSMain_CIRSRowCreateDate] DEFAULT (getdate()) FOR
[CREATED_ON_DATE],
CONSTRAINT [DF_tblCIRSMain_CIRSRowModUser] DEFAULT ('') FOR
[MODIFIED_BY_USER],
CONSTRAINT [aaaaatblCIRSMain_PK] PRIMARY KEY NONCLUSTERED
(
[SoldTo]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [CMSLoginID] ON [dbo].[tblCIRSMain]([CMSLoginID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [CSU/DSULoginID] ON
[dbo].[tblCIRSMain]([CSUDSULoginID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

CREATE INDEX [PBXLoginID] ON [dbo].[tblCIRSMain]([PBXLoginID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [RFASID] ON [dbo].[tblCIRSMain]([RFASID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [VoiceMailLoginID] ON
[dbo].[tblCIRSMain]([VoiceMailLoginID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

CREATE INDEX [ix_ILNumber] ON [dbo].[tblCIRSMain]([FLNumber]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO


Nov 13 '05 #2

P: n/a
Steve,

Checking view -> design view shows no Key displayed.

Open a new query, and try running this Access DDL ok

CREATE INDEX Pkview_rs_tblCIRSMAIN
ON view_rs_tblCIRSMAIN (SOLDTO)
WITH PRIMARY;

I was then able to update the view!

I am going to work on multi table views.

Thanks Steve!

Steve Jorgensen <no****@nospam.nospam> wrote in message news:<pj********************************@4ax.com>. ..
With an MDB, Access must be told which field or fields in the link comprise
the primary key. This can be done when you create the link through the menu,
and Access asks you to select the key fields, or on an existing link, by
executing an Access DDL query to "create" the "primary key" on the link. If
this has been done successfully, you should see the key symbol next to the
field or fields when you open the link in design view.

On 1 Sep 2004 11:56:22 -0700, ab*************@yahoo.com (abefuzzleduser2)
wrote:
I am using sql 2000 (mdb) with linked tables on SQL 2000. I need to
use views to limit users records, instead of direct table access. I
setup a single talbe view in sql and tested update and inserting the
view using query analyzer. But when I add that view as a "linked"
table in access (windows db security dsn-less). Access does not allow
me to edit the data from view. Table has Pk have tried indexing view
but we use several ntext fields which does not work for clustered
index

Have tried creating view "with schemabinding" and "with viewmetadata"

Can not get this to work. Is it the lack of index on view?

REATE VIEW dbo.vuCIRSFILTER
AS
SELECT SoldTo, CIRSID, FLNumber, CustomerIDNumber, CustomerName,
CustomerAddress, City, State, ZIP, ContactName, ContactPhone,
ContactCell,
OnSiteContact, ContactEmail, OnSitePhone,
OnSiteCellPage, OnSiteEmail, SalesRep, Division, PM,
InstallUpgradeDate, Notes, RFASID, RFASIDVers,
RFATrans, PBXSystemType, PBXVersion,
PBXDialUpModem, PBXPassword, PBXLoginID, VoiceMailSystemType,
VoiceMailVersion,
VoiceMailDialUpModem, VoiceMailLoginID,
VoicemailPassword, CMSSystemType, CMSVersion, CMSDialUpModem,
CMSLoginID, CMSPassword,
MAPD, MAPDDialup, MAPDLogin, MAPDPassword,
CTIInformation, CSUDSUSystemType, CSUDSUVersion, CSUDSUDialUpModem,
CSUDSULoginID,
CSUDSUPassword, AdditionalEquipment,
PWMContract, SupportNotes, OE, CREATED_BY_USER, CREATED_ON_DATE,
MODIFIED_ON_DATE,
MODIFIED_BY_USER
FROM dbo.tblCIRSMain
WHERE (CREATED_BY_USER = 'company\auser')

Thanks
CREATE TABLE [dbo].[tblCIRSMain] (
[SoldTo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CIRSID] [int] IDENTITY (1, 1) NOT NULL ,
[FLNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CustomerIDNumber] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CustomerAddress] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIP] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ContactPhone] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ContactCell] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ContactEmail] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSiteContact] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSitePhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSiteCellPage] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSiteEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[SalesRep] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Division] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[PM] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InstallUpgradeDate] [datetime] NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RFASID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RFASIDVers] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[RFATrans] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[PBXSystemType] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXVersion] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXDialUpModem] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXLoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[VoiceMailSystemType] [nvarchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoiceMailVersion] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoiceMailDialUpModem] [nvarchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoiceMailLoginID] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoicemailPassword] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CMSSystemType] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSVersion] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSDialUpModem] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSLoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MAPD] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MAPDDialup] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MAPDLogin] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MAPDPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CTIInformation] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CSUDSUSystemType] [nvarchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CSUDSUVersion] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CSUDSUDialUpModem] [nvarchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CSUDSULoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CSUDSUPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[AdditionalEquipment] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PWMContract] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[SupportNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[upsize_ts] [timestamp] NULL ,
[CREATED_BY_USER] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CREATED_ON_DATE] [datetime] NOT NULL ,
[MODIFIED_ON_DATE] [datetime] NULL ,
[MODIFIED_BY_USER] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [CIRS_SOLDTO] ON
[dbo].[tblCIRSMain]([SoldTo]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblCIRSMain] ADD
CONSTRAINT [DF_tblCIRSMain_(suser_sname())] DEFAULT (suser_sname())
FOR [CREATED_BY_USER],
CONSTRAINT [DF_tblCIRSMain_CIRSRowCreateDate] DEFAULT (getdate()) FOR
[CREATED_ON_DATE],
CONSTRAINT [DF_tblCIRSMain_CIRSRowModUser] DEFAULT ('') FOR
[MODIFIED_BY_USER],
CONSTRAINT [aaaaatblCIRSMain_PK] PRIMARY KEY NONCLUSTERED
(
[SoldTo]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [CMSLoginID] ON [dbo].[tblCIRSMain]([CMSLoginID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [CSU/DSULoginID] ON
[dbo].[tblCIRSMain]([CSUDSULoginID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

CREATE INDEX [PBXLoginID] ON [dbo].[tblCIRSMain]([PBXLoginID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [RFASID] ON [dbo].[tblCIRSMain]([RFASID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [VoiceMailLoginID] ON
[dbo].[tblCIRSMain]([VoiceMailLoginID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

CREATE INDEX [ix_ILNumber] ON [dbo].[tblCIRSMain]([FLNumber]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.