Advice on Database structure

I am thinking of doing a fake PC company site for my ASP project. sowhat they will have is a chat, products with reviews, and users canhave "Buddies".

So my DB so far (Tables):

ProductPC - ID, Processor, RAM, HDD, Graphics, LCD ...ProductHardware- ID, Title (Like "Intel C2D E6600"), Description, Price, Rating (0-5stars, so integer), Category (CPU, HDD, Graphics)ProductSoftware - ID, Title (eg. "Adobe CS3"), Description, Price, Rating, Category (eg. Design, Programming)Reviews - ID, ProductID, Title, Content, DateChat - ID, TopicID, Title, Content, DateUsers - ID, Username, Password,
The problem, how do i connect Reviews to the products since they are from diff tables.
How do i get the "buddy" system working
Chat i think its not as simple as that

But i just need a simple ASP project, so no need to get too complex, but i still hope to learn as much.

Concerning

ProductPC - ID, Processor, RAM, HDD, Graphics, LCD ...ProductHardware - ID, Title (Like "Intel C2D E6600"), Description, Price, Rating (0-5 stars, so integer), Category (CPU, HDD, Graphics)ProductSoftware - ID, Title (eg. "Adobe CS3"), Description, Price, Rating, Category (eg. Design, Programming)Reviews - ID, ProductID, Title, Content, Date
you have a problem since Review.ProductId can refere to 1 of 3 tables. To overcome this, I suggest you create a ProductTable thus:
Product - Id, Cost, Title, IsPC, IsHardware, IsSoftware
Change the Id column on ProductPc, ProductHardware and ProductSoftware tables to be non-identity. When a Product entry is created, one and only of the Is must be true. The appropriate record of ProductPc, ProductHardware or ProductSoftware can be created using the the New Product.Id and the Id value. This will allow your review table to work.|||

sorry... but what do u mean? taking off my IDs? then when i add to DB how should i add?

|||ProductPC - ID, Processor, RAM, HDD, Graphics, LCD ...ProductHardware - ID, Title (Like "Intel C2D E6600"), Description, Price, Rating (0-5 stars, so integer), Category (CPU, HDD, Graphics)ProductSoftware - ID, Title (eg. "Adobe CS3"), Description, Price, Rating, Category (eg. Design, Programming)Reviews - ID, ProductID, Title, Content, Date|||

Thanks. i am getting you. but the problem id how do i acheive the part of having the ID to be unique, do i need to code that? because now i have set those IDs to identity and auto increment. if they are not identity, then how do they be unique (automatically)? and to have product ID to be unique across 3 tables?

|||

The sequence for creating a new record is:

Create the new product reference

|||

mmm... Scope_Identity() is something unfamiliar to me right now i will search abt it later. for now i am going off to school.

|||

is it ok if i have 1 Reviews Table with the ProductID linking to 3 Tables? PC, Hardware & Software Table

So,

PK : Reviews.ProdID - FK: ProdPC.PCID
PK : Reviews.ProdID - FK: ProdHW.HWID
PK : Reviews.ProdID - FK: ProdSW.SWID

|||

No the link is:

PK : Reviews.ProdID - FK: Product.Id
PK : Reviews.ProdID - FK: Product.Id
PK : Reviews.ProdID - FK: Product.Id

This is because
PK: ProdPC.PCID - FK Product.Id
PK: ProdHW.HWI - FK Product.Id
PK: ProdSW.SWID- FK Product.Id

|||

i think u say that because u think i used Scope_Identity()? for now i dunno how to use that. so what i done is 1 Reviews Table linking to 3 Products Table (1 for PC, HW & SW) so Reviews.ProdID to ProdXX.ID. is that also ok?

|||

