I have an application that needs to create invoices on a daily basis to
multiple clients based on orders shipped that day. This is easy to do on th
e
front-end. But how can I do this on the back end in SQL Server.
I want to sort orders by Client ID and put all orders belonging to one
customer on one invoice. When customer id changes, I change the Invoice ID.
Is this possible in SQL?
WRYou're already doing it the right way. Why does the database need to do the
iteration? Is sa or dbo going to be reviewing / paying the invoices?
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"WhiskyRomeo" <WhiskyRomeo@.discussions.microsoft.com> wrote in message
news:2D4504ED-3016-4D04-8D77-E9538284A2F0@.microsoft.com...
>I have an application that needs to create invoices on a daily basis to
> multiple clients based on orders shipped that day. This is easy to do on
> the
> front-end. But how can I do this on the back end in SQL Server.
> I want to sort orders by Client ID and put all orders belonging to one
> customer on one invoice. When customer id changes, I change the Invoice
> ID.
> Is this possible in SQL?
> WR
>|||This sounds like an INSERT to me. For example:
INSERT INTO Invoices (...)
FROM Orders
WHERE ...
GROUP BY client
INSERT INTO InvoiceItems (...)
FROM Orders
WHERE ...
Iteration shouldn't come into it.
If you need more help please post DDL and sample data (CREATE and
INSERT statements) and show your required end result.
David Portas
SQL Server MVP
--|||You're already doing it the right way. Why does the database need to do the
iteration? --> We need a way to assign invoice numbers to orders without
involving someone having to open the application and clicking on a button.
We want this part to be completely hands off.
Is sa or dbo going to be reviewing / paying the invoices? --> I have no
comment to this remark.
WR
"Aaron [SQL Server MVP]" wrote:
> You're already doing it the right way. Why does the database need to do t
he
> iteration? Is sa or dbo going to be reviewing / paying the invoices?
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "WhiskyRomeo" <WhiskyRomeo@.discussions.microsoft.com> wrote in message
> news:2D4504ED-3016-4D04-8D77-E9538284A2F0@.microsoft.com...
>
>|||> You're already doing it the right way. Why does the database need to do
> the
> iteration? --> We need a way to assign invoice numbers to orders without
> involving someone having to open the application and clicking on a button.
> We want this part to be completely hands off.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||For each client_id, i need to insert a record into the tblInvoice and update
all orders records with that Client Id to the Invoice_ID (identity column).
Here is the Invoice table ddl
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblCustomerOrder_tblInvoice]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblCustomerOrder] DROP CONSTRAINT
FK_tblCustomerOrder_tblInvoice
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblPayInvoice_tblInvoice]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblPayInvoice] DROP CONSTRAINT FK_tblPayInvoice_tblInvoice
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblInvoice]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblInvoice]
GO
CREATE TABLE [dbo].[tblInvoice] (
[Client_ID] [int] NOT NULL ,
[Invoice_ID] [int] IDENTITY (1, 1) NOT NULL ,
[InvoiceNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[GenDT] [smalldatetime] NOT NULL ,
[DueDT] [smalldatetime] NOT NULL ,
[AmtDue] [money] NOT NULL ,
[PaidFlag] [bit] NOT NULL
) ON [PRIMARY]
GO
Here is the CustomerOrder table ddl:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo]. [FK_tblCustomerOrderItem_tblCustomerOrde
r]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblCustomerOrderItem] DROP CONSTRAINT
FK_tblCustomerOrderItem_tblCustomerOrder
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblCustomerOrder]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblCustomerOrder]
GO
CREATE TABLE [dbo].[tblCustomerOrder] (
[OrderTrkNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BatchNo] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Client_ID] [int] NULL ,
[Invoice_ID] [int] NULL ,
[ClientCCSAcctNo] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipTrkNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClientOrderNo] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Customer_ID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderDate] [datetime] NULL ,
[NameFirst] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NameLast] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StateCD] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProdCnt] [tinyint] NULL ,
[ShipBoxCnt] [tinyint] NULL ,
[Shipper] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipService] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipFee] [money] NULL ,
[ShipWeight] [decimal](6, 2) NULL ,
[ShipDateEst] [smalldatetime] NULL ,
[ShipDate] [datetime] NULL ,
[ReplacedByOrder] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USPSZone] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADone] [bit] NULL ,
[AHold] [bit] NULL ,
[QAPass] [bit] NULL ,
[QAFail] [bit] NULL ,
[RDone] [bit] NULL ,
[RHold] [bit] NULL ,
[Ship] [bit] NULL ,
[ShipTrkNoFlg] [bit] NULL ,
[Inventoried] [bit] NULL
) ON [PRIMARY]
GO
"David Portas" wrote:
> This sounds like an INSERT to me. For example:
> INSERT INTO Invoices (...)
> FROM Orders
> WHERE ...
> GROUP BY client
> INSERT INTO InvoiceItems (...)
> FROM Orders
> WHERE ...
> Iteration shouldn't come into it.
> If you need more help please post DDL and sample data (CREATE and
> INSERT statements) and show your required end result.
> --
> David Portas
> SQL Server MVP
> --
>|||Where are the keys? I'll take a guess that ordertrkno is the key of the
Order table just because it's the only non-nullable column (Is that
sensible? What is the meaning of an order that has only an ordertrkno
but no other information at all? How can you have an order but not know
the date the order was placed or which customer it belongs to?, etc,
etc)
Here's my guess for the INSERT:
INSERT INTO tblInvoice
(client_id, invoiceno, gendt, duedt, amtdue, paidflag)
SELECT O.client_id, COUNT(*)+
(SELECT CAST(MAX(invoiceno) AS INTEGER) /* ? is char */
FROM tblInvoice),
O.orderdate,
DATEADD(DAY,30,O.orderdate),
(SELECT SUM(O.shipfee)
FROM tblCustomerOrder
WHERE client_id = O.client_id), 0
FROM tblCustomerOrder AS O
JOIN tblCustomerOrder AS P
ON O.ordertrkno >= P.ordertrkno
WHERE O.orderdate = '20050415' /* Today's orders */
AND P.orderdate = '20050415'
GROUP BY O.client_id, O.orderdate
The Invoice_ID in tblCustomerOrder is perhaps redundant but try this:
UPDATE tblCustomerOrder
SET invoice_id =
(SELECT invoice_id
FROM tblInvoice
WHERE client_id = tblCustomerOrder.client_id
AND gendt = tblCustomerOrder.orderdate)
WHERE orderdate = '20050415'
(Both untested)
Hope this helps.
David Portas
SQL Server MVP
--|||Oops. Let's try that INSERT a different way:
INSERT INTO tblInvoice
(client_id, invoiceno, gendt, duedt, amtdue, paidflag)
SELECT O.client_id,
(SELECT COUNT(DISTINCT client_id)
FROM tblCustomerOrder
WHERE orderdate = O.orderdate
AND client_id <= O.client_id)+
(SELECT CAST(MAX(invoiceno) AS INTEGER) /* ? is char */
FROM tblInvoice),
O.orderdate,
DATEADD(DAY,30,O.orderdate),
SUM(O.shipfee), 0
FROM tblCustomerOrder AS O
WHERE O.orderdate = '20050415' /* Today's orders */
GROUP BY O.client_id, O.orderdate
David Portas
SQL Server MVP
--|||The primary key of tblInvoice is Invoice_ID (identity) it has one-many
relationship with tblOrder. In the ddl version I gave you Client_ID in
tblInvoice was inappropriate.
In regard to tblCustomerOrder, you are correct, OrderTrkNo is the PK. Sorry
about the confusion with all the nullable columns. The order information
comes from several outside sources and we are just looking at the quality an
d
consisentency of this data before locking down what can be null and what
can't. But you are right Client_ID, OrderDate, FirstName, LastName,
Address1, City, State, Zip, Country, etc cannot be null.
Finally, I did not give you all the information to caculcate the total on
the invoice. There is actually a tblOrderItem a child of tblOrder, that
contains the Price and Quantity for each line item.
I just needed to be pointed in the right direction. I think this might do
it. I appreciate the time you took on this.
WR
"David Portas" wrote:
> Where are the keys? I'll take a guess that ordertrkno is the key of the
> Order table just because it's the only non-nullable column (Is that
> sensible? What is the meaning of an order that has only an ordertrkno
> but no other information at all? How can you have an order but not know
> the date the order was placed or which customer it belongs to?, etc,
> etc)
> Here's my guess for the INSERT:
> INSERT INTO tblInvoice
> (client_id, invoiceno, gendt, duedt, amtdue, paidflag)
> SELECT O.client_id, COUNT(*)+
> (SELECT CAST(MAX(invoiceno) AS INTEGER) /* ? is char */
> FROM tblInvoice),
> O.orderdate,
> DATEADD(DAY,30,O.orderdate),
> (SELECT SUM(O.shipfee)
> FROM tblCustomerOrder
> WHERE client_id = O.client_id), 0
> FROM tblCustomerOrder AS O
> JOIN tblCustomerOrder AS P
> ON O.ordertrkno >= P.ordertrkno
> WHERE O.orderdate = '20050415' /* Today's orders */
> AND P.orderdate = '20050415'
> GROUP BY O.client_id, O.orderdate
> The Invoice_ID in tblCustomerOrder is perhaps redundant but try this:
> UPDATE tblCustomerOrder
> SET invoice_id =
> (SELECT invoice_id
> FROM tblInvoice
> WHERE client_id = tblCustomerOrder.client_id
> AND gendt = tblCustomerOrder.orderdate)
> WHERE orderdate = '20050415'
> (Both untested)
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||I have really messed you up on the Orderdate. These orders are orders from
clients who have taken the order from the customer. The orderdate is their
order date.
We process all Orders from clients in Batches. The BatchDT is the really
our orderdate. This is a system that make plaques for photography studios.
The photographere has taken the picture and his customer placed an order. I
f
the order is a plaque, they send us the photo, engraving info, etc, basicall
y
most everything you see in tblCustomerOrder and what you didn't see in the
order item table. We process anywhere from 100 to a 1,000 plaques a day.
The way we will tell that an order is ready to be invoiced is the Invoice_ID
is null and the ShipDate = Today.
"David Portas" wrote:
> Oops. Let's try that INSERT a different way:
> INSERT INTO tblInvoice
> (client_id, invoiceno, gendt, duedt, amtdue, paidflag)
> SELECT O.client_id,
> (SELECT COUNT(DISTINCT client_id)
> FROM tblCustomerOrder
> WHERE orderdate = O.orderdate
> AND client_id <= O.client_id)+
> (SELECT CAST(MAX(invoiceno) AS INTEGER) /* ? is char */
> FROM tblInvoice),
> O.orderdate,
> DATEADD(DAY,30,O.orderdate),
> SUM(O.shipfee), 0
> FROM tblCustomerOrder AS O
> WHERE O.orderdate = '20050415' /* Today's orders */
> GROUP BY O.client_id, O.orderdate
> --
> David Portas
> SQL Server MVP
> --
>
Showing posts with label clients. Show all posts
Showing posts with label clients. Show all posts
Wednesday, March 28, 2012
Iteration in SQL
I have an application that needs to create invoices on a daily basis to multiple clients based on orders shipped that day. This is easy to do on the front-end. But how can I do this on the back end in SQL Server.
I want to sort orders by Client ID and put all orders belonging to one customer on one invoice. When customer id changes, I change the Invoice ID. Is this possible in SQL?
Xcog
Asked and answered in the microsoft.public.sqlserver.programming newsgroup.|||
Yes it is possible, install AdventureWorks in you development box in Enterprise manager click on stored procedures and you can get close to what you need. I would also check Northwind database but it was for mail order while AdventureWorks is for Ecommerce. Hope this helps.
http://www.microsoft.com/downloads/details.aspx?FamilyID=487C9C23-2356-436E-94A8-2BFB66F0ABDC&displaylang=en
sqlFriday, March 23, 2012
ISV using SQL Authentication and a small concern
Hi,
I am an independent software vendor and my app will be deployed on users machine, it will use msde for db needs on clients machine. I am using sql authentication and my app will be connecting to the database using a fixed username and password always, fo
r e.g. sa as user and "myPassword$2" as password. (I know I know that this is not 100% great, but we want to keep things very very simple but dont want windows authentication)
Now, please imagine this situation:
a) small office environment
b) My app installed on three machines.
c) database on 4th machine
d) all 3 users running my app on their individual machines and talking to db.
my concern is, will this scenario work, I mean no matter who the user is, my app logs into msde using sa and "myPassword$2", so will MSDE allow 3 users with same logins to access database at the same time.
Looking forward to hearing from you..
Thanks
Hello,
It is working correctly, but if you want to have some security (for example
a part of the program is not enabled if a user is working on another part,
you will have some problems.). For example, if user 1 has a crash, the
program will still believe that user 1 is still connected. Unless you lock
the tables...
Marc Allard
Allcomp
"dev" <anonymous@.discussions.microsoft.com> a crit dans le message de
news:3C66C1B4-CD85-4473-8B9C-9BF40E84853F@.microsoft.com...
> Hi,
> I am an independent software vendor and my app will be deployed on users
machine, it will use msde for db needs on clients machine. I am using sql
authentication and my app will be connecting to the database using a fixed
username and password always, for e.g. sa as user and "myPassword$2" as
password. (I know I know that this is not 100% great, but we want to keep
things very very simple but dont want windows authentication)
> Now, please imagine this situation:
> a) small office environment
> b) My app installed on three machines.
> c) database on 4th machine
> d) all 3 users running my app on their individual machines and talking to
db.
> my concern is, will this scenario work, I mean no matter who the user is,
my app logs into msde using sa and "myPassword$2", so will MSDE allow 3
users with same logins to access database at the same time.
> Looking forward to hearing from you..
> Thanks
|||Hello, (sorry I was in hollidays)
For example, in our program, when a user is working on a project (Project 1)
, any user can enter a new or a position in Project 1, but no other one
modify the project.
Another user can work with project 2 without any restriction so we can not
lock the full table. We had to write a column str_Wholocked on the Project
table to know that the a specific project is locked.
The problem is that if a user crash, he will not free the wholocked field so
you have to know if a user that has locked a fiel is still connected or not.
To make it, when a user connect in my application, I write his connectionID,
login_time... on a table. After that, I look at all the users connected. If
they have a login_time or connection ID different than the one on my table,
it mean that they have crashed so I can free the wholocked field. I make
this test for every connection and every access to something that can be
locked.
PS : I hope that I am clear enough, if not, please tell me what you don't
understand.
"dev" <anonymous@.discussions.microsoft.com> a crit dans le message de
news:765961A8-8196-4B6F-B87A-228DDB63F1F9@.microsoft.com...
> Allcomp please elaborate my what you mean by saying "but if you want to
have some security (for example
> a part of the program is not enabled if a user is working on another part,
you will have some problems.)."
> Thanks
sql
I am an independent software vendor and my app will be deployed on users machine, it will use msde for db needs on clients machine. I am using sql authentication and my app will be connecting to the database using a fixed username and password always, fo
r e.g. sa as user and "myPassword$2" as password. (I know I know that this is not 100% great, but we want to keep things very very simple but dont want windows authentication)
Now, please imagine this situation:
a) small office environment
b) My app installed on three machines.
c) database on 4th machine
d) all 3 users running my app on their individual machines and talking to db.
my concern is, will this scenario work, I mean no matter who the user is, my app logs into msde using sa and "myPassword$2", so will MSDE allow 3 users with same logins to access database at the same time.
Looking forward to hearing from you..
Thanks
Hello,
It is working correctly, but if you want to have some security (for example
a part of the program is not enabled if a user is working on another part,
you will have some problems.). For example, if user 1 has a crash, the
program will still believe that user 1 is still connected. Unless you lock
the tables...
Marc Allard
Allcomp
"dev" <anonymous@.discussions.microsoft.com> a crit dans le message de
news:3C66C1B4-CD85-4473-8B9C-9BF40E84853F@.microsoft.com...
> Hi,
> I am an independent software vendor and my app will be deployed on users
machine, it will use msde for db needs on clients machine. I am using sql
authentication and my app will be connecting to the database using a fixed
username and password always, for e.g. sa as user and "myPassword$2" as
password. (I know I know that this is not 100% great, but we want to keep
things very very simple but dont want windows authentication)
> Now, please imagine this situation:
> a) small office environment
> b) My app installed on three machines.
> c) database on 4th machine
> d) all 3 users running my app on their individual machines and talking to
db.
> my concern is, will this scenario work, I mean no matter who the user is,
my app logs into msde using sa and "myPassword$2", so will MSDE allow 3
users with same logins to access database at the same time.
> Looking forward to hearing from you..
> Thanks
|||Hello, (sorry I was in hollidays)
For example, in our program, when a user is working on a project (Project 1)
, any user can enter a new or a position in Project 1, but no other one
modify the project.
Another user can work with project 2 without any restriction so we can not
lock the full table. We had to write a column str_Wholocked on the Project
table to know that the a specific project is locked.
The problem is that if a user crash, he will not free the wholocked field so
you have to know if a user that has locked a fiel is still connected or not.
To make it, when a user connect in my application, I write his connectionID,
login_time... on a table. After that, I look at all the users connected. If
they have a login_time or connection ID different than the one on my table,
it mean that they have crashed so I can free the wholocked field. I make
this test for every connection and every access to something that can be
locked.
PS : I hope that I am clear enough, if not, please tell me what you don't
understand.
"dev" <anonymous@.discussions.microsoft.com> a crit dans le message de
news:765961A8-8196-4B6F-B87A-228DDB63F1F9@.microsoft.com...
> Allcomp please elaborate my what you mean by saying "but if you want to
have some security (for example
> a part of the program is not enabled if a user is working on another part,
you will have some problems.)."
> Thanks
sql
Monday, March 19, 2012
issues using MS Access with SQL Server 2005
I'm trying to help my clients configure MS Access to run queries against a
SQL Server 2005 on a hoste providers server.
I'm using Access 2002 (10.6771.6735) SP3. When I try and design a view I get
the following error:
"This version of Microsoft Access deosn't support design changes with the
version of MS SQL Server your Access project is connected to. Your design
changes will not be saved."
My client, using Access 2003 gets a similar message and cannot save the view
she designed.
Is there something we have to configure on the hosted server to allow use of
Access?
Thanks for any assistance with this.Dabbler wrote:
> I'm trying to help my clients configure MS Access to run queries against a
> SQL Server 2005 on a hoste providers server.
> I'm using Access 2002 (10.6771.6735) SP3. When I try and design a view I get
> the following error:
> "This version of Microsoft Access deosn't support design changes with the
> version of MS SQL Server your Access project is connected to. Your design
> changes will not be saved."
> My client, using Access 2003 gets a similar message and cannot save the view
> she designed.
> Is there something we have to configure on the hosted server to allow use of
> Access?
> Thanks for any assistance with this.
Access ADP doesn't support modifications to SQL Server 2005 objects.
The easiest way to modify views is to use SQL Server Management Studio.
It seems to be unclear at the moment what the future of ADP is:
http://www.databaseadvisors.com/gazette/sqlexpress.htm
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the lowdown on this. Not sure what MS is thinking with regard to
the primary market for Access end users, I don't think SQL Server Management
Studio is appropriate for end users.
"David Portas" wrote:
> Dabbler wrote:
> > I'm trying to help my clients configure MS Access to run queries against a
> > SQL Server 2005 on a hoste providers server.
> >
> > I'm using Access 2002 (10.6771.6735) SP3. When I try and design a view I get
> > the following error:
> > "This version of Microsoft Access deosn't support design changes with the
> > version of MS SQL Server your Access project is connected to. Your design
> > changes will not be saved."
> >
> > My client, using Access 2003 gets a similar message and cannot save the view
> > she designed.
> >
> > Is there something we have to configure on the hosted server to allow use of
> > Access?
> >
> > Thanks for any assistance with this.
> Access ADP doesn't support modifications to SQL Server 2005 objects.
> The easiest way to modify views is to use SQL Server Management Studio.
> It seems to be unclear at the moment what the future of ADP is:
> http://www.databaseadvisors.com/gazette/sqlexpress.htm
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Microsoft is really screwing up by not supporting SQLS2005 design work within
ADPs. So now I'm going to purchase the older SQLS2000 instead of 2005 to
accomplish what my users require? Seems counter-advancement. At least this
will save about $4000 with std. edition. As Dabbler says, SQLS Management
Studio is not appropriate for end users.
"Dabbler" wrote:
> Thanks for the lowdown on this. Not sure what MS is thinking with regard to
> the primary market for Access end users, I don't think SQL Server Management
> Studio is appropriate for end users.
> "David Portas" wrote:
> > Dabbler wrote:
> > > I'm trying to help my clients configure MS Access to run queries against a
> > > SQL Server 2005 on a hoste providers server.
> > >
> > > I'm using Access 2002 (10.6771.6735) SP3. When I try and design a view I get
> > > the following error:
> > > "This version of Microsoft Access deosn't support design changes with the
> > > version of MS SQL Server your Access project is connected to. Your design
> > > changes will not be saved."
> > >
> > > My client, using Access 2003 gets a similar message and cannot save the view
> > > she designed.
> > >
> > > Is there something we have to configure on the hosted server to allow use of
> > > Access?
> > >
> > > Thanks for any assistance with this.
> >
> > Access ADP doesn't support modifications to SQL Server 2005 objects.
> > The easiest way to modify views is to use SQL Server Management Studio.
> >
> > It seems to be unclear at the moment what the future of ADP is:
> > http://www.databaseadvisors.com/gazette/sqlexpress.htm
> >
> > --
> > David Portas, SQL Server MVP
> >
> > Whenever possible please post enough code to reproduce your problem.
> > Including CREATE TABLE and INSERT statements usually helps.
> > State what version of SQL Server you are using and specify the content
> > of any error messages.
> >
> > SQL Server Books Online:
> > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> > --
> >
> >
SQL Server 2005 on a hoste providers server.
I'm using Access 2002 (10.6771.6735) SP3. When I try and design a view I get
the following error:
"This version of Microsoft Access deosn't support design changes with the
version of MS SQL Server your Access project is connected to. Your design
changes will not be saved."
My client, using Access 2003 gets a similar message and cannot save the view
she designed.
Is there something we have to configure on the hosted server to allow use of
Access?
Thanks for any assistance with this.Dabbler wrote:
> I'm trying to help my clients configure MS Access to run queries against a
> SQL Server 2005 on a hoste providers server.
> I'm using Access 2002 (10.6771.6735) SP3. When I try and design a view I get
> the following error:
> "This version of Microsoft Access deosn't support design changes with the
> version of MS SQL Server your Access project is connected to. Your design
> changes will not be saved."
> My client, using Access 2003 gets a similar message and cannot save the view
> she designed.
> Is there something we have to configure on the hosted server to allow use of
> Access?
> Thanks for any assistance with this.
Access ADP doesn't support modifications to SQL Server 2005 objects.
The easiest way to modify views is to use SQL Server Management Studio.
It seems to be unclear at the moment what the future of ADP is:
http://www.databaseadvisors.com/gazette/sqlexpress.htm
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the lowdown on this. Not sure what MS is thinking with regard to
the primary market for Access end users, I don't think SQL Server Management
Studio is appropriate for end users.
"David Portas" wrote:
> Dabbler wrote:
> > I'm trying to help my clients configure MS Access to run queries against a
> > SQL Server 2005 on a hoste providers server.
> >
> > I'm using Access 2002 (10.6771.6735) SP3. When I try and design a view I get
> > the following error:
> > "This version of Microsoft Access deosn't support design changes with the
> > version of MS SQL Server your Access project is connected to. Your design
> > changes will not be saved."
> >
> > My client, using Access 2003 gets a similar message and cannot save the view
> > she designed.
> >
> > Is there something we have to configure on the hosted server to allow use of
> > Access?
> >
> > Thanks for any assistance with this.
> Access ADP doesn't support modifications to SQL Server 2005 objects.
> The easiest way to modify views is to use SQL Server Management Studio.
> It seems to be unclear at the moment what the future of ADP is:
> http://www.databaseadvisors.com/gazette/sqlexpress.htm
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Microsoft is really screwing up by not supporting SQLS2005 design work within
ADPs. So now I'm going to purchase the older SQLS2000 instead of 2005 to
accomplish what my users require? Seems counter-advancement. At least this
will save about $4000 with std. edition. As Dabbler says, SQLS Management
Studio is not appropriate for end users.
"Dabbler" wrote:
> Thanks for the lowdown on this. Not sure what MS is thinking with regard to
> the primary market for Access end users, I don't think SQL Server Management
> Studio is appropriate for end users.
> "David Portas" wrote:
> > Dabbler wrote:
> > > I'm trying to help my clients configure MS Access to run queries against a
> > > SQL Server 2005 on a hoste providers server.
> > >
> > > I'm using Access 2002 (10.6771.6735) SP3. When I try and design a view I get
> > > the following error:
> > > "This version of Microsoft Access deosn't support design changes with the
> > > version of MS SQL Server your Access project is connected to. Your design
> > > changes will not be saved."
> > >
> > > My client, using Access 2003 gets a similar message and cannot save the view
> > > she designed.
> > >
> > > Is there something we have to configure on the hosted server to allow use of
> > > Access?
> > >
> > > Thanks for any assistance with this.
> >
> > Access ADP doesn't support modifications to SQL Server 2005 objects.
> > The easiest way to modify views is to use SQL Server Management Studio.
> >
> > It seems to be unclear at the moment what the future of ADP is:
> > http://www.databaseadvisors.com/gazette/sqlexpress.htm
> >
> > --
> > David Portas, SQL Server MVP
> >
> > Whenever possible please post enough code to reproduce your problem.
> > Including CREATE TABLE and INSERT statements usually helps.
> > State what version of SQL Server you are using and specify the content
> > of any error messages.
> >
> > SQL Server Books Online:
> > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> > --
> >
> >
issues using MS Access with SQL Server 2005
I'm trying to help my clients configure MS Access to run queries against a
SQL Server 2005 on a hoste providers server.
I'm using Access 2002 (10.6771.6735) SP3. When I try and design a view I get
the following error:
"This version of Microsoft Access deosn't support design changes with the
version of MS SQL Server your Access project is connected to. Your design
changes will not be saved."
My client, using Access 2003 gets a similar message and cannot save the view
she designed.
Is there something we have to configure on the hosted server to allow use of
Access?
Thanks for any assistance with this.Dabbler wrote:
> I'm trying to help my clients configure MS Access to run queries against a
> SQL Server 2005 on a hoste providers server.
> I'm using Access 2002 (10.6771.6735) SP3. When I try and design a view I g
et
> the following error:
> "This version of Microsoft Access deosn't support design changes with the
> version of MS SQL Server your Access project is connected to. Your design
> changes will not be saved."
> My client, using Access 2003 gets a similar message and cannot save the vi
ew
> she designed.
> Is there something we have to configure on the hosted server to allow use
of
> Access?
> Thanks for any assistance with this.
Access ADP doesn't support modifications to SQL Server 2005 objects.
The easiest way to modify views is to use SQL Server Management Studio.
It seems to be unclear at the moment what the future of ADP is:
http://www.databaseadvisors.com/gazette/sqlexpress.htm
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the lowdown on this. Not sure what MS is thinking with regard to
the primary market for Access end users, I don't think SQL Server Management
Studio is appropriate for end users.
"David Portas" wrote:
> Dabbler wrote:
> Access ADP doesn't support modifications to SQL Server 2005 objects.
> The easiest way to modify views is to use SQL Server Management Studio.
> It seems to be unclear at the moment what the future of ADP is:
> http://www.databaseadvisors.com/gazette/sqlexpress.htm
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Microsoft is really screwing up by not supporting SQLS2005 design work withi
n
ADPs. So now I'm going to purchase the older SQLS2000 instead of 2005 to
accomplish what my users require? Seems counter-advancement. At least this
will save about $4000 with std. edition. As Dabbler says, SQLS Management
Studio is not appropriate for end users.
"Dabbler" wrote:
[vbcol=seagreen]
> Thanks for the lowdown on this. Not sure what MS is thinking with regard t
o
> the primary market for Access end users, I don't think SQL Server Manageme
nt
> Studio is appropriate for end users.
> "David Portas" wrote:
>
SQL Server 2005 on a hoste providers server.
I'm using Access 2002 (10.6771.6735) SP3. When I try and design a view I get
the following error:
"This version of Microsoft Access deosn't support design changes with the
version of MS SQL Server your Access project is connected to. Your design
changes will not be saved."
My client, using Access 2003 gets a similar message and cannot save the view
she designed.
Is there something we have to configure on the hosted server to allow use of
Access?
Thanks for any assistance with this.Dabbler wrote:
> I'm trying to help my clients configure MS Access to run queries against a
> SQL Server 2005 on a hoste providers server.
> I'm using Access 2002 (10.6771.6735) SP3. When I try and design a view I g
et
> the following error:
> "This version of Microsoft Access deosn't support design changes with the
> version of MS SQL Server your Access project is connected to. Your design
> changes will not be saved."
> My client, using Access 2003 gets a similar message and cannot save the vi
ew
> she designed.
> Is there something we have to configure on the hosted server to allow use
of
> Access?
> Thanks for any assistance with this.
Access ADP doesn't support modifications to SQL Server 2005 objects.
The easiest way to modify views is to use SQL Server Management Studio.
It seems to be unclear at the moment what the future of ADP is:
http://www.databaseadvisors.com/gazette/sqlexpress.htm
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the lowdown on this. Not sure what MS is thinking with regard to
the primary market for Access end users, I don't think SQL Server Management
Studio is appropriate for end users.
"David Portas" wrote:
> Dabbler wrote:
> Access ADP doesn't support modifications to SQL Server 2005 objects.
> The easiest way to modify views is to use SQL Server Management Studio.
> It seems to be unclear at the moment what the future of ADP is:
> http://www.databaseadvisors.com/gazette/sqlexpress.htm
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Microsoft is really screwing up by not supporting SQLS2005 design work withi
n
ADPs. So now I'm going to purchase the older SQLS2000 instead of 2005 to
accomplish what my users require? Seems counter-advancement. At least this
will save about $4000 with std. edition. As Dabbler says, SQLS Management
Studio is not appropriate for end users.
"Dabbler" wrote:
[vbcol=seagreen]
> Thanks for the lowdown on this. Not sure what MS is thinking with regard t
o
> the primary market for Access end users, I don't think SQL Server Manageme
nt
> Studio is appropriate for end users.
> "David Portas" wrote:
>
Wednesday, March 7, 2012
Issue with DTS from Sql25k
Hi everyone,
I understand that this is an issue. Microsofts doesn't inform anywhere
When you've got in your workstation both Sql2k and Sql25k clients and try to install the package for design dts 2000 from Sql25k then you'll not be able to re-start your Enterprise Manager from
MMC.
Appears this error:
?ProcessExecute@.@.YAXPAUHWND_@.@.PBG1@.Z dynamic link library procedure start point is not founded (SEMSFC.DLL)
Query Analyzer starts as usual, without problems but you lose your EM.
TIA
If you have both 2000 and 2005 client tools installed, you do not need the "SQL 2005 Compatibility DTS tools" installed. First, uninstall them. Then figure out what is causing your other error, that says you need to install them.My problem seems to have been the SQL 7 client tools were also installed, so I had 7, 2000 and 2005. This confused 2005. As soon as I uninstalled the SQL 7 client tools, the error went away and I was able to run the DTS designer in 2005.|||
Hi Tom,
Thanks for your response. I get the point but at least it could be reported.
|||What it really need to do is give you a valid error message, instead of "install DTS components" which you don't need to do. :) If it said "could not find xxxxx" or "error in .DLL" or something, then it wouldn't lead people, including myself, down the wrong path.
Subscribe to:
Posts (Atom)