Here is a SQL2005 script with some of the tables and a stored procedure for inserting a record showing how to use Scope_Identity()
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductSoftware]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ProductSoftware](
[Id] [int] NOT NULL,
[Category] [nvarchar](50) NOT NULL,
[Description] [nvarchar](250) NOT NULL,
[Rating] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_ProductSoftware] PRIMARY KEY CLUSTERED
([Id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Non-identity primary key from Product.Id' ,@.level0type=N'SCHEMA', @.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'ProductSoftware', @.level2type=N'COLUMN', @.level2name=N'Id'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Product](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](250) NOT NULL,
[Price] [money] NOT NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
([Id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'a.k.a. Title' ,@.level0type=N'SCHEMA', @.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'Product', @.level2type=N'COLUMN', @.level2name=N'Name'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductHardware]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ProductHardware](
[Id] [int] NOT NULL,
[Description] [varchar](max) NOT NULL,
CONSTRAINT [PK_ProductHardware] PRIMARY KEY CLUSTERED
([Id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Non-identity product key from Product.Id' ,@.level0type=N'SCHEMA', @.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'ProductHardware', @.level2type=N'COLUMN', @.level2name=N'Id'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductPc]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ProductPc](
[Id] [int] NOT NULL,
[Graphics] [nvarchar](50) NOT NULL,
[HDD] [nvarchar](50) NOT NULL,
[LCD] [nvarchar](50) NOT NULL,
[Processor] [nvarchar](50) NOT NULL,
[RAM] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_ProductPc] PRIMARY KEY CLUSTERED
([Id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Non-identity primary key from Product.Id' ,@.level0type=N'SCHEMA', @.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'ProductPc', @.level2type=N'COLUMN', @.level2name=N'Id'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Review]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Review](
[Id] [int] NOT NULL,
[ProductId] [int] NOT NULL,
[ContentText] [nvarchar](max) NULL,
[Title] [nvarchar](50) NOT NULL,
[DateAdded] [datetime] NOT NULL CONSTRAINT [DF_Review_DateAdded] DEFAULT (getdate()),
CONSTRAINT [PK_Review] PRIMARY KEY CLUSTERED
([Id] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ProductHardware_Insert]')
AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'CREATE PROCEDURE [dbo].[usp_ProductHardware_Insert]
(
@.Description varchar (MAX),
@.Name nvarchar (250),
@.Price money,
@.RETURN INT OUTPUT,
@.IDENTITY INT OUTPUT
) AS
-- Purpose:
-- Insert record into Product and ProductHardware table
-- History:
-- 11Jun2007 CVC Original coding
SET NOCOUNT ON
INSERT INTO Product(Name, Price) VALUES (@.Name, @.Price)
SELECT @.IDENTITY = SCOPE_IDENTITY()
INSERT INTO ProductHardware(Id, Description)
VALUES ( @.IDENTITY, @.Description)
SELECT @.RETURN = @.@.error
RETURN
----- this is the end ------
'
END|||

i think i will need more knowledge about Stored Procedures and Scope Identity. any tutorials about that?

|||

There are some good stored procedure tutorials at

http://www.codeproject.com/database/hkstoredproc.asp (Everything you wanted to know about Stored Procedures)

Advice Needed: Where to put ADO Code

I need some advice on a project that I am working on...

First, here is what I am trying to achieve: A Web Form with two controls: A DropDownList with two items added at design time (Fruits and Vegetables) and an empty ListBox. When the user chooses a "category" from the DropDownList, the ListBox will be populated with a list of either "Fruits" or "Vegetables" retrieved from a SQL database. (Note: Since the data in the SQL database must be converted and formatted programatically, simply databinding the ListBox will not work here.)

I believe that I can do this with the following code (stolen from an MSDN article):

'Create ADO.NET objects.Private myConnAs SqlConnectionPrivate myCmdAs SqlCommandPrivate myReaderAs SqlDataReaderPrivate resultsAs String'Create a Connection object. myConn =New SqlConnection("Initial Catalog=Northwind;" & _"Data Source=localhost;Integrated Security=SSPI;")'Create a Command object. myCmd = myConn.CreateCommand myCmd.CommandText ="SELECT FirstName, LastName FROM Employees"'Open the connection. myConn.Open() myReader = myCmd.ExecuteReader()'Concatenate the query result into a string.Do While myReader.Read() results = results & myReader.GetString(0) & vbTab & _ myReader.GetString(1) & vbLfLoop'Display results. MsgBox(results)'Close the reader and the database connection. myReader.Close() myConn.Close()

Now here is the part that I am not sure about: Is the FormLoad event the best place to put this code? If I do, is this not a lot of overhead (creating, opening and closing a connection) everytime there is a page refresh/PostBack? Would I be better off putting this code in the DropDownList SelectedIndexChanged event? Although that seems like it could make the process of selecting a category take a fairly long time.

Finally, if the is a better way of doing this, I am certainly open to suggestions.

All advice is greatly appreciated.

hello,

you may like to read "ASP.Net Tutorial - If Not Page.IsPostBack" article at,

http://aspnet101.com/aspnet101/tutorials.aspx?id=3

ALSO,

i'd like to suggest you to read this too,

1) "Examining the Data Access Application Block" at,

http://aspnet.4guysfromrolla.com/articles/070203-1.aspx

2) "Working with the Enterprise Library's Data Access Application Block" at ,

http://aspnet.4guysfromrolla.com/articles/030905-1.aspx

regards,

Niraj sikotara.

Advice Needed on Large Scale DataBases

We are in the process of developing a web indexing project similar to
http://www.grub.org/ . We will have a database that is going to contain
about 5 billion records. Currently I am split between MS SQL and My SQL for
this project. Some of the issues we are going to have is storage size. Based
on our math, our data base will be about 100 GB in size, is there a file
size limit with MS SQL (I heard some where there is a 2GB limit or was that
2 TB limit). A better design is a split data base but for now we would like
to use a single db.
Jay
Good luck with MySQL and 5 billion rows<g>. SQL Server does not have a
limit that you suggested except in it's free version (MSDE). THere are many
SQL Servers out there with 2TB or more of data. Even though 100GB is not
that large these days for SQL Server I can't stress enough that you do your
homework first and ensure you have a properly designed db schema and
application.
Andrew J. Kelly SQL MVP
"Jay Janarthanan" <jay@.objectcube.com> wrote in message
news:e8N7AKl2EHA.1452@.TK2MSFTNGP11.phx.gbl...
> We are in the process of developing a web indexing project similar to
> http://www.grub.org/ . We will have a database that is going to contain
> about 5 billion records. Currently I am split between MS SQL and My SQL
> for this project. Some of the issues we are going to have is storage size.
> Based on our math, our data base will be about 100 GB in size, is there a
> file size limit with MS SQL (I heard some where there is a 2GB limit or
> was that 2 TB limit). A better design is a split data base but for now we
> would like to use a single db.
> Jay
>
|||Thanks Andrew. There is only one Table that is going to contain the 5B rows,
the rest of the tables are small in size.
I am more concerned about the HW requirements.
Jay
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OwmvZXl2EHA.924@.TK2MSFTNGP14.phx.gbl...
> Good luck with MySQL and 5 billion rows<g>. SQL Server does not have a
> limit that you suggested except in it's free version (MSDE). THere are
> many SQL Servers out there with 2TB or more of data. Even though 100GB is
> not that large these days for SQL Server I can't stress enough that you do
> your homework first and ensure you have a properly designed db schema and
> application.
> --
> Andrew J. Kelly SQL MVP
>
> "Jay Janarthanan" <jay@.objectcube.com> wrote in message
> news:e8N7AKl2EHA.1452@.TK2MSFTNGP11.phx.gbl...
>
|||Jay Janarthanan wrote:[vbcol=seagreen]
> Thanks Andrew. There is only one Table that is going to contain the
> 5B rows, the rest of the tables are small in size.
> I am more concerned about the HW requirements.
> Jay
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OwmvZXl2EHA.924@.TK2MSFTNGP14.phx.gbl...
Hardware is easy. Lots of CPU, lots of fast drives, and lots of
memory...
If you put that large table on its own RAID 10 array if it gets writtent
to a lot, or a fast RAID 5 array if it is mostly read-only (batches at
night and small updates during the day are fine), you shold be ok. Use a
lot of drives in the array for best performance and put that array on
its own channel.
Regarding the hardware, you'll need to do some homework about the size
of the table and how frequently it gets accessed to determine if you'll
need the improved memory allowance of SQL Server 2000 EE. If you mostly
accessing single rows and have the necessary indexing in place, you may
find that memory is not a big concern and a fast 2-cpu system may
suffice. 100BG is not that large for SQL Server as Andrew mentioned. But
given that large table table, you probably want to make sure it's
isolated from the other data on its own drive subsystem. Drive are
cheap, though. You could throw 10 fast drives in a RAID 5 array for a
couple thousand and then use that array for backing up if the database
was idle at night. RAID 10 (1+0) is a little more expensive in terms of
drives, but has the best write and read performance. RAID 5 is the worst
for writing, but fast at reading, and the cheapest redundant solution.
David Gugick
Imceda Software
www.imceda.com
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OwmvZXl2EHA.924@.TK2MSFTNGP14.phx.gbl...
> Good luck with MySQL and 5 billion rows<g>.
Well, to fair, the largest MySQL db I've heard of has 10 billion rows...

>
|||You will want to isolate that table and its non-clustered indexes across as
many physical disk spindles as possible. Also, consider creating two
seperate File Groups, one for the Clustered Index and one for the
non-clustered indexes. In each File Group, create as many physical files as
you have CPUs and independent disks. If you have enough disk counts, keep
the files from the Clustered Index seperated from the files of the
non-clustered indexes, but certainly use multiple files per file group, even
if you are limited by number of physical disks.
Also, create at least 2 log files of equal size and absolutely place these
on dedicated disks.
As far as capacity is concerned, the database size must be not be greater
than 1,048,515 TB, number of File Groups must not exceed 256 per database,
files per databse 32,767, data and log file sizes can not be larger than 32
TB each.
I do not think you will have a size problem; however, the row count will be
sufficient enough to warrant thoughtful database design, file layout, and
indexing strategy. You will also need to consider an appropriate Candidate
Key for the Cluster Index, absolutely DO NOT SET any surrogate Primary Key
as the clustered index, as is the default. This is usually the worst
possible candidate only superseded by not having a clustered index defined
at all.
The 2 GB limitation is for MSDE only.
Sincerely,
Anthony Thomas

"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:2aQsd.37569$1u.35532@.twister.nyroc.rr.com...
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OwmvZXl2EHA.924@.TK2MSFTNGP14.phx.gbl...
> Good luck with MySQL and 5 billion rows<g>.
Well, to fair, the largest MySQL db I've heard of has 10 billion rows...

>
|||Anthony,
I find your advice about clustered index and placement interesting, can you
point me to any good articles which covers this topic or even a book?
Thanks,
Saptagiri Tangudu
PNT Marketing Services
"AnthonyThomas" wrote:

> You will want to isolate that table and its non-clustered indexes across as
> many physical disk spindles as possible. Also, consider creating two
> seperate File Groups, one for the Clustered Index and one for the
> non-clustered indexes. In each File Group, create as many physical files as
> you have CPUs and independent disks. If you have enough disk counts, keep
> the files from the Clustered Index seperated from the files of the
> non-clustered indexes, but certainly use multiple files per file group, even
> if you are limited by number of physical disks.
> Also, create at least 2 log files of equal size and absolutely place these
> on dedicated disks.
> As far as capacity is concerned, the database size must be not be greater
> than 1,048,515 TB, number of File Groups must not exceed 256 per database,
> files per databse 32,767, data and log file sizes can not be larger than 32
> TB each.
> I do not think you will have a size problem; however, the row count will be
> sufficient enough to warrant thoughtful database design, file layout, and
> indexing strategy. You will also need to consider an appropriate Candidate
> Key for the Cluster Index, absolutely DO NOT SET any surrogate Primary Key
> as the clustered index, as is the default. This is usually the worst
> possible candidate only superseded by not having a clustered index defined
> at all.
> The 2 GB limitation is for MSDE only.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
> news:2aQsd.37569$1u.35532@.twister.nyroc.rr.com...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OwmvZXl2EHA.924@.TK2MSFTNGP14.phx.gbl...
>
> Well, to fair, the largest MySQL db I've heard of has 10 billion rows...
>
>

Advice Needed on Large Scale DataBases

We are in the process of developing a web indexing project similar to
http://www.grub.org/ . We will have a database that is going to contain
about 5 billion records. Currently I am split between MS SQL and My SQL for
this project. Some of the issues we are going to have is storage size. Based
on our math, our data base will be about 100 GB in size, is there a file
size limit with MS SQL (I heard some where there is a 2GB limit or was that
2 TB limit). A better design is a split data base but for now we would like
to use a single db.
JayGood luck with MySQL and 5 billion rows<g>. SQL Server does not have a
limit that you suggested except in it's free version (MSDE). THere are many
SQL Servers out there with 2TB or more of data. Even though 100GB is not
that large these days for SQL Server I can't stress enough that you do your
homework first and ensure you have a properly designed db schema and
application.
--
Andrew J. Kelly SQL MVP
"Jay Janarthanan" <jay@.objectcube.com> wrote in message
news:e8N7AKl2EHA.1452@.TK2MSFTNGP11.phx.gbl...
> We are in the process of developing a web indexing project similar to
> http://www.grub.org/ . We will have a database that is going to contain
> about 5 billion records. Currently I am split between MS SQL and My SQL
> for this project. Some of the issues we are going to have is storage size.
> Based on our math, our data base will be about 100 GB in size, is there a
> file size limit with MS SQL (I heard some where there is a 2GB limit or
> was that 2 TB limit). A better design is a split data base but for now we
> would like to use a single db.
> Jay
>|||Thanks Andrew. There is only one Table that is going to contain the 5B rows,
the rest of the tables are small in size.
I am more concerned about the HW requirements.
Jay
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OwmvZXl2EHA.924@.TK2MSFTNGP14.phx.gbl...
> Good luck with MySQL and 5 billion rows<g>. SQL Server does not have a
> limit that you suggested except in it's free version (MSDE). THere are
> many SQL Servers out there with 2TB or more of data. Even though 100GB is
> not that large these days for SQL Server I can't stress enough that you do
> your homework first and ensure you have a properly designed db schema and
> application.
> --
> Andrew J. Kelly SQL MVP
>
> "Jay Janarthanan" <jay@.objectcube.com> wrote in message
> news:e8N7AKl2EHA.1452@.TK2MSFTNGP11.phx.gbl...
>> We are in the process of developing a web indexing project similar to
>> http://www.grub.org/ . We will have a database that is going to contain
>> about 5 billion records. Currently I am split between MS SQL and My SQL
>> for this project. Some of the issues we are going to have is storage
>> size. Based on our math, our data base will be about 100 GB in size, is
>> there a file size limit with MS SQL (I heard some where there is a 2GB
>> limit or was that 2 TB limit). A better design is a split data base but
>> for now we would like to use a single db.
>> Jay
>>
>|||Jay Janarthanan wrote:
> Thanks Andrew. There is only one Table that is going to contain the
> 5B rows, the rest of the tables are small in size.
> I am more concerned about the HW requirements.
> Jay
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OwmvZXl2EHA.924@.TK2MSFTNGP14.phx.gbl...
>> Good luck with MySQL and 5 billion rows<g>. SQL Server does not
>> have a limit that you suggested except in it's free version (MSDE).
>> THere are many SQL Servers out there with 2TB or more of data. Even
>> though 100GB is not that large these days for SQL Server I can't
>> stress enough that you do your homework first and ensure you have a
>> properly designed db schema and application.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jay Janarthanan" <jay@.objectcube.com> wrote in message
>> news:e8N7AKl2EHA.1452@.TK2MSFTNGP11.phx.gbl...
>> We are in the process of developing a web indexing project similar
>> to http://www.grub.org/ . We will have a database that is going to
>> contain about 5 billion records. Currently I am split between MS
>> SQL and My SQL for this project. Some of the issues we are going to
>> have is storage size. Based on our math, our data base will be
>> about 100 GB in size, is there a file size limit with MS SQL (I
>> heard some where there is a 2GB limit or was that 2 TB limit). A
>> better design is a split data base but for now we would like to use
>> a single db. Jay
Hardware is easy. Lots of CPU, lots of fast drives, and lots of
memory...
If you put that large table on its own RAID 10 array if it gets writtent
to a lot, or a fast RAID 5 array if it is mostly read-only (batches at
night and small updates during the day are fine), you shold be ok. Use a
lot of drives in the array for best performance and put that array on
its own channel.
Regarding the hardware, you'll need to do some homework about the size
of the table and how frequently it gets accessed to determine if you'll
need the improved memory allowance of SQL Server 2000 EE. If you mostly
accessing single rows and have the necessary indexing in place, you may
find that memory is not a big concern and a fast 2-cpu system may
suffice. 100BG is not that large for SQL Server as Andrew mentioned. But
given that large table table, you probably want to make sure it's
isolated from the other data on its own drive subsystem. Drive are
cheap, though. You could throw 10 fast drives in a RAID 5 array for a
couple thousand and then use that array for backing up if the database
was idle at night. RAID 10 (1+0) is a little more expensive in terms of
drives, but has the best write and read performance. RAID 5 is the worst
for writing, but fast at reading, and the cheapest redundant solution.
David Gugick
Imceda Software
www.imceda.com|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OwmvZXl2EHA.924@.TK2MSFTNGP14.phx.gbl...
> Good luck with MySQL and 5 billion rows<g>.
Well, to fair, the largest MySQL db I've heard of has 10 billion rows...
>|||You will want to isolate that table and its non-clustered indexes across as
many physical disk spindles as possible. Also, consider creating two
seperate File Groups, one for the Clustered Index and one for the
non-clustered indexes. In each File Group, create as many physical files as
you have CPUs and independent disks. If you have enough disk counts, keep
the files from the Clustered Index seperated from the files of the
non-clustered indexes, but certainly use multiple files per file group, even
if you are limited by number of physical disks.
Also, create at least 2 log files of equal size and absolutely place these
on dedicated disks.
As far as capacity is concerned, the database size must be not be greater
than 1,048,515 TB, number of File Groups must not exceed 256 per database,
files per databse 32,767, data and log file sizes can not be larger than 32
TB each.
I do not think you will have a size problem; however, the row count will be
sufficient enough to warrant thoughtful database design, file layout, and
indexing strategy. You will also need to consider an appropriate Candidate
Key for the Cluster Index, absolutely DO NOT SET any surrogate Primary Key
as the clustered index, as is the default. This is usually the worst
possible candidate only superseded by not having a clustered index defined
at all.
The 2 GB limitation is for MSDE only.
Sincerely,
Anthony Thomas
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:2aQsd.37569$1u.35532@.twister.nyroc.rr.com...
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OwmvZXl2EHA.924@.TK2MSFTNGP14.phx.gbl...
> Good luck with MySQL and 5 billion rows<g>.
Well, to fair, the largest MySQL db I've heard of has 10 billion rows...
>|||Anthony,
I find your advice about clustered index and placement interesting, can you
point me to any good articles which covers this topic or even a book'
Thanks,
Saptagiri Tangudu
PNT Marketing Services
"AnthonyThomas" wrote:
> You will want to isolate that table and its non-clustered indexes across as
> many physical disk spindles as possible. Also, consider creating two
> seperate File Groups, one for the Clustered Index and one for the
> non-clustered indexes. In each File Group, create as many physical files as
> you have CPUs and independent disks. If you have enough disk counts, keep
> the files from the Clustered Index seperated from the files of the
> non-clustered indexes, but certainly use multiple files per file group, even
> if you are limited by number of physical disks.
> Also, create at least 2 log files of equal size and absolutely place these
> on dedicated disks.
> As far as capacity is concerned, the database size must be not be greater
> than 1,048,515 TB, number of File Groups must not exceed 256 per database,
> files per databse 32,767, data and log file sizes can not be larger than 32
> TB each.
> I do not think you will have a size problem; however, the row count will be
> sufficient enough to warrant thoughtful database design, file layout, and
> indexing strategy. You will also need to consider an appropriate Candidate
> Key for the Cluster Index, absolutely DO NOT SET any surrogate Primary Key
> as the clustered index, as is the default. This is usually the worst
> possible candidate only superseded by not having a clustered index defined
> at all.
> The 2 GB limitation is for MSDE only.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
> news:2aQsd.37569$1u.35532@.twister.nyroc.rr.com...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OwmvZXl2EHA.924@.TK2MSFTNGP14.phx.gbl...
> > Good luck with MySQL and 5 billion rows<g>.
>
> Well, to fair, the largest MySQL db I've heard of has 10 billion rows...
> >
>
>sql

Adventure Works.sln DEPLOY ERROR first time

After opening the Adventure Works.sln.

I build.

I deploy.

I get the following error ...

Build started: Project: Adventure Works DW, Configuration: Development

Started Building Analysis Services project: Incremental ....

Build complete -- 0 errors, 0 warnings

Deploy started: Project: Adventure Works DW, Configuration: Development

Performing an incremental deployment of the 'Adventure Works DW' database to the 'localhost' server.

Generating deployment script...

Add Database Adventure Works DW

Process Database Adventure Works DW

Done

Sending deployment script to the server...

Error -1056308996 : The attribute Destination Currency does not have any members.

Error -1056308996 : The attribute Parent Organization Key0 does not have any members.

Deploy complete -- 2 errors, 0 warnings

========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

========== Deploy: 0 succeeded, 1 failed, 0 skipped ==========

What am I doing wrong?

What do I do next?

AIM

The only thing I could think of is that maybe you are deploying the enterprise sample verison against a standard server....|||

I am using SQL Server Enterprise 2005 180-Eval (and I am choosing the Enterprise folder and not the Standard folder).

My (MSSQLSERVER) Analysis Services is started. Hmmm mm. Maybe its the Data connection? Maybe its not finding my AdventureWorksDW local database? Maybe I have to define this and not using the default? Maybe its the order. I did build. Next, I did deploy. Hmmm mm, maybe I a missing something?

Any ideas?

AIMDBA

|||

When I choose "Process" I get the following result. ...

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
Processing Database 'Adventure Works DW' completed successfully.
Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:01
Processing Dimension 'Destination Currency' completed successfully.
Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:47 AM; Duration: 0:00:00
Processing Dimension Attribute 'Destination Currency' completed successfully. 1 rows have been read.
Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:47 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[DimDestinationCurrency].[CurrencyName] AS [DimDestinationCurrencyCurrencyName0_0]
FROM
(

SELECT CurrencyKey, CurrencyAlternateKey, CurrencyName
FROM DimCurrency
WHERE (CurrencyKey IN
(SELECT DISTINCT CurrencyKey
FROM FactCurrencyRate))
)
AS [DimDestinationCurrency]
Processing Dimension Attribute 'Destination Currency Code' completed successfully. 1 rows have been read.
Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:47 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[DimDestinationCurrency].[CurrencyKey] AS [DimDestinationCurrencyCurrencyKey0_0],[DimDestinationCurrency].[CurrencyAlternateKey] AS [DimDestinationCurrencyCurrencyAlternateKey0_1],[DimDestinationCurrency].[CurrencyName] AS [DimDestinationCurrencyCurrencyName0_2]
FROM
(

SELECT CurrencyKey, CurrencyAlternateKey, CurrencyName
FROM DimCurrency
WHERE (CurrencyKey IN
(SELECT DISTINCT CurrencyKey
FROM FactCurrencyRate))
)
AS [DimDestinationCurrency]
Processing Dimension 'Organization' completed successfully.
Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:47 AM; Duration: 0:00:00
Processing Dimension Attribute 'Currency Code' completed successfully. 1 rows have been read.
Start time: 4/18/2006 10:39:48 AM; End time: 4/18/2006 10:39:47 AM; Duration: 0:00:-01
SQL queries 1
SELECT
DISTINCT
[dbo_DimOrganization].[CurrencyKey] AS [dbo_DimOrganizationCurrencyKey0_0],[dbo_DimOrganization].[CurrencyAlternateKey] AS [dbo_DimOrganizationCurrencyAlternateKey0_1]
FROM
(

SELECT o.OrganizationKey, o.ParentOrganizationKey, o.PercentageOfOwnership, o.OrganizationName, o.CurrencyKey, c.CurrencyAlternateKey
FROM DimOrganization AS o INNER JOIN
DimCurrency AS c ON o.CurrencyKey = c.CurrencyKey
)
AS [dbo_DimOrganization]
Processing Dimension Attribute 'Organization' completed successfully. 1 rows have been read.
Start time: 4/18/2006 10:39:48 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_DimOrganization].[OrganizationKey] AS [dbo_DimOrganizationOrganizationKey0_0],[dbo_DimOrganization].[OrganizationName] AS [dbo_DimOrganizationOrganizationName0_1],[dbo_DimOrganization].[PercentageOfOwnership] AS [dbo_DimOrganizationPercentageOfOwnership0_2],[dbo_DimOrganization].[CurrencyKey] AS [dbo_DimOrganizationCurrencyKey0_3],[dbo_DimOrganization].[ParentOrganizationKey] AS [dbo_DimOrganizationParentOrganizationKey0_4]
FROM
(

SELECT o.OrganizationKey, o.ParentOrganizationKey, o.PercentageOfOwnership, o.OrganizationName, o.CurrencyKey, c.CurrencyAlternateKey
FROM DimOrganization AS o INNER JOIN
DimCurrency AS c ON o.CurrencyKey = c.CurrencyKey
)
AS [dbo_DimOrganization]
Processing Hierarchy 'Organizations'.
Start time: 4/18/2006 10:39:48 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:00
Processing Dimension 'Sales Reason' completed successfully.
Start time: 4/18/2006 10:39:48 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:00
Errors and Warnings from Response
The attribute Destination Currency does not have any members.

Any ideas?

Thanks.

AIMDBA

Advantage of using a Data Source in a project

I am new to SSIS.

I came across the following article:

http://technet.microsoft.com/en-us/library/ms139848(SQL.90).aspx

It states that:

"A data source can be defined one time and then referenced by connection managers in multiple packages. You use a data source object in a package by adding a connection manager that references the data source object to the package. There is no dependency between a data source and the connection managers that reference it."

I have created a data source (DS1) and set it to point to a database say DB1. In the connection managers area, I create a connection manager CM1 using the datasource DS1.Now I edit DS1 to point to a different database DB2. When I open CM1 however it’s still pointing to DS1.

I guess this is because it’s said that there is no dependency between the connection manager and data source. My question is what exactly is the advantage of using a data source?

Priya

AFAIK, it is used only as a template of sorts for a data source connection. Other than that, I don't really know why one would use it. I have never used them.

Maybe someone else can shed some light onto why you should use it.|||

Hi Priya,

I went through the article and what i understood is:

"A datasource is created outside the reference of a package" , means you can share it across many packages by pointing your connection manger to the datasource you just created.Consider this scenario where you have 10 packages pointing to same source then you may use a Datasource so that all connection managers point ot same datasource.Hence at later point of time if you want to change the source you just need to update you datasource rather than updating each connection manager independently.

Note: Please correct me if I am wrong.

|||

When I started with SSIS , I also wanted to use Data source, since it seemd reasonable to reuse the Data source in various packages. Until you want to use package configuration files to store your connectionstrings. Then it is no longer possible to use datasources. From now on I do not use Data sources any longer. I am using package configuration files that contain the connctions strings of my connections, and in my packages I use the appropriate configuration file. I also use environment variables to proint to my package configuration files. This allows for a somewhat easier deployment of packages on developer and production servers.

|||This is the correct way of handling environment differences with SSIS.
As said, data sources are for design time.

A nice little "feature" of data sources: Delete it and it deletes all your connections which are derived from it.|||Hi all,

Thanks for ur replies.
I'v used configuration file to configure connection manager before and you can't configure data source with it.

Unni, as you'v said that:
"Hence at later point of time if you want to change the source you just need to update you datasource rather than updating each connection manager independently."

Did you try out updating the datasource to point to a different source? Does it get reflected in the connection managers? As i've mentioned in the 1st post the connection manager was still pointing to the older db in my case.
Priya

|||

Let me clarify why I am using configuration files and not datas sources:

- datasources are fine if you always edit your package with visual studio as a developer, no matter where your packages are deployed.

- I have to design a package on a development server where I have full access. but my package will be deployed in a production environment where I will have very little access. The sysadmin will now how to make the enviroment variable en how to edit the configuration file with notepad, those are all the tools he will need. The ssis packages will be restored on the production server via backup restore.

So as far I am concerned this way of working is SOX compliant, and therefore I do not use datasources.

|||In general, I think the consensus among most of the regular posters on the forums is that using data sources is more trouble than it is worth. If updating a data source actually updated all the connection managers derived from it, there might be a benefit to using them. But each connection manager maintains its own copy of the connection string, so there is not much value added by using the data source. Plus, the messages to Synchronize Connection Strings are really annoying |||

Hi,

"Hence at later point of time if you want to change the source you just need to update you datasource rather than updating each connection manager independently."

I have not tried this, I am using configuration files in my package.

|||Yes, if you open each and every package in the project, it will prompt you to change the connect string. It doesn't automatically propagate the change. Configurations are a much easier way of getting the same result.

ADP project on SQL 2000 sp4

We currently have a adp project linked with a Sql server 7 running NT4.
We need to replace old server with a win server 2003 with SQL 2000 sp4.
When viewing tables, stored procedures from ADP project all objects have
(dbo) after them. I then run into problems when trying to run saved reports -
the report is looking for the table name itself instead of tablename (dbo).
It would be a lot of work to fix all of the reports.
Thanks
Maybe you should create a view to meet client tool demands:
CREATE VIEW [table1 (dbo)] AS SELECT * FROM table1
so reports will access table1 through the view with the name they wanted for
"exchangerookie1994" <exchangerookie1994@.discussions.microsoft.com> wrote in
message news:03F79A1A-56AF-4440-BE3B-27C00AF3FB93@.microsoft.com...
> We currently have a adp project linked with a Sql server 7 running NT4.
> We need to replace old server with a win server 2003 with SQL 2000 sp4.
> When viewing tables, stored procedures from ADP project all objects have
> (dbo) after them. I then run into problems when trying to run saved
> reports -
> the report is looking for the table name itself instead of tablename
> (dbo).
> It would be a lot of work to fix all of the reports.
> Thanks
|||Is there a way to remove the (dbo) from everything. Do you know what causes
this. I tried the exact same thing on a SQL 2000 sp3 and eveything was fine -
no (dbo)
Should I run another instance of SQL 2000 with sp3 instead?
Thank you
"Alex Cieszinski" wrote:

> Maybe you should create a view to meet client tool demands:
> CREATE VIEW [table1 (dbo)] AS SELECT * FROM table1
> so reports will access table1 through the view with the name they wanted for
> "exchangerookie1994" <exchangerookie1994@.discussions.microsoft.com> wrote in
> message news:03F79A1A-56AF-4440-BE3B-27C00AF3FB93@.microsoft.com...
>
>
|||It's related to the security context you are using in the
ADP and what permissions that login, user has in SQL Server.
And it's handled a bit differently with different version of
Access - but I don't remember what they changed with 2002
and above from Access 2000.
I know that in Access 2000, if you connect as a sysadmin,
you won't see the dbo appended. All others will see dbo. You
can't remove dbo from everything, you should be creating the
Access objects to use fully qualified names using
Owner.Object.
This is more of an Access issue so you would want to post in
an Access newsgroup. Try:
microsoft.public.access.adp.sqlserver
-Sue
On Fri, 3 Mar 2006 08:13:29 -0800, exchangerookie1994
<exchangerookie1994@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Is there a way to remove the (dbo) from everything. Do you know what causes
>this. I tried the exact same thing on a SQL 2000 sp3 and eveything was fine -
>no (dbo)
>Should I run another instance of SQL 2000 with sp3 instead?
>Thank you
>"Alex Cieszinski" wrote:

ADP project on SQL 2000 sp4

We currently have a adp project linked with a Sql server 7 running NT4.
We need to replace old server with a win server 2003 with SQL 2000 sp4.
When viewing tables, stored procedures from ADP project all objects have
(dbo) after them. I then run into problems when trying to run saved reports -
the report is looking for the table name itself instead of tablename (dbo).
It would be a lot of work to fix all of the reports.
ThanksMaybe you should create a view to meet client tool demands:
CREATE VIEW [table1 (dbo)] AS SELECT * FROM table1
so reports will access table1 through the view with the name they wanted for
"exchangerookie1994" <exchangerookie1994@.discussions.microsoft.com> wrote in
message news:03F79A1A-56AF-4440-BE3B-27C00AF3FB93@.microsoft.com...
> We currently have a adp project linked with a Sql server 7 running NT4.
> We need to replace old server with a win server 2003 with SQL 2000 sp4.
> When viewing tables, stored procedures from ADP project all objects have
> (dbo) after them. I then run into problems when trying to run saved
> reports -
> the report is looking for the table name itself instead of tablename
> (dbo).
> It would be a lot of work to fix all of the reports.
> Thanks|||Is there a way to remove the (dbo) from everything. Do you know what causes
this. I tried the exact same thing on a SQL 2000 sp3 and eveything was fine -
no (dbo)
Should I run another instance of SQL 2000 with sp3 instead?
Thank you
"Alex Cieszinski" wrote:
> Maybe you should create a view to meet client tool demands:
> CREATE VIEW [table1 (dbo)] AS SELECT * FROM table1
> so reports will access table1 through the view with the name they wanted for
> "exchangerookie1994" <exchangerookie1994@.discussions.microsoft.com> wrote in
> message news:03F79A1A-56AF-4440-BE3B-27C00AF3FB93@.microsoft.com...
> > We currently have a adp project linked with a Sql server 7 running NT4.
> > We need to replace old server with a win server 2003 with SQL 2000 sp4.
> > When viewing tables, stored procedures from ADP project all objects have
> > (dbo) after them. I then run into problems when trying to run saved
> > reports -
> > the report is looking for the table name itself instead of tablename
> > (dbo).
> > It would be a lot of work to fix all of the reports.
> >
> > Thanks
>
>|||It's related to the security context you are using in the
ADP and what permissions that login, user has in SQL Server.
And it's handled a bit differently with different version of
Access - but I don't remember what they changed with 2002
and above from Access 2000.
I know that in Access 2000, if you connect as a sysadmin,
you won't see the dbo appended. All others will see dbo. You
can't remove dbo from everything, you should be creating the
Access objects to use fully qualified names using
Owner.Object.
This is more of an Access issue so you would want to post in
an Access newsgroup. Try:
microsoft.public.access.adp.sqlserver
-Sue
On Fri, 3 Mar 2006 08:13:29 -0800, exchangerookie1994
<exchangerookie1994@.discussions.microsoft.com> wrote:
>Is there a way to remove the (dbo) from everything. Do you know what causes
>this. I tried the exact same thing on a SQL 2000 sp3 and eveything was fine -
>no (dbo)
>Should I run another instance of SQL 2000 with sp3 instead?
>Thank you
>"Alex Cieszinski" wrote:
>> Maybe you should create a view to meet client tool demands:
>> CREATE VIEW [table1 (dbo)] AS SELECT * FROM table1
>> so reports will access table1 through the view with the name they wanted for
>> "exchangerookie1994" <exchangerookie1994@.discussions.microsoft.com> wrote in
>> message news:03F79A1A-56AF-4440-BE3B-27C00AF3FB93@.microsoft.com...
>> > We currently have a adp project linked with a Sql server 7 running NT4.
>> > We need to replace old server with a win server 2003 with SQL 2000 sp4.
>> > When viewing tables, stored procedures from ADP project all objects have
>> > (dbo) after them. I then run into problems when trying to run saved
>> > reports -
>> > the report is looking for the table name itself instead of tablename
>> > (dbo).
>> > It would be a lot of work to fix all of the reports.
>> >
>> > Thanks
>>
>>

ADP project on SQL 2000 sp4

We currently have a adp project linked with a Sql server 7 running NT4.
We need to replace old server with a win server 2003 with SQL 2000 sp4.
When viewing tables, stored procedures from ADP project all objects have
(dbo) after them. I then run into problems when trying to run saved reports
-
the report is looking for the table name itself instead of tablename (dbo).
It would be a lot of work to fix all of the reports.
ThanksMaybe you should create a view to meet client tool demands:
CREATE VIEW [table1 (dbo)] AS SELECT * FROM table1
so reports will access table1 through the view with the name they wanted for
"exchangerookie1994" <exchangerookie1994@.discussions.microsoft.com> wrote in
message news:03F79A1A-56AF-4440-BE3B-27C00AF3FB93@.microsoft.com...
> We currently have a adp project linked with a Sql server 7 running NT4.
> We need to replace old server with a win server 2003 with SQL 2000 sp4.
> When viewing tables, stored procedures from ADP project all objects have
> (dbo) after them. I then run into problems when trying to run saved
> reports -
> the report is looking for the table name itself instead of tablename
> (dbo).
> It would be a lot of work to fix all of the reports.
> Thanks|||Is there a way to remove the (dbo) from everything. Do you know what causes
this. I tried the exact same thing on a SQL 2000 sp3 and eveything was fine
-
no (dbo)
Should I run another instance of SQL 2000 with sp3 instead?
Thank you
"Alex Cieszinski" wrote:

> Maybe you should create a view to meet client tool demands:
> CREATE VIEW [table1 (dbo)] AS SELECT * FROM table1
> so reports will access table1 through the view with the name they wanted f
or
> "exchangerookie1994" <exchangerookie1994@.discussions.microsoft.com> wrote
in
> message news:03F79A1A-56AF-4440-BE3B-27C00AF3FB93@.microsoft.com...
>
>|||It's related to the security context you are using in the
ADP and what permissions that login, user has in SQL Server.
And it's handled a bit differently with different version of
Access - but I don't remember what they changed with 2002
and above from Access 2000.
I know that in Access 2000, if you connect as a sysadmin,
you won't see the dbo appended. All others will see dbo. You
can't remove dbo from everything, you should be creating the
Access objects to use fully qualified names using
Owner.Object.
This is more of an Access issue so you would want to post in
an Access newsgroup. Try:
microsoft.public.access.adp.sqlserver
-Sue
On Fri, 3 Mar 2006 08:13:29 -0800, exchangerookie1994
<exchangerookie1994@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Is there a way to remove the (dbo) from everything. Do you know what causes
>this. I tried the exact same thing on a SQL 2000 sp3 and eveything was fine
-
>no (dbo)
>Should I run another instance of SQL 2000 with sp3 instead?
>Thank you
>"Alex Cieszinski" wrote:
>

ADP connection to SQL

I would like any advice on connecting an Access Project to SQL Server 2000. Authentication is in mixed mode and user enter a name and password. When he click cancel twice, it will appear error - he enter without check a name and password. How I can prevent that?Any chance of changing the authentication to Windows only?|||

I don't understand what is happening in your scenario - can you explain with more details how you set up your project to connect to SQL Server?

Thanks
Laurentiu

ADP connection to SQL

I would like any advice on connecting an Access Project to SQL Server 2000. Authentication is in mixed mode and user enter a name and password. When he click cancel twice, it will appear error - he enter without check a name and password. How I can prevent that?Any chance of changing the authentication to Windows only?|||

I don't understand what is happening in your scenario - can you explain with more details how you set up your project to connect to SQL Server?

Thanks
Laurentiu

ADP and Sql server

hey
I m new to this and i dont know how to create adp project using access and sql server.
actually what i hv heard from my manager that we gonna create the front end in access and backend in sql server 2000.
I dont know from where to start reading abt it. if any body has good links or book plz send me.
thx in advance.It's not too difficult. Just open access and create a new database, selecting Access Data Project as the type.
An ADP file has all the forms, reports, macros, and modules that you would expect from an Access database, but all the data is stored on the server.|||To be fair, if you are new to relational databases, Access and SQL Server then it is a steep learning curve from now on in.

Step #1 before you do anything - get some sort of feel for relational database design by reading this closely:
http://www.r937.com/relational.html|||And just so you know, you don't have to create an ADP; an MDB will work fine with SQL Server. There are advantages and disadvantages to each. Personally, I continue to use MDB's with it, and Microsoft itself seems to have moved away from ADP's (or at least not pushed them forward).|||What? You would use linked tables over an ADP design?

I'm trying to think of any way that linked tables would be a better implementation than ADP, but I'm not coming up with much...

Also, please support your statement that MS is moving away from ADPs. I've not heard anything about this.|||Blindman, I have a great deal of respect for you and your opinion. I didn't say linked tables were better, I simply said that you did not HAVE to use an ADP with SQL Server, and I said there were advantages and disadvantages to each. As to your question about whether I would use them, I not only would, I do.

I can quote several Access MVP's on the topic (and perhaps should have disclosed that I am one myself), but here's the first I found from a highly respected MVP on another site, Pat Hartman:

the .adp is being phased out. I would recommend not creating new applications in the .adp format. Unlike the .adp, Access .mdb's can be used just as effectively as front ends to ANY back end database for which there is an ODBC driver. The limitations of the .adp are too restrictive and SQL Server centric. The format has not been widely adoped and so there is no big push to enhance the feature. The .adp will still be supported in Access 12 when it is released but no enhancements have been made. In fact, although existing .adp's will be able to use SQL Server 2005, they will not be able to create new databases through the Access GUI as they can with older versions of SQL Server.

If you like I can find others, but suffice it to say that there are many respected people who do not like ADP's. There are also many other respected people, such as yourself, who do. Again, my main point was simply to point out that it was not a requirement to use an ADP, but an option. I apologize if it came off otherwise.|||I can quote several Access MVP's on the topic (and perhaps should have disclosed that I am one myself)Paul - is this recent? I don't remember this from the first time I came across you a year or two ago - I checked you out then after a few very positive references and would have thought I would have noted it.|||FWIW I exclusively use MDB\Es too as I prefer the flexibility they allow. I can appreciate that ADPs might be easier and more intuitive (though not for me) and they do go some way to providing an all-in-one GUI. (and perhaps I should disclose that I am not an MVP myself :))|||pbaldy, thanks for supplying the quote from Pat Hartman. If what he says is true, I am most disappointed. I found ADPs to be much more convenient and much more efficient than linked tables. As a rapid application development platform for SQL Server, ADP files could not be beat.
By the way, I wan't being accusatory in my post. Sometimes, when I say "Please support your statement", I really am just looking for more information. The rude jerk persona I exude is really just an act. Like Paris Hilton's stupidity, you know...|||Paul - is this recent?

No...it's been over a week now! :p

Blindman, no offense taken here. If I can't come up with some backup for something I say, I shouldn't say it in the first place. By the way, just saw this post elsewhere this morning, from author/MVP John Viescas:

If you're using a Project file (.adp) the answer is no. For what it's
worth, Microsoft has given up on Project files. No enhancements in V12, and
I suspect the feature will be deprecated in VNext. Microsoft now admits
that the best front end to an SQL Server database in an mdb file using
linked tables. When you do that, you can have local tables and also link to
other databases - Oracle or MySQL.|||Please bare with me, what the finaly decision is that its better to link the tables and keep Access as an MDB rather then turn them into an ADP?? Is this correct??|||Well, personally, I would strongly disagree.
Linked tables are more difficult to manage and less efficient than using an ADP application.
With ADP, all the code is executed at the server. While with MDB linked tables they code may or may not be issued as a pass-through sql statement. If Access is unable to construct the pass-through statement it will suck the data from database over your network and perform the calculation locally. You can bet that will be slow.
With an ADP file you have one link to the source database, and that is it. And it is very easy to redirect that link to a different source. With MDB files every external object must be linked separately.|||MDB = more flexibility but requires more sophisticated coding (read expertise and coding hours) to be truly efficient.
ADP = easier to use (especially if you are more familiar with SQL Server) but is less flexible. Easier to leverage SSs power. I know an ADP cannot conntect to two instances simultaneously (hence less flexible). Is it also the same with SS dbs?

No...it's been over a week now! :p Congratulations Paul. I know you only flirt with posting over here but I have heard that you have built up a superb reputation over at ... er... the other site and your MVPship is well earned. :)|||I think asking which is better is like asking whether a sports car or pickup truck is better. They're different animals. They each have pros and cons, and which is better for you depends on your situation and needs.

Thanks pootle. I actually visit here a lot, it's just that there are a lot of very strong users here, and most of the questions are already answered by the time I see them. If you guys would leave me some scraps, I'd post more. :cool:|||If you guys would leave me some scraps, I'd post more. :cool:

Yeah, right..|||Yeah, right..

I'm not quite sure how to take that. Have I offended you in some way?|||No, not at all...it's more of a reference to the fact that the posts get answered to quickly..and that you would answer if they weren't already answered...hence my response

The core here are sort of addicted

And I doubt anyone in the Core would ever get offende...by anything

We usually get amused|||Well, I didn't say too quickly, I simply said they were answered by the time I saw them. It was meant to be a compliment to the other users here.|||Yup -
"quick response > accurate response" is my motto ;)
I sense a new sig on the horizon

ADOMD.net Can't get the KPI.ID or Cube.ID

When you open an AS project, you will see every object has an ID property, just like KPI.ID and cube.ID and Dimention.ID. And that's diffrent of the Name property. You could chanage the name property to everything you like, but when you created an object, you could not change it's ID.

When i use the ADOMD.NET, I could do this:

Dim myKPIConnection As AdomdConnection
Dim myCubeDef As CubeDef
Dim k As Kpi

myConnectionString = "Data Source=" + myOlapServer + ";Catalog=" + myOlapDatabase + ";Provider=MSOLAP;"

myKPIConnection = New AdomdConnection(myConnectionString)
myKPIConnection.Open()

For i = 0 To myKPIConnection.Cubes.Count - 1

If myKPIConnection.Cubes(i).Type = CubeType.Cube Then

myCubeDef = myKPIConnection.Cubes(i)

k = myKPIConnection.Cubes(i).Kpis(0)

MessageBox.Show(k.Name)

MessageBox.Show(k.Caption)

end if
next

But the question is, there's no k.ID or cubes(i).ID property there!

The k.Name and k.Caption both refer to the kpi's Name property in the AS server in factly.

Is that means we could not use the object 's ID property in the ADOMD.NET?

Another question, in MDX, we could only refer a dimention by it's name, and could not by it's ID, right?

ivanchain wrote:

Is that means we could not use the object 's ID property in the ADOMD.NET?

Another question, in MDX, we could only refer a dimention by it's name, and could not by it's ID, right?

That's right. In ADOMD.NET and MDX all references are done by name. The ID property is mainly used by the administrative APIs

ADODB errors since SQL 2000 upgrade

I have a VB 6.0 project that uses ADODB. Friday, it worked fine; Monday it
started generating the following two errors, one right after the other in
the order in which I've listed them:
-2147217885: Cursor operation conflict
2147217887: Multiple-step OLE DB operation generated errors. Check each OLE
DB status value, if available. No work was done.
The only difference is that it is now running against SQL 2000 database in
SQL 7.0 compatibility mode (Friday it was running against a SQL 7.0
database)
Any hits?- what the MDAC level on the old config and on the new config ?
jobi
"Nathan W. Phelps" <NOSPAM.nphelps@.solarc.com> wrote in message
news:#9BRQR5hDHA.2516@.TK2MSFTNGP09.phx.gbl...
> I have a VB 6.0 project that uses ADODB. Friday, it worked fine; Monday
it
> started generating the following two errors, one right after the other in
> the order in which I've listed them:
> -2147217885: Cursor operation conflict
> 2147217887: Multiple-step OLE DB operation generated errors. Check each
OLE
> DB status value, if available. No work was done.
> The only difference is that it is now running against SQL 2000 database in
> SQL 7.0 compatibility mode (Friday it was running against a SQL 7.0
> database)
> Any hits?
>|||On the server, I'm assuming that installing SQL 2000 upgrades the MDAC
stack. However, the client which is experiencing this issue was not
touched.
"jobi" <jobi@.reply2.group> wrote in message
news:bldugt$njq$1@.reader08.wxs.nl...
> - what the MDAC level on the old config and on the new config ?
> jobi
> "Nathan W. Phelps" <NOSPAM.nphelps@.solarc.com> wrote in message
> news:#9BRQR5hDHA.2516@.TK2MSFTNGP09.phx.gbl...
> > I have a VB 6.0 project that uses ADODB. Friday, it worked fine; Monday
> it
> > started generating the following two errors, one right after the other
in
> > the order in which I've listed them:
> >
> > -2147217885: Cursor operation conflict
> >
> > 2147217887: Multiple-step OLE DB operation generated errors. Check each
> OLE
> > DB status value, if available. No work was done.
> >
> > The only difference is that it is now running against SQL 2000 database
in
> > SQL 7.0 compatibility mode (Friday it was running against a SQL 7.0
> > database)
> >
> > Any hits?
> >
> >
>

ADO Interop Using MDAC 2.8 On Vista

I am trying to load a project under VS2005 under Windows Vista that references an ADOX wrapper that is looking for version 2.8.0.0. The reference fails because the library is not registered.

I thought maybe installing MDAC 2.8 would solve my problems, but when I run the installer for that, it briefly puts up a dialog that it is extracting files, then it disappears.

Any ideas?

--Bruce

Hi Bruce,

Did you ever get this problem solved? I'm getting a similar problem on a Windows 2000 machine. Whenever we try to do ADOX Interop we get a "Library not registered" exception. We can't replicate this behaviour as it's on a client machine using Terminal Services.

Regards,

Brent

|||

Hi,

Just a note to all, registering the msadox.dll could solve the "Library not registered" problem when trying to use ADOX Interop.

regsvr32 "C:\Program Files\Common Files\System\ado\msadox.dll"

Hope this helps someone.

- Brent

|||

I have found that registering msadox does not help in this case because the msadox version that lives in vista is 6.0, whereas the latest version I have been able to find is 2.8. Does anyone know where the 6.0 version comes from?

Jeff

|||

I have found that MDAC has been replaced by WDAC as this article describes: http://msdn2.microsoft.com/en-us/library/ms692897.aspx That is where the 6.0 version comes from. Now I just need to know how to get 2.8 on the system.

|||

Take a look at this link that talks about Redistributing MDAC 28 : http://msdn2.microsoft.com/en-us/library/ms693148.aspx

Also can you add "Microsoft ActiveX Data Objects 2.8 Library", "Microsoft ADO Ext 6.0 for DDL and Security" to your project references and see if it helps?

msado28.tlb should be there in \Program Files\Common Files\System\ado location

Hope this helps

|||

Okay, so have any of the above lead to a solution? My problem is that I have this new development laptop with Vista. But my code needs to run on Server 2000/2003 and XP. So how do I get my project to build with a reference to ADOX 2.8 on Vista? Or, if I replace my ADOX 2.8 reference with one to ADOX 6.0, will I be able to install my project on an XP machine?

I'm posting this because I'm not sure if any of the above have actually fixed the problem. Please correct me if I'm wrong.

Thanks.

Kenneth.

|||Could you try having your msadox.dll of the lowest version you want create .net metadata dll like:

tlbimp msadox.dll /out:ADOX28.dll

Then put this ADOX28.dll file on your development machine and add reference to it.|||

Okay, so that seems to get me to be able to build if I do this:

using ADOX = ADOX28;

But I don't think that'll help me deploy to XP right?

So I did this on an XP development machine with ADOX installed then moved the resultant output to my Vista development machine:

tlbimp ADOX.dll /namespace:ADOX /asmversion:2.8 /out:ADOX28.dll

So now I can simply do:

using ADOX;

Thanks.

Kenneth.

ADO Interop Using MDAC 2.8 On Vista

I am trying to load a project under VS2005 under Windows Vista that references an ADOX wrapper that is looking for version 2.8.0.0. The reference fails because the library is not registered.

I thought maybe installing MDAC 2.8 would solve my problems, but when I run the installer for that, it briefly puts up a dialog that it is extracting files, then it disappears.

Any ideas?

--Bruce

Hi Bruce,

Did you ever get this problem solved? I'm getting a similar problem on a Windows 2000 machine. Whenever we try to do ADOX Interop we get a "Library not registered" exception. We can't replicate this behaviour as it's on a client machine using Terminal Services.

Regards,

Brent

|||

Hi,

Just a note to all, registering the msadox.dll could solve the "Library not registered" problem when trying to use ADOX Interop.

regsvr32 "C:\Program Files\Common Files\System\ado\msadox.dll"

Hope this helps someone.

- Brent

|||

I have found that registering msadox does not help in this case because the msadox version that lives in vista is 6.0, whereas the latest version I have been able to find is 2.8. Does anyone know where the 6.0 version comes from?

Jeff

|||

I have found that MDAC has been replaced by WDAC as this article describes: http://msdn2.microsoft.com/en-us/library/ms692897.aspx That is where the 6.0 version comes from. Now I just need to know how to get 2.8 on the system.

|||

Take a look at this link that talks about Redistributing MDAC 28 : http://msdn2.microsoft.com/en-us/library/ms693148.aspx

Also can you add "Microsoft ActiveX Data Objects 2.8 Library", "Microsoft ADO Ext 6.0 for DDL and Security" to your project references and see if it helps?

msado28.tlb should be there in \Program Files\Common Files\System\ado location

Hope this helps

|||

Okay, so have any of the above lead to a solution? My problem is that I have this new development laptop with Vista. But my code needs to run on Server 2000/2003 and XP. So how do I get my project to build with a reference to ADOX 2.8 on Vista? Or, if I replace my ADOX 2.8 reference with one to ADOX 6.0, will I be able to install my project on an XP machine?

I'm posting this because I'm not sure if any of the above have actually fixed the problem. Please correct me if I'm wrong.

Thanks.

Kenneth.

|||Could you try having your msadox.dll of the lowest version you want create .net metadata dll like:

tlbimp msadox.dll /out:ADOX28.dll

Then put this ADOX28.dll file on your development machine and add reference to it.|||

Okay, so that seems to get me to be able to build if I do this:

using ADOX = ADOX28;

But I don't think that'll help me deploy to XP right?

So I did this on an XP development machine with ADOX installed then moved the resultant output to my Vista development machine:

tlbimp ADOX.dll /namespace:ADOX /asmversion:2.8 /out:ADOX28.dll

So now I can simply do:

using ADOX;

Thanks.

Kenneth.

ADO Interop Using MDAC 2.8 On Vista

I am trying to load a project under VS2005 under Windows Vista that references an ADOX wrapper that is looking for version 2.8.0.0. The reference fails because the library is not registered.

I thought maybe installing MDAC 2.8 would solve my problems, but when I run the installer for that, it briefly puts up a dialog that it is extracting files, then it disappears.

Any ideas?

--Bruce

Hi Bruce,

Did you ever get this problem solved? I'm getting a similar problem on a Windows 2000 machine. Whenever we try to do ADOX Interop we get a "Library not registered" exception. We can't replicate this behaviour as it's on a client machine using Terminal Services.

Regards,

Brent

|||

Hi,

Just a note to all, registering the msadox.dll could solve the "Library not registered" problem when trying to use ADOX Interop.

regsvr32 "C:\Program Files\Common Files\System\ado\msadox.dll"

Hope this helps someone.

- Brent

|||

I have found that registering msadox does not help in this case because the msadox version that lives in vista is 6.0, whereas the latest version I have been able to find is 2.8. Does anyone know where the 6.0 version comes from?

Jeff

|||

I have found that MDAC has been replaced by WDAC as this article describes: http://msdn2.microsoft.com/en-us/library/ms692897.aspx That is where the 6.0 version comes from. Now I just need to know how to get 2.8 on the system.

|||

Take a look at this link that talks about Redistributing MDAC 28 : http://msdn2.microsoft.com/en-us/library/ms693148.aspx

Also can you add "Microsoft ActiveX Data Objects 2.8 Library", "Microsoft ADO Ext 6.0 for DDL and Security" to your project references and see if it helps?

msado28.tlb should be there in \Program Files\Common Files\System\ado location

Hope this helps

|||

Okay, so have any of the above lead to a solution? My problem is that I have this new development laptop with Vista. But my code needs to run on Server 2000/2003 and XP. So how do I get my project to build with a reference to ADOX 2.8 on Vista? Or, if I replace my ADOX 2.8 reference with one to ADOX 6.0, will I be able to install my project on an XP machine?

I'm posting this because I'm not sure if any of the above have actually fixed the problem. Please correct me if I'm wrong.

Thanks.

Kenneth.

|||Could you try having your msadox.dll of the lowest version you want create .net metadata dll like:

tlbimp msadox.dll /out:ADOX28.dll

Then put this ADOX28.dll file on your development machine and add reference to it.|||

Okay, so that seems to get me to be able to build if I do this:

using ADOX = ADOX28;

But I don't think that'll help me deploy to XP right?

So I did this on an XP development machine with ADOX installed then moved the resultant output to my Vista development machine:

tlbimp ADOX.dll /namespace:ADOX /asmversion:2.8 /out:ADOX28.dll

So now I can simply do:

using ADOX;

Thanks.

Kenneth.

ADO Interop Using MDAC 2.8 On Vista

I am trying to load a project under VS2005 under Windows Vista that references an ADOX wrapper that is looking for version 2.8.0.0. The reference fails because the library is not registered.

I thought maybe installing MDAC 2.8 would solve my problems, but when I run the installer for that, it briefly puts up a dialog that it is extracting files, then it disappears.

Any ideas?

--Bruce

Hi Bruce,

Did you ever get this problem solved? I'm getting a similar problem on a Windows 2000 machine. Whenever we try to do ADOX Interop we get a "Library not registered" exception. We can't replicate this behaviour as it's on a client machine using Terminal Services.

Regards,

Brent

|||

Hi,

Just a note to all, registering the msadox.dll could solve the "Library not registered" problem when trying to use ADOX Interop.

regsvr32 "C:\Program Files\Common Files\System\ado\msadox.dll"

Hope this helps someone.

- Brent

|||

I have found that registering msadox does not help in this case because the msadox version that lives in vista is 6.0, whereas the latest version I have been able to find is 2.8. Does anyone know where the 6.0 version comes from?

Jeff

|||

I have found that MDAC has been replaced by WDAC as this article describes: http://msdn2.microsoft.com/en-us/library/ms692897.aspx That is where the 6.0 version comes from. Now I just need to know how to get 2.8 on the system.

|||

Take a look at this link that talks about Redistributing MDAC 28 : http://msdn2.microsoft.com/en-us/library/ms693148.aspx

Also can you add "Microsoft ActiveX Data Objects 2.8 Library", "Microsoft ADO Ext 6.0 for DDL and Security" to your project references and see if it helps?

msado28.tlb should be there in \Program Files\Common Files\System\ado location

Hope this helps

|||

Okay, so have any of the above lead to a solution? My problem is that I have this new development laptop with Vista. But my code needs to run on Server 2000/2003 and XP. So how do I get my project to build with a reference to ADOX 2.8 on Vista? Or, if I replace my ADOX 2.8 reference with one to ADOX 6.0, will I be able to install my project on an XP machine?

I'm posting this because I'm not sure if any of the above have actually fixed the problem. Please correct me if I'm wrong.

Thanks.

Kenneth.

|||Could you try having your msadox.dll of the lowest version you want create .net metadata dll like:

tlbimp msadox.dll /out:ADOX28.dll

Then put this ADOX28.dll file on your development machine and add reference to it.|||

Okay, so that seems to get me to be able to build if I do this:

using ADOX = ADOX28;

But I don't think that'll help me deploy to XP right?

So I did this on an XP development machine with ADOX installed then moved the resultant output to my Vista development machine:

tlbimp ADOX.dll /namespace:ADOX /asmversion:2.8 /out:ADOX28.dll

So now I can simply do:

using ADOX;

Thanks.

Kenneth.

ADO Error Collection in VB With SQL Prints

In Visual Basic using ADO, I am trying to get print
statements from SQL into my VB project. How does query
analyzer get the print statements from the server? I am
able to display the first TWO print statements using the
[recordset].NextRecordset command, but I get an error if
I try to go past the second recordset. Please help if
you can.
[CODE]
Dim objerr As ADODB.Error
On Error GoTo err_check
Do
For Each objerr In gcn.Errors
GetSQLPrints = GetSQLPrints & vbCrLf &
objerr.Description
rs.NextRecordset
Next
Loop
[/CODE]
Only Prints two... I don't know why. Please help.It looks like you're executing a NextRecordset during your Errors collection
iteration. I suggest you complete the iteration before NextRecordset since
multiple messages can be returned along with a recordset.
Below is a VBScript example that shows one method to process multiple
recordsets with messages. In VB, another method is to handle the ADO
Connection InfoMessage event and process the messages in your event handler.
SqlScript = _
"PRINT 'test message 1'" & vbCrLf & _
"PRINT 'test message 2'" & vbCrLf & _
"SELECT 3" & vbCrLf & _
"PRINT 'test message 4'" & vbCrLf & _
"SELECT 5" & vbCrLf & _
"PRINT 'test message 6'" & vbCrLf
Set MyRecordset = MyConnection.Execute(SqlScript)
Message = ""
Do While Not MyRecordset Is Nothing
RecordsetNumber = RecordsetNumber + 1
Message = Message & "Recordset " & _
RecordsetNumber & ":" & VbCrLf
If MyConnection.Errors.Count > 0 Then
For Each SqlError In MyConnection.Errors
Message = Message & vbTab & "Message: " & _
SqlError.Description & vbCrLf
Next
Else
Message = Message & vbTab & _
"No messages." & vbCrLf
End If
If MyRecordset.State = adStateOpen Then
Message = Message & vbTab & _
"Rowset returned." & vbCrLf
Else
Message = Message & vbTab & _
"No rowset returned." & vbCrLf
End If
Message = Message & vbCrLf
Set MyRecordset = MyRecordset.NextRecordset
Loop
Message = Message & "Recordset is Nothing."
MyConnection.Close
MsgBox Message
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike B" <BGates@.Microsoft.com> wrote in message
news:034801c3c671$449addf0$a101280a@.phx.gbl...
> In Visual Basic using ADO, I am trying to get print
> statements from SQL into my VB project. How does query
> analyzer get the print statements from the server? I am
> able to display the first TWO print statements using the
> [recordset].NextRecordset command, but I get an error if
> I try to go past the second recordset. Please help if
> you can.
> [CODE]
> Dim objerr As ADODB.Error
> On Error GoTo err_check
> Do
> For Each objerr In gcn.Errors
> GetSQLPrints = GetSQLPrints & vbCrLf &
> objerr.Description
> rs.NextRecordset
> Next
> Loop
> [/CODE]
> Only Prints two... I don't know why. Please help.|||For our connection, we are using SQLOLEDB...
When we run you code example, getting an error saying:
"Current Provider does not support returning multiple
recordsets from a single execution"
If we take the do-while loop out, we get a message box
saying:
Recordset 1:
Message: text message 1
No recordset returned
Recordset is nothing
We did add the following Dim's:
Dim SqlScript as string
dim Myrecordset as new adodb.recordset
dim recordsetnumber as long
dim sqlerror as error
dim message as string
>--Original Message--
>It looks like you're executing a NextRecordset during
your Errors collection
>iteration. I suggest you complete the iteration before
NextRecordset since
>multiple messages can be returned along with a recordset.
>Below is a VBScript example that shows one method to
process multiple
>recordsets with messages. In VB, another method is to
handle the ADO
>Connection InfoMessage event and process the messages in
your event handler.
>SqlScript = _
> "PRINT 'test message 1'" & vbCrLf & _
> "PRINT 'test message 2'" & vbCrLf & _
> "SELECT 3" & vbCrLf & _
> "PRINT 'test message 4'" & vbCrLf & _
> "SELECT 5" & vbCrLf & _
> "PRINT 'test message 6'" & vbCrLf
>Set MyRecordset = MyConnection.Execute(SqlScript)
>Message = ""
>Do While Not MyRecordset Is Nothing
> RecordsetNumber = RecordsetNumber + 1
> Message = Message & "Recordset " & _
> RecordsetNumber & ":" & VbCrLf
> If MyConnection.Errors.Count > 0 Then
> For Each SqlError In MyConnection.Errors
> Message = Message & vbTab & "Message: " & _
> SqlError.Description & vbCrLf
> Next
> Else
> Message = Message & vbTab & _
> "No messages." & vbCrLf
> End If
> If MyRecordset.State = adStateOpen Then
> Message = Message & vbTab & _
> "Rowset returned." & vbCrLf
> Else
> Message = Message & vbTab & _
> "No rowset returned." & vbCrLf
> End If
> Message = Message & vbCrLf
> Set MyRecordset = MyRecordset.NextRecordset
>Loop
>Message = Message & "Recordset is Nothing."
>MyConnection.Close
>MsgBox Message
>
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>
>"Mike B" <BGates@.Microsoft.com> wrote in message
>news:034801c3c671$449addf0$a101280a@.phx.gbl...
>> In Visual Basic using ADO, I am trying to get print
>> statements from SQL into my VB project. How does query
>> analyzer get the print statements from the server? I am
>> able to display the first TWO print statements using
the
>> [recordset].NextRecordset command, but I get an error
if
>> I try to go past the second recordset. Please help if
>> you can.
>> [CODE]
>> Dim objerr As ADODB.Error
>> On Error GoTo err_check
>> Do
>> For Each objerr In gcn.Errors
>> GetSQLPrints = GetSQLPrints & vbCrLf &
objerr.Description
>> rs.NextRecordset
>> Next
>> Loop
>> [/CODE]
>> Only Prints two... I don't know why. Please help.
>
>.
>|||It looks like there are some differences in behavior between VB and
VBScript. On my system, the VB code below uses Recordset.Open instead of
Connection.Execute and returns the same result as the original VBScript I
posted.
Dim SqlScript As String
Dim MyConnection As New ADODB.Connection
Dim MyRecordset As New ADODB.Recordset
Dim recordsetnumber As Long
Dim sqlerror As Error
Dim message As String
Dim ConnectionString As String
Dim lastError As Integer
ConnectionString = _
"Provider=SQLOLEDB" & _
";Data Source=MyServer" & _
";Integrated Security=SSPI"
MyConnection.Open ConnectionString
SqlScript = _
"SET NOCOUNT ON" & vbCrLf & _
"PRINT 'test message 1'" & vbCrLf & _
"PRINT 'test message 2'" & vbCrLf & _
"SELECT 3" & vbCrLf & _
"PRINT 'test message 4'" & vbCrLf & _
"SELECT 5" & vbCrLf & _
"PRINT 'test message 6'" & vbCrLf
MyRecordset.Open SqlScript, MyConnection
message = ""
Do While Not MyRecordset.ActiveCommand Is Nothing
recordsetnumber = recordsetnumber + 1
message = message & "Recordset " & _
recordsetnumber & ":" & vbCrLf
If MyConnection.Errors.Count > 0 Then
For Each sqlerror In MyConnection.Errors
message = message & vbTab & "Message: " & _
sqlerror.Description & vbCrLf
Next
Else
message = message & vbTab & _
"No messages." & vbCrLf
End If
If MyRecordset.State = adStateOpen Then
message = message & vbTab & _
"Rowset returned." & vbCrLf
Else
message = message & vbTab & _
"No rowset returned." & vbCrLf
End If
message = message & vbCrLf
On Error Resume Next
Set MyRecordset = MyRecordset.NextRecordset
Loop
message = message & "Recordset is Nothing."
MyConnection.Close
MsgBox message
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike B/Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:10e801c3c70e$22406160$a301280a@.phx.gbl...
> For our connection, we are using SQLOLEDB...
> When we run you code example, getting an error saying:
> "Current Provider does not support returning multiple
> recordsets from a single execution"
> If we take the do-while loop out, we get a message box
> saying:
> Recordset 1:
> Message: text message 1
> No recordset returned
> Recordset is nothing
> We did add the following Dim's:
> Dim SqlScript as string
> dim Myrecordset as new adodb.recordset
> dim recordsetnumber as long
> dim sqlerror as error
> dim message as string
>
> >--Original Message--
> >It looks like you're executing a NextRecordset during
> your Errors collection
> >iteration. I suggest you complete the iteration before
> NextRecordset since
> >multiple messages can be returned along with a recordset.
> >
> >Below is a VBScript example that shows one method to
> process multiple
> >recordsets with messages. In VB, another method is to
> handle the ADO
> >Connection InfoMessage event and process the messages in
> your event handler.
> >
> >SqlScript = _
> > "PRINT 'test message 1'" & vbCrLf & _
> > "PRINT 'test message 2'" & vbCrLf & _
> > "SELECT 3" & vbCrLf & _
> > "PRINT 'test message 4'" & vbCrLf & _
> > "SELECT 5" & vbCrLf & _
> > "PRINT 'test message 6'" & vbCrLf
> >
> >Set MyRecordset = MyConnection.Execute(SqlScript)
> >
> >Message = ""
> >Do While Not MyRecordset Is Nothing
> > RecordsetNumber = RecordsetNumber + 1
> > Message = Message & "Recordset " & _
> > RecordsetNumber & ":" & VbCrLf
> > If MyConnection.Errors.Count > 0 Then
> > For Each SqlError In MyConnection.Errors
> > Message = Message & vbTab & "Message: " & _
> > SqlError.Description & vbCrLf
> > Next
> > Else
> > Message = Message & vbTab & _
> > "No messages." & vbCrLf
> > End If
> > If MyRecordset.State = adStateOpen Then
> > Message = Message & vbTab & _
> > "Rowset returned." & vbCrLf
> > Else
> > Message = Message & vbTab & _
> > "No rowset returned." & vbCrLf
> > End If
> > Message = Message & vbCrLf
> > Set MyRecordset = MyRecordset.NextRecordset
> >Loop
> >Message = Message & "Recordset is Nothing."
> >MyConnection.Close
> >MsgBox Message
> >
> >
> >--
> >Hope this helps.
> >
> >Dan Guzman
> >SQL Server MVP
> >
> >
> >"Mike B" <BGates@.Microsoft.com> wrote in message
> >news:034801c3c671$449addf0$a101280a@.phx.gbl...
> >> In Visual Basic using ADO, I am trying to get print
> >> statements from SQL into my VB project. How does query
> >> analyzer get the print statements from the server? I am
> >> able to display the first TWO print statements using
> the
> >> [recordset].NextRecordset command, but I get an error
> if
> >> I try to go past the second recordset. Please help if
> >> you can.
> >>
> >> [CODE]
> >> Dim objerr As ADODB.Error
> >> On Error GoTo err_check
> >> Do
> >> For Each objerr In gcn.Errors
> >> GetSQLPrints = GetSQLPrints & vbCrLf &
> >>
> objerr.Description
> >> rs.NextRecordset
> >> Next
> >> Loop
> >> [/CODE]
> >> Only Prints two... I don't know why. Please help.
> >
> >
> >.
> >|||Thank you - your example worked great in VB as well.
Not sure where our problem was - went in circles for a
while...
>--Original Message--
>It looks like there are some differences in behavior
between VB and
>VBScript. On my system, the VB code below uses
Recordset.Open instead of
>Connection.Execute and returns the same result as the
original VBScript I
>posted.
>Dim SqlScript As String
>Dim MyConnection As New ADODB.Connection
>Dim MyRecordset As New ADODB.Recordset
>Dim recordsetnumber As Long
>Dim sqlerror As Error
>Dim message As String
>Dim ConnectionString As String
>Dim lastError As Integer
>ConnectionString = _
> "Provider=SQLOLEDB" & _
> ";Data Source=MyServer" & _
> ";Integrated Security=SSPI"
>MyConnection.Open ConnectionString
>SqlScript = _
> "SET NOCOUNT ON" & vbCrLf & _
> "PRINT 'test message 1'" & vbCrLf & _
> "PRINT 'test message 2'" & vbCrLf & _
> "SELECT 3" & vbCrLf & _
> "PRINT 'test message 4'" & vbCrLf & _
> "SELECT 5" & vbCrLf & _
> "PRINT 'test message 6'" & vbCrLf
>MyRecordset.Open SqlScript, MyConnection
>message = ""
>Do While Not MyRecordset.ActiveCommand Is Nothing
> recordsetnumber = recordsetnumber + 1
> message = message & "Recordset " & _
> recordsetnumber & ":" & vbCrLf
> If MyConnection.Errors.Count > 0 Then
> For Each sqlerror In MyConnection.Errors
> message = message & vbTab & "Message: " & _
> sqlerror.Description & vbCrLf
> Next
> Else
> message = message & vbTab & _
> "No messages." & vbCrLf
> End If
> If MyRecordset.State = adStateOpen Then
> message = message & vbTab & _
> "Rowset returned." & vbCrLf
> Else
> message = message & vbTab & _
> "No rowset returned." & vbCrLf
> End If
> message = message & vbCrLf
> On Error Resume Next
> Set MyRecordset = MyRecordset.NextRecordset
>Loop
>message = message & "Recordset is Nothing."
>MyConnection.Close
>MsgBox message
>
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>
>"Mike B/Steve Z" <szlamany@.antarescomputing.com> wrote
in message
>news:10e801c3c70e$22406160$a301280a@.phx.gbl...
>> For our connection, we are using SQLOLEDB...
>> When we run you code example, getting an error saying:
>> "Current Provider does not support returning multiple
>> recordsets from a single execution"
>> If we take the do-while loop out, we get a message box
>> saying:
>> Recordset 1:
>> Message: text message 1
>> No recordset returned
>> Recordset is nothing
>> We did add the following Dim's:
>> Dim SqlScript as string
>> dim Myrecordset as new adodb.recordset
>> dim recordsetnumber as long
>> dim sqlerror as error
>> dim message as string
>>
>> >--Original Message--
>> >It looks like you're executing a NextRecordset during
>> your Errors collection
>> >iteration. I suggest you complete the iteration
before
>> NextRecordset since
>> >multiple messages can be returned along with a
recordset.
>> >
>> >Below is a VBScript example that shows one method to
>> process multiple
>> >recordsets with messages. In VB, another method is to
>> handle the ADO
>> >Connection InfoMessage event and process the messages
in
>> your event handler.
>> >
>> >SqlScript = _
>> > "PRINT 'test message 1'" & vbCrLf & _
>> > "PRINT 'test message 2'" & vbCrLf & _
>> > "SELECT 3" & vbCrLf & _
>> > "PRINT 'test message 4'" & vbCrLf & _
>> > "SELECT 5" & vbCrLf & _
>> > "PRINT 'test message 6'" & vbCrLf
>> >
>> >Set MyRecordset = MyConnection.Execute(SqlScript)
>> >
>> >Message = ""
>> >Do While Not MyRecordset Is Nothing
>> > RecordsetNumber = RecordsetNumber + 1
>> > Message = Message & "Recordset " & _
>> > RecordsetNumber & ":" & VbCrLf
>> > If MyConnection.Errors.Count > 0 Then
>> > For Each SqlError In MyConnection.Errors
>> > Message = Message & vbTab & "Message: " &
_
>> > SqlError.Description & vbCrLf
>> > Next
>> > Else
>> > Message = Message & vbTab & _
>> > "No messages." & vbCrLf
>> > End If
>> > If MyRecordset.State = adStateOpen Then
>> > Message = Message & vbTab & _
>> > "Rowset returned." & vbCrLf
>> > Else
>> > Message = Message & vbTab & _
>> > "No rowset returned." & vbCrLf
>> > End If
>> > Message = Message & vbCrLf
>> > Set MyRecordset = MyRecordset.NextRecordset
>> >Loop
>> >Message = Message & "Recordset is Nothing."
>> >MyConnection.Close
>> >MsgBox Message
>> >
>> >
>> >--
>> >Hope this helps.
>> >
>> >Dan Guzman
>> >SQL Server MVP
>> >
>> >
>> >"Mike B" <BGates@.Microsoft.com> wrote in message
>> >news:034801c3c671$449addf0$a101280a@.phx.gbl...
>> >> In Visual Basic using ADO, I am trying to get print
>> >> statements from SQL into my VB project. How does
query
>> >> analyzer get the print statements from the server?
I am
>> >> able to display the first TWO print statements using
>> the
>> >> [recordset].NextRecordset command, but I get an
error
>> if
>> >> I try to go past the second recordset. Please help
if
>> >> you can.
>> >>
>> >> [CODE]
>> >> Dim objerr As ADODB.Error
>> >> On Error GoTo err_check
>> >> Do
>> >> For Each objerr In gcn.Errors
>> >> GetSQLPrints = GetSQLPrints & vbCrLf &
>> >>
>> objerr.Description
>> >> rs.NextRecordset
>> >> Next
>> >> Loop
>> >> [/CODE]
>> >> Only Prints two... I don't know why. Please help.
>> >
>> >
>> >.
>> >
>
>.
>