IUSR_Machine account permissions in Report Builder

We have an ASP.NET application using Forms authentication which
launches the Report Builder via an HTML link with the href set to:
ReportServer/ReportBuilder/ReportBuilder.application
This will launch Report Builder and allow users to create, modify, and
save reports. But when the user tries to run the report, the above
error message comes up. Just for testing purposes, I tried adding the
IUSR account to the Administrators group but still with the same
result. It seems that everything we try still leads us to the
following error message:
The permissions granted to user '<domain>\IUSR_<machine>' are
insufficient for performing this operation. (rsAccessDenied)
After days of searching for a resolution to this I'm still unsure
about how to fix it. If anyone could give some advice or maybe provide
a link that explains how to resolve the situation we would be sincerely
grateful.
Thanks in advance!I'm encountering the same problem. Launching Report Builder from the
URL and authenticating the forms user, I can create a report and
save/deploy to the server but can't view it through Report Builder.
Also, Report Builder icon doesn't display in Report Manager when using
forms authentication for any user other than the default admin user.
I'll be writing an ASP.NET app to launch and deploy Report Builder but
it would be nice to have the icon in Report Manager
Chris wrote:
> We have an ASP.NET application using Forms authentication which
> launches the Report Builder via an HTML link with the href set to:
> ReportServer/ReportBuilder/ReportBuilder.application
> This will launch Report Builder and allow users to create, modify, and
> save reports. But when the user tries to run the report, the above
> error message comes up. Just for testing purposes, I tried adding the
> IUSR account to the Administrators group but still with the same
> result. It seems that everything we try still leads us to the
> following error message:
> The permissions granted to user '<domain>\IUSR_<machine>' are
> insufficient for performing this operation. (rsAccessDenied)
> After days of searching for a resolution to this I'm still unsure
> about how to fix it. If anyone could give some advice or maybe provide
> a link that explains how to resolve the situation we would be sincerely
> grateful.
> Thanks in advance!|||So have you had any luck with any kind of work-around? It's driving me
crazy and I have a client very anxious to start using it.
Chris
si.downes@.gmail.com wrote:
> I'm encountering the same problem. Launching Report Builder from the
> URL and authenticating the forms user, I can create a report and
> save/deploy to the server but can't view it through Report Builder.
> Also, Report Builder icon doesn't display in Report Manager when using
> forms authentication for any user other than the default admin user.
> I'll be writing an ASP.NET app to launch and deploy Report Builder but
> it would be nice to have the icon in Report Manager
> Chris wrote:
> > We have an ASP.NET application using Forms authentication which
> > launches the Report Builder via an HTML link with the href set to:
> >
> > ReportServer/ReportBuilder/ReportBuilder.application
> >
> > This will launch Report Builder and allow users to create, modify, and
> > save reports. But when the user tries to run the report, the above
> > error message comes up. Just for testing purposes, I tried adding the
> > IUSR account to the Administrators group but still with the same
> > result. It seems that everything we try still leads us to the
> > following error message:
> >
> > The permissions granted to user '<domain>\IUSR_<machine>' are
> > insufficient for performing this operation. (rsAccessDenied)
> >
> > After days of searching for a resolution to this I'm still unsure
> > about how to fix it. If anyone could give some advice or maybe provide
> > a link that explains how to resolve the situation we would be sincerely
> > grateful.
> >
> > Thanks in advance!|||Still no solution!!
Noticed in your error message the failure is for a domain user
<domain>\IUSR_.. mine is for the name of the forms user I have
created in the UserAccounts database. Under IIS I have reportBuilder
running with Anonymous Authentication as user IUSR_
It's starting to drive me mad too, this needs to be integrated into a
product for the end of the month.
Si
Chris wrote:
> So have you had any luck with any kind of work-around? It's driving me
> crazy and I have a client very anxious to start using it.
> Chris
>
> si.downes@.gmail.com wrote:
> > I'm encountering the same problem. Launching Report Builder from the
> > URL and authenticating the forms user, I can create a report and
> > save/deploy to the server but can't view it through Report Builder.
> >
> > Also, Report Builder icon doesn't display in Report Manager when using
> > forms authentication for any user other than the default admin user.
> > I'll be writing an ASP.NET app to launch and deploy Report Builder but
> > it would be nice to have the icon in Report Manager
> >
> > Chris wrote:
> > > We have an ASP.NET application using Forms authentication which
> > > launches the Report Builder via an HTML link with the href set to:
> > >
> > > ReportServer/ReportBuilder/ReportBuilder.application
> > >
> > > This will launch Report Builder and allow users to create, modify, and
> > > save reports. But when the user tries to run the report, the above
> > > error message comes up. Just for testing purposes, I tried adding the
> > > IUSR account to the Administrators group but still with the same
> > > result. It seems that everything we try still leads us to the
> > > following error message:
> > >
> > > The permissions granted to user '<domain>\IUSR_<machine>' are
> > > insufficient for performing this operation. (rsAccessDenied)
> > >
> > > After days of searching for a resolution to this I'm still unsure
> > > about how to fix it. If anyone could give some advice or maybe provide
> > > a link that explains how to resolve the situation we would be sincerely
> > > grateful.
> > >
> > > Thanks in advance!|||To create a user/group that can log into Report Manager and do
everything, create the user/group, then do the following:
1. Log into Report Manager as the admin user (or whatever name you gave
to the admin user).
2. Click "Site Settings" in the top-right corner
3. Under Security, click "Configure Item-level role definitions"
4. Name the Role and assign it to all of the tasks (click OK when done)
5. Click on "Site Settings" again
6. Under Security, click "Configure system-level role definitions"
7. Click "New Role"
8. Name the Role and assign it to all of the tasks (click OK when done)
9. Click "Site Settings"
10. Click "Configure Site-wide security"
11. If your user/group is already listed, click "Edit" next to it,
otherwise click "New Role Assignment" then type in your user/group name
on the next screen
12. Select the checkbox next to the system role you created in steps
6-8 (click Apply when done)
13. Click "Home" in the top-right corner
14. Click on the blue "Properties" tab
15. If your user/group is listed, click "Edit" next to it, otherwise
click "New Role Assignment" and type in your user/group name on the
next screen
16. Select the checkbox next to the role you created in steps 3-4
(click Apply when done).
17. Click Home. The end.
There are two different types of roles: item-level roles and
system-level roles. My guess would be that something was missing from
your list of 'tasks' in the item-level roles assigned to the
group/user. If you follow these instructions, you should end up with a
user/group that can do everything. This should help you determine if
it's a matter of permissions settings or something wonky with your
server setup. FYI, I would recommend against giving your users full
control over everything as described here.
I hope this helps.
Tim Larson

IUSER Security Risk?

We have an old ASP application that authenticates to a SQL Server 2000 database using the IUSER account.

We are exploring the possibility of moving it to a DMZ so users can access from home. The IUSER account is being used for all database activity for this application. Is there any reason to be concerned about using IUSER as opposed to sql authentication for this tool, or any other security risks I should be wary of. Mind you there may be some sensitive information contained in the database. Thanks in advance.

I would recommend either application roles or some other security method, such as a middle-tier or at least SQL Server security to access the database rather than this user. For one thing, you can't track actions to an individual this way.

This is a good reference for what you are trying to do:

http://www.windowsecurity.com/articles/Secure_Architecture_SQL_Web_Server.html

IUSER Security Risk?

We have an old ASP application that authenticates to a SQL Server 2000 database using the IUSER account.

We are exploring the possibility of moving it to a DMZ so users can access from home. The IUSER account is being used for all database activity for this application. Is there any reason to be concerned about using IUSER as opposed to sql authentication for this tool, or any other security risks I should be wary of. Mind you there may be some sensitive information contained in the database. Thanks in advance.

I would recommend either application roles or some other security method, such as a middle-tier or at least SQL Server security to access the database rather than this user. For one thing, you can't track actions to an individual this way.

This is a good reference for what you are trying to do:

http://www.windowsecurity.com/articles/Secure_Architecture_SQL_Web_Server.html

sql

It's baaack

I've been chasing a performance issue on and off for several ws. A stored
procedure in a custom application will run in under 30 seconds or it may tak
e
as long as 6 hours to run. I've got a stack of execution plans on this
procedure and sometimes the procedure uses a good plan complete with
parallelism and other times the optimizer decides to take the long way
through the data and use a serial plan and just crunch hundreds of millions
of records.
I got some help from this board several ws ago and learned about
parameter sniffing and found that local variables and defaults helped the
optimizer choose the good plan and incorporate parallelism. We thought we ha
d
it solved until last w, when the optimizer began choosing the bad plan
again. So, we reindexed, updated stats on key tables with full scans and tha
t
solved it.
It's now morphed again and doesn' respond to previous remedies. For whatever
reason, the optimizer insists on taking the long way. I really need to
rewrite this procedure but need a quick fix AND I'd like to understand what'
s
going on. The procedure includes views joined to views which include views
(it's ugly) as well as joins to base tables and I know the confusion is the
order in which tables get joined and processed. So, I reordered the join
syntax in the SP and used a force order option. Now I get the good plan agai
n
complete with parallelism... for now until it morphs again.
Do views prevent the optimizer from making the best use of statiscs? Why
doesn't the optimizer always use parallelism..the cost setting is set to 5-
default and I set it down to 1 and it still didn't use parallelism?
Thanks for any insight
MardyWhether the actual query uses parallelism is determined at run time each and
every time the query is run. This is based on several factors such as how
busy the cpu's are, the amount of available memory etc. A complex query
will usually generate a serial and parallel plan but that is only the
initial plan. At run time the engine decides which to run. So running a
query with a single proc or more than 1 proc can be different each time it
is run. With that said a very complex query with lots of views, joins etc
can be tough to crunch. There are times when the optimizer simply gives up
and says this plan will have to do even though there may be a more optimal
plan that can be generated. When you have lots of objects (tables, views,
indexes) to deal with the cost of trying out each and every possibility can
get too prohibitive. The more objects you have the choices become somewhat
exponential. I have seen very complex sp's take a long time to compile and
not even have the most optimal plan. The solution is to rewrite your
queries. Probably breaking it down into multiple individual sp's. That
way each sp can be optimized rather quickly and is usually easier to tune.
Hope that helps.
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:37BD8EB7-2457-4742-B250-EE33CCE3DEA7@.microsoft.com...
> I've been chasing a performance issue on and off for several ws. A
> stored
> procedure in a custom application will run in under 30 seconds or it may
> take
> as long as 6 hours to run. I've got a stack of execution plans on this
> procedure and sometimes the procedure uses a good plan complete with
> parallelism and other times the optimizer decides to take the long way
> through the data and use a serial plan and just crunch hundreds of
> millions
> of records.
> I got some help from this board several ws ago and learned about
> parameter sniffing and found that local variables and defaults helped the
> optimizer choose the good plan and incorporate parallelism. We thought we
> had
> it solved until last w, when the optimizer began choosing the bad plan
> again. So, we reindexed, updated stats on key tables with full scans and
> that
> solved it.
> It's now morphed again and doesn' respond to previous remedies. For
> whatever
> reason, the optimizer insists on taking the long way. I really need to
> rewrite this procedure but need a quick fix AND I'd like to understand
> what's
> going on. The procedure includes views joined to views which include views
> (it's ugly) as well as joins to base tables and I know the confusion is
> the
> order in which tables get joined and processed. So, I reordered the join
> syntax in the SP and used a force order option. Now I get the good plan
> again
> complete with parallelism... for now until it morphs again.
> Do views prevent the optimizer from making the best use of statiscs? Why
> doesn't the optimizer always use parallelism..the cost setting is set to
> 5-
> default and I set it down to 1 and it still didn't use parallelism?
> Thanks for any insight
> Mardy|||If the query contains a lot of sub-queries, then consider inserting the
intermediate results into temporary tables.
Queries of the following style can trip up the optimizer at runtime:
select ... from ... where x in (select ... from ...)
Also, calling UDFs can impede the optimizer.
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:37BD8EB7-2457-4742-B250-EE33CCE3DEA7@.microsoft.com...
> I've been chasing a performance issue on and off for several ws. A
stored
> procedure in a custom application will run in under 30 seconds or it may
take
> as long as 6 hours to run. I've got a stack of execution plans on this
> procedure and sometimes the procedure uses a good plan complete with
> parallelism and other times the optimizer decides to take the long way
> through the data and use a serial plan and just crunch hundreds of
millions
> of records.
> I got some help from this board several ws ago and learned about
> parameter sniffing and found that local variables and defaults helped the
> optimizer choose the good plan and incorporate parallelism. We thought we
had
> it solved until last w, when the optimizer began choosing the bad plan
> again. So, we reindexed, updated stats on key tables with full scans and
that
> solved it.
> It's now morphed again and doesn' respond to previous remedies. For
whatever
> reason, the optimizer insists on taking the long way. I really need to
> rewrite this procedure but need a quick fix AND I'd like to understand
what's
> going on. The procedure includes views joined to views which include views
> (it's ugly) as well as joins to base tables and I know the confusion is
the
> order in which tables get joined and processed. So, I reordered the join
> syntax in the SP and used a force order option. Now I get the good plan
again
> complete with parallelism... for now until it morphs again.
> Do views prevent the optimizer from making the best use of statiscs? Why
> doesn't the optimizer always use parallelism..the cost setting is set to
5-
> default and I set it down to 1 and it still didn't use parallelism?
> Thanks for any insight
> Mardy

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 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
> --
>

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

sql

Iterating

Hi,

I have a 6 different textboxes in my web application. I have 6 different tables in my database such as tbl1,tbl2,tbl3 etc.

When the user clicks the submit button I have to check whether the values in the textboxes match the value in the database. (if in txt1 the user enters 3 I need to go to tbl1 and check if there is such a value).

What is the most efficient way to perform such a check? Will I need to write 6 select statements or can I use a loop and if I can use a loop I would appreciate an example

Thanks

Why do you need to do this?

If you are loading the value into the textbox to begin with, and you simply want to check whether the text has changed, there is a text changed event raised by the textbox. If that doesn't work for you, you could always store the initial value of the textbox in a custom property when you populate the textbox:

myTextBox1.Attributes.Add('initValue',myValue)

Then simply check to see whether it has changed.

If you MUST check these against values from the database (i.e.; for data volatility reasons), you should retrieve all six from the db in a single SQL query, if possible. It would be more efficient...

|||

I need to do this coz the user enters a code in the textboxes and I need to check if such a code exists in the db and if there is no such a code then the user must select another code.

How can I retrieve all 6 values in a single query from 6 different tables?

ISSUES. . .

I'm a developer for a rich client application with a primary grid that should be refreshed when data is changed by other users. I loath to resort to some sort of polling. It would be really cool if there is a native way to raise an event on the client from a SQL Server trigger. RAISEERROR can send a message to the Connection's InfoMessage() event. But of course this will only send the message back to the user who made the change. Is there anyway in SQL Server to raise an error message on another process so that this ADO event can pick it up?

This is not possible without some elaborate engineering in SQL Server 2005 (even more so in SQL Server 2000). With SQL Server 2005, you can use service broker to do this. It is hard to tell without knowing more details but I suggest that you take a look at the new features in SQL Server 2005 and see if those address your needs.|||You could always use firebird/interbase. probably on of the best rdbms's around.

Other that that (hypotheticals follow)

in SQL 2000 ->
You could put a com+ object in DTS. Trigger on a data-change notifies the com+ object. COM+ object has list of registered clients that it notifies of the changes.

SQL 2005 it would be even easier with the integration of .NET

wouldn't that work?

Oh dont make me do this in VBSUX because VB, well vb simply sux - how did it become so successful?! I dont want to do it in VB.NET as I want to cater the lowest common denominator. And boy is VB the lowest denominator!

Simple to do in delphi! but give me an hour. . . I'll do it in VB
God delphi rox!|||Thanks for your help. I'm not suprised that there isn't anything. However I'm disapointed SQL Server 2005 doesn't have it. SQL Server has all the capabilities as a simply event conduit between it's clients. Kind of a waste then that some other messaging service is required for simple data related events to be passed around.
For an alternative I have an idea to referesh based on user activty. A few events will call a function and refresh if some criteria has been met. Eg the form is activated after five minutes.
|||

well trying to do it using vbsux further ingrained my disdain for the piece of sh|t that vb6 is. Its not hard to do in delphi. It is Actually rather simple.

About 20 lines of actual code you need to write. . .

Create an activeX exe (DBNotifier) that defines an object (NotifyClient) that implements the interface

INotifyClient
{
Notify()
}
with an event OnNotify

another singleton object (Manager) implements the interface -

IManager
{
void RegisterClient(INotifyClient client);
void UnRegisterClient(INotifyClient client);
void NotifyClients()
}
During the call to NotifyClients you need to synchronize access to a collection it contains that will hold references to the clients.

Finally an object that (NotifyAgent) implements
INotifyAgent
{
void Notify(long ID)
}
NotifyAgent calls the Managers.NotifyClients Method

In the GUI app, get a reference to the IManager Object, Instance An INotifyClient and register it. Define an OnNotifyEvent to do what ever you want.
Now in the database (I will use northwind for example) attach a trigger of this sort:
=============================

create trigger trig_employeesDataChange on northwind.dbo.employees for DELETE,INSERT,UPDATE
as
DECLARE @.object int
DECLARE @.hr int
DECLARE @.obj_ID int
DECLARE @.src varchar(255), @.desc varchar(255)
select @.obj_ID = ID from sysobjects o inner join
sysusers u on o.uid = u.uid
where
u.name ='dbo' and
o.name ='employees'

EXEC @.hr = sp_OACreate 'DBNotifier.Agent', @.object OUT
IF @.hr = 0
EXEC @.hr = sp_OAMethod @.object, 'Notify', NULL, @.obj_id
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
=============================

Again. . . dont try in VBSUX, its not worth the effort use a real development environment. Why you might ask? The synchronization is impossible. VB is just a load of cr@.p (have they yet shot the guys who developed it? shoot their mothers too!!!)

Now. . . why is this not implemented natively? Well, because you aren't supposed to remain connected to the database. Its bad design!

Connect, get your data and get out!!!!
Connect, change your data and get out!!!!

|||You can do it using service broker and query notifications mechanisms in SQL Server 2005. It depends on your requirements. As I said, you should check out those features in Books Online.|||

Boy, I could complicate a wet dream!

Its not difficult at all, provided you use Delphi as you can't do it with VBSUX alone because VBSUX is a piece of cr@.p and cant create COM+ event objects. . . Have those guys been shot yet?

Total time of implementation 10 minutes depending on how many SQL objects you want sending notifications!!!

Three parts. . .

● Build, register and install a COM+ Event Object - SQLEvents (this is not hard but you cannot do it in VBSUX!!! Use Delphi!)
● Add triggers to your SQL objects that should initiate the SQLEvents
● Create an ActiveX Event Sink for handling the COM+ Events - SQLEventSink (This can be done in VBSUX!!!)
If you don't have delphi, go home - you suck!

[Part One - Looks like alot, but it takes all of 3 minutes!!!]
1. create a Delphi Active X library, Call it SQLEvents.
2. From the file menu - > Add >Other -> ActiveX -> Automation Object call it SQLEvent with Apartment Threading (no events)
3. From view Menu -> Type Library. . . In the the TypeLibrary editor, add a method to the ISQLEvent interface called Notify that takes a long parameter called SQLObject [see here]
4. Generate the code and call the generated pas file SQLEvents_impl.pas; you dont need to write ANY code!!!!!
5. From the Run Menu -> Register Active X Server (this also builds the DLL)

On the SQL Server machine:
6. Open Component Services and drill down to COM+ Applications. Right Click and select New -> Application - Next -> Empty Application Call it 'SQLEvents' and set Server Application as your activation type -> Applciation Identity Interactive User -> use the default application roles -> dont add any roles -> finish

7. Expand the SQLEvents Aplication folder to the Components folder and right click and select New -> Component -> Next -> Click Install New Event Class(es) and locate and open the SQLEvents.dll you built in step 5. -Next -> Finish

The event object is done. and the tree should look like this when expanded.

[Part 2]
8. Add triggers to the SQL Objects that need to send notifications -

NOTES: My build has a CLSID of "{8FD50E86-203D-4939-9CAE-0F2865C69465}" yours will be different! You can find it out by right clicking the Component installed in step 7 and selecting properties.
Also. this example uses northwind and will trigger events on Update Delete and Insert on the employees table:
=====================================================
CREATE trigger trig_employeesDataChange on northwind.dbo.employees for DELETE,INSERT,UPDATE
as
DECLARE @.object int
DECLARE @.hr int
DECLARE @.obj_ID int
DECLARE @.src varchar(255), @.desc varchar(255)
select @.obj_ID = ID from sysobjects o inner join
sysusers u on o.uid = u.uid
where
u.name ='dbo' and
o.name ='employees'

EXEC @.hr = sp_OACreate '{8FD50E86-203D-4939-9CAE-0F2865C69465}', @.object OUT
IF @.hr = 0
EXEC @.hr = sp_OAMethod @.object, 'Notify', NULL, @.obj_id
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
=====================================================

Part 3 [you can use VB here, total time 2 minutes!!!]

9. Create a new VB ActiveX DLL Project, save it as SQLEventsSink, Rename Class1 to SQLEventsSink and save the file as SQLEventsSink.cls.

10. From the Projects menu Add a reference to the SQLEvents Lbrary you built in Step 5 above.

11. Add this code to SQLEventsSink:
=================================
Option Explicit
Implements SQLEvents.SQLEvent

Private mSQLObjectID As Long 'local copy
Public Event OnNotify()

Public Property Let SQLObjectID(ByVal vData As Long)
mSQLObjectID = vData
End Property

Public Property Get SQLObjectID() As Long
SQLObjectID = mSQLObjectID
End Property

Private Sub SQLEvent_Notify(ByVal SQLObjectID As Long)
If SQLObjectID = Me.SQLObjectID Then RaiseEvent OnNotify
End Sub
=================================

12. Build the Library and you are done. . . you have an event system!!!

Here's how to use ->

1. Create A New VB Application Project Call it TestApp
2. From the Project Menu, add references to MS Active Data Object 2.6 (minimum), COM+ Admin Library and the SQLEventSink library you built in step 12 above. In the Toolbox add the MS DataGrid 6.0
3. Add a Module, name it globals and add the following code
==============================================
Option Explicit

Public Const CONNECTIONSTRING = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=False;" & _
"Initial Catalog=Northwind;Data Source=.\SQL2000"

' NOTE: CHANGE THE FOLLOWING TO REFLECT THE CLSID
' OF THE SQLEvents.SQLEvent OBJECT YOU PREVIOUSLY
' BUILT AND INSTALLED IN COM+

Public Const CLSID_SQLEVENT = "{8FD50E86-203D-4939-9CAE-0F2865C69465}"
==============================================

4. Add a Module, name it ComUtils and add the following code (funny, in delphi it only takes about 6 lines of code to accomplish the same!!! Have I mentioned that VBSUX, sucks?!?)
==============================================
' This method creates a Transient Subscription to a COM+ Component
' Refer to The Windows Platform SDK and Particularly ICOMAdminCatalog
' clsID is the COM+ Event Component to which you are subscribing
' objref is the subscriber
' hostname is the machine on which the COM+ Event Component is registered
' If empty, connection stays on local machine

Public Function CreateTransientSubscription( _
ByVal clsid As String, _
ByVal objref As Object, _
Optional ByVal hostName As String = "") As String
Dim oCOMAdminCatalog As COMAdmin.COMAdminCatalog
Dim oTSCol As COMAdminCatalogCollection
Dim oSubscription As ICatalogObject
Dim objvar As Variant
On Error GoTo CreateTransientSubscriptionError
Set oCOMAdminCatalog = CreateObject("COMAdmin.COMAdminCatalog")
'Connect to the
If hostName <> "" Then oCOMAdminCatalog.Connect hostName
'Gets the TransientSubscriptions collection
Set oTSCol = oCOMAdminCatalog.GetCollection( _
"TransientSubscriptions")
Set oSubscription = oTSCol.Add
Set objvar = objref
oSubscription.Value("SubscriberInterface") = objref
oSubscription.Value("EventCLSID") = clsid
oSubscription.Value("Name") = "TransientSubscription"
oTSCol.SaveChanges
CreateTransientSubscription = oSubscription.Value("ID")
Set oSubscription = Nothing
Set oTSCol = Nothing
Set oCOMAdminCatalog = Nothing
Set objvar = Nothing
Exit Function
CreateTransientSubscriptionError:
CreateTransientSubscription = ""
Err.Raise Err.Number, "[CreateTransientSubscription]" & _
Err.Source, Err.Description
End Function
==============================================

5. Drop a DataGrid control on your form leaving the properties as their defaults. Add a Timer to the form as you need it because VBSUX does not natively support multi-threading. . . Have I told you how much VBSUX sucks? It really does! I wouldn't lie to you!

6. In the Form1 code add the following:
=========================================

Option Explicit
Private WithEvents mSink As SQLEventSink

Private Sub Form_Load()
Set mSink = New SQLEventSink
CreateTransientSubscription _
"{8FD50E86-203D-4939-9CAE-0F2865C69465}", _
mSink
LoadGrid
End Sub

Private Sub mSink_OnNotify()
Timer1.Enabled = True
End Sub

Private Sub Timer1_Timer()
Timer1.Enabled = False
LoadGrid
End Sub

Private Sub LoadGrid()
Dim con As Connection
Set con = New ADODB.Connection
con.Open CONNECTIONSTRING
If DataGrid1.DataSource Is Nothing Then
With con.Execute("SELECT ID FROM SYSOBJECTS O " & _
" INNER JOIN SYSUSERS U ON O.UID = U.UID " & _
" WHERE U.NAME = 'DBO' and O.NAME = 'EMPLOYEES'")
mSink.SQLObjectID = .Fields(0)
.Close
End With
End If
Dim rst As Recordset
Set rst = New Recordset
rst.CursorLocation = adUseClient
Set rst.ActiveConnection = con
rst.Open "SELECT * FROM EMPLOYEES"
Set rst.ActiveConnection = Nothing
con.Close
Set DataGrid1.DataSource = rst
Set con = Nothing
Set rst = Nothing
End Sub
=========================================

7. Run the app -
if your Northwind has not been changed, the name for employeeID 7 should be: King, Robert.

In SQL Query Analyzer, execute:

update employees set FirstName = 'Stephen' where EmployeeID = 7

and "voila!!!" Stephen King automatically appears in your datagrid!!!

References:
ICOMAdminCatalog
Registering a Transient Subscription

Complete Source Code can be found here. Zip also contains a compiled SQLEvents.dll,, just in case you don't have delphi and are still hanging around!

|||1. Don't implement the sink in VBSUX as the VBSUX com object is unstable.
No problems with a sink implemented in delphi. Have I told you that VBSUX is a total piece of CR@.P?

2. You need to enter a critical immediately upon entering the handler. After entering the critical section, null the Sink reference. Reinitialize the sink right after make changes inside the thread that does the response to the notification. So Don't Implement a Sink Client in VBSUX because Critical sections in VBSUX are a total pain in the @.SS - and threads in VBSUX are even worse!!! Have I told you that VBSUX is a PIECE OF CR@.P?

3. Best perfromance is not kicking the COM+ event off in the sql server but in the applciation that makes the change to the database. Immediately after making a change you want to publish, instance a COM+ Event and call Notify.

On a 2.6 P4 H/T w 775mb mem, I had 22 publishers notifying 30 subscribers. . .slow but no blow-ups.

Issues with Report Manager on both a web server and the report server?

My reports have been run off of a combined IIS/SQL server. I have a new application, which is using the SQL server, but runs on its own web server. The developer would like to deploy his reports to the new IIS server. Are there any issues with just installing a copy of the Report Manager on the new IIS server and putting reports there while continuing to run the original application on the combined server?

TIA

Daniel

Hi,

From your description, it seems that you want to deploy your reporting service on other server than the server with SQLServer, right?

I'm afraid you have to install components for SQLServer reporting service on that new IIS. Only n this way, you can re-configure the settings in SQL Server Reporting Services Configuration, including Web Service Identity, report server and report manager virtual directory, also the new database server's information. After that , you can run your reporting service with other database servers.

For more information on each components of Reporting service, see:

http://msdn2.microsoft.com/en-us/library/ms155792.aspx

Thanks.

|||

Michael,

I want to deploy report manager on two servers, the SQL server itself and on another server running just as a web server. I have not found a definitive answer, but based on what I have found I am thinking you can only have one report manager set up.

Regards,

Daniel

|||

Hi,

the SQL server itself and on another server running just as a web server.

No matter how many machines you want to run your Report Manager, you should install the SQLServer client component to enable the SQL Report manager. (connecting a remote database).

sql

Issues with MSDE after installing WMSDE, WSUS

I have a stable 3rd party application which has been running on MSDE Sp3 (ver
2000.80.760.0) as the default instance. I installed WSUS, which apparently
uses WMSDE (version 2000.80.884.0), as a named instance. After this
installation I noticed that the original SQL server has a memory leak,
consuming the resources of the server over a few hours.
I upgraded (replaced) the Sp3 default instance with Sp4 (version
2000.80.2039.0). Howeveer the problem seems to be continuing. Is it wise to
have these 2 different database versions running on the same machine or
should I separate/amalgamate them?
TIA
hi,
Lance wrote:
> I have a stable 3rd party application which has been running on MSDE
> Sp3 (ver 2000.80.760.0) as the default instance. I installed WSUS,
> which apparently uses WMSDE (version 2000.80.884.0), as a named
> instance. After this installation I noticed that the original SQL
> server has a memory leak, consuming the resources of the server over
> a few hours.
> I upgraded (replaced) the Sp3 default instance with Sp4 (version
> 2000.80.2039.0). Howeveer the problem seems to be continuing. Is it
> wise to have these 2 different database versions running on the same
> machine or should I separate/amalgamate them?
> TIA
WMSDE is a modified version of MSDE that enables db size larger then 2gb per
database, that is one of the constraints/limitations of "standard" MSDE, and
is provided for SharePoint, AFAIK..
unfortunately no sp4 for this edition has still been provided, even if it
has been promised...
Andrea Montanari
http://www.asql.biz/DbaMgr.shtm
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

Issues with creating SQL Server Jobs

Hi,
My application creates 4-5 SQL Server jobs on the production server. I just
want to know what is the impact of creating larger number of jobs on
production server.
Will it hamper it performance, except that SQL Server Agent service should
be running?
Thanks in advance,
PushkarI don't think SQL Jobs use significant resourcesl unless they are
running. The issue would be how often are your jobs scheduled to run?
What do they do?|||I don't think SQL jobs use significant resources unless they are
runnng. The question would be how often are they scheduled to run? What
do they do?|||Maybe, are you thinking in terms of refresh or something like that?
sysjobs,sysjobsteps,sysjobhistory... Do you get the point?
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Jon" wrote:

> I don't think SQL jobs use significant resources unless they are
> runnng. The question would be how often are they scheduled to run? What
> do they do?
>

Issues with creating SQL Server Jobs

Hi,
My application creates 4-5 SQL Server jobs on the production server. I just
want to know what is the impact of creating larger number of jobs on
production server.
Will it hamper it performance, except that SQL Server Agent service should
be running?
Thanks in advance,
Pushkar
I don't think SQL Jobs use significant resourcesl unless they are
running. The issue would be how often are your jobs scheduled to run?
What do they do?
|||I don't think SQL jobs use significant resources unless they are
runnng. The question would be how often are they scheduled to run? What
do they do?
|||Maybe, are you thinking in terms of refresh or something like that?
sysjobs,sysjobsteps,sysjobhistory... Do you get the point?
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Jon" wrote:

> I don't think SQL jobs use significant resources unless they are
> runnng. The question would be how often are they scheduled to run? What
> do they do?
>

Issues with creating SQL Server Jobs

Hi,
My application creates 4-5 SQL Server jobs on the production server. I just
want to know what is the impact of creating larger number of jobs on
production server.
Will it hamper it performance, except that SQL Server Agent service should
be running?
Thanks in advance,
PushkarI don't think SQL Jobs use significant resourcesl unless they are
running. The issue would be how often are your jobs scheduled to run?
What do they do?|||I don't think SQL jobs use significant resources unless they are
runnng. The question would be how often are they scheduled to run? What
do they do?|||Maybe, are you thinking in terms of refresh or something like that?
sysjobs,sysjobsteps,sysjobhistory... Do you get the point?
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Jon" wrote:

> I don't think SQL jobs use significant resources unless they are
> runnng. The question would be how often are they scheduled to run? What
> do they do?
>

Issues with creating SQL Server Jobs

Hi,
My application creates 4-5 SQL Server jobs on the production server. I just
want to know what is the impact of creating larger number of jobs on
production server.
Will it hamper it performance, except that SQL Server Agent service should
be running?
Thanks in advance,
PushkarI don't think SQL Jobs use significant resourcesl unless they are
running. The issue would be how often are your jobs scheduled to run?
What do they do?|||I don't think SQL jobs use significant resources unless they are
runnng. The question would be how often are they scheduled to run? What
do they do?|||Maybe, are you thinking in terms of refresh or something like that?
sysjobs,sysjobsteps,sysjobhistory... Do you get the point?
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Jon" wrote:
> I don't think SQL jobs use significant resources unless they are
> runnng. The question would be how often are they scheduled to run? What
> do they do?
>

Issues with 1.1 framework calling SQL 2005 CLR sprocs?

Are there any issues calling SQL 2005 CLR bases stored procedures and functions from a web application which uses the dotnet 1.1 framework?

I assume not, but would like advice from those who've been there...

Thanks,

Marie

Hi,

no there are nor problems. Even calling SP from other program with the MDAC stack does not bring any problems. You will not be able to use certain features as they are only supportedby the ADO.NET 2.0 framework (like query notification) but there any no problems with the "old" functionality.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Thanks Jens!

Issues with 1.1 framework calling SQL 2005 CLR sprocs?

Are there any issues calling SQL 2005 CLR bases stored procedures and functions from a web application which uses the dotnet 1.1 framework?

I assume not, but would like advice from those who've been there...

Thanks,

Marie

Hi,

no there are nor problems. Even calling SP from other program with the MDAC stack does not bring any problems. You will not be able to use certain features as they are only supportedby the ADO.NET 2.0 framework (like query notification) but there any no problems with the "old" functionality.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Thanks Jens!

Issue with VB6 ADO on vista

Hi,

I am having a vb6 application with sql server 2005 database. It works fine on windows xp. But when i install the application on windows vista I am getting Adodc error "login failed for user sa". Still the forms are populated with data from the database. But the event handlers dosent seem to work after i get this error. Does someone know what caused that adodc error. I have disabled User account control for the vista installation.

Vinith

Hi Vinith.

I'm not sure what Adodc but could you please report the complete error message and the associated error message logged to the server's error log. Please take a look at this webpage for more info: http://msdn2.microsoft.com/en-us/library/ms366351.aspx

Thanks,

Il-Sung.

|||

There was a change in Vista to be more secure in terms of credental propagation. Without more details it is hard to say if this is the case for you.

Try adding

Persist Security Info=True

to your connection string and see if your application is affected by that change.|||

Hi Il Sung and Anton,

I have given persist security info=true and it seems to have solved my problem.

I have kept my connection in an ADOCD connection object. I was using that object when ever i needed database connection. The password was not getting persisted in the connection object. ie before executing if i checked ADODC.connectionstring it didnt contain password information. And that caused the message login failed for user sa.

However this problem occured only when the application runs on vista. With XP it worked fine. Anyway by giving Persist Security Info = true in vista connection string the problem is solved and the password is persisted.

Thanks a lot

Vinith

Issue with VB6 ADO on vista

Hi,

I am having a vb6 application with sql server 2005 database. It works fine on windows xp. But when i install the application on windows vista I am getting Adodc error "login failed for user sa". Still the forms are populated with data from the database. But the event handlers dosent seem to work after i get this error. Does someone know what caused that adodc error. I have disabled User account control for the vista installation.

Vinith

Hi Vinith.

I'm not sure what Adodc but could you please report the complete error message and the associated error message logged to the server's error log. Please take a look at this webpage for more info: http://msdn2.microsoft.com/en-us/library/ms366351.aspx

Thanks,

Il-Sung.

|||

There was a change in Vista to be more secure in terms of credental propagation. Without more details it is hard to say if this is the case for you.

Try adding

Persist Security Info=True

to your connection string and see if your application is affected by that change.|||

Hi Il Sung and Anton,

I have given persist security info=true and it seems to have solved my problem.

I have kept my connection in an ADOCD connection object. I was using that object when ever i needed database connection. The password was not getting persisted in the connection object. ie before executing if i checked ADODC.connectionstring it didnt contain password information. And that caused the message login failed for user sa.

However this problem occured only when the application runs on vista. With XP it worked fine. Anyway by giving Persist Security Info = true in vista connection string the problem is solved and the password is persisted.

Thanks a lot

Vinith

Issue with running reports on machine that does not have report services

I am trying to run reports from a web application. If I am on the machine
with reporting services then the report will pull up. If I try from a PC
that doesn't have reporting services I get a "Page cannot be displayed"
error. I have checked permissions on most everything I could think of.
Please help. I have been researching and trying to resolve this for over
two weeks. Thanks!
--
Message posted via http://www.sqlmonster.comDo you mean you can't get to http://servername/reports from a remote
machine? Or are you trying to embed a report viewer control in an asp.net
app that's on a different machine?
Mike G.
"JC via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f491c9d8986847cbbd8e3872e85aabca@.SQLMonster.com...
>I am trying to run reports from a web application. If I am on the machine
> with reporting services then the report will pull up. If I try from a PC
> that doesn't have reporting services I get a "Page cannot be displayed"
> error. I have checked permissions on most everything I could think of.
> Please help. I have been researching and trying to resolve this for over
> two weeks. Thanks!
> --
> Message posted via http://www.sqlmonster.com|||I can get the http//servername/reportserver to pull up after logging in as
an administrator, but what I need I will explain below.
I have a reportviewer embedded in my asp.net webapplication, but when I
click on the link from a PC that does not have report services loaded it
gives me the page cannot be displayed error.
Thanks!
Jill
--
Message posted via http://www.sqlmonster.com|||The report control doesn't require anything to be installed with the
browser. It just renders an iframe that points to the reportserver. This
may sound silly, but just to be sure, perhaps did you specify localhost as
part of the serverurl property of the viewer? If you did, the iframe won't
be pointing at the remote report server, but would still be looking at
localhost a.k.a itself.
Even if you didn't use localhost, from the client, type in what's in the
serverurl property and make sure you can get to the server from the client.
Also, you can always do a view source on the page, look for the iframe and
take a look at the src property.
Mike G.
"JC via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:de02341f0922462dba9a2ec5cdcb3992@.SQLMonster.com...
>I can get the http//servername/reportserver to pull up after logging in as
> an administrator, but what I need I will explain below.
> I have a reportviewer embedded in my asp.net webapplication, but when I
> click on the link from a PC that does not have report services loaded it
> gives me the page cannot be displayed error.
> Thanks!
> Jill
> --
> Message posted via http://www.sqlmonster.com|||Whoops...(blushes). I found where I had the localhost in the url. I
thought I had taken it out, but I must have not saved it. Thanks for that
tip.
The report is trying to come up now, but it prompts the user for a username
and password. I don't want the user to have to do that. Is there a way
around this?
Thanks!
--
Message posted via http://www.sqlmonster.com|||What is IIS set to allow? For an intranet, you probably want just windows
integrated (not anonymous). Of course, that means giving giving the
users/groups the correct permissions to the reports in the report manager.
Mike G.
"JC via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:183115a982ba408a8600324cca74f560@.SQLMonster.com...
> Whoops...(blushes). I found where I had the localhost in the url. I
> thought I had taken it out, but I must have not saved it. Thanks for that
> tip.
> The report is trying to come up now, but it prompts the user for a
> username
> and password. I don't want the user to have to do that. Is there a way
> around this?
> Thanks!
> --
> Message posted via http://www.sqlmonster.com

Issue with Identity Columns after replication Changes

Dear all,

We have an application that use merge replication between MSDE and Devices with SQL CE.

Due to a major application changes, we have to change our replication on all our workstation. Our process is the following:

- drop current replication

- recreate our replication

After first check our replication seems to work but after some test we have identify that all identity ranges have been reset on the workstation. As side effect, device start to reuse existing range and also existing value in the range.

- Have you ever encounter this type of issue?

- What can create it?

- Is our approach fully inappropriate?

Please let me know if you need more information,

Best regards,

I have a client (SQL2000<->SqlCE) that is experiencing similar problems. We have been unable to determine a cause as yet.

Symptoms:

-"duplicate key on insert" errors during replication for one table (two subscribers appearing to be sharing the same number range)

-"duplicate key on insert" error with a back-end process inserting data into a publisher table (diff to the one above)

The tables are using automatic identity range management and have been synchronising happily for a long time prior to this.

Does anyone know of any actions that might cause the automatic identity range management to go screwy like this?

|||You can try re-adjusting your identity ranges, look at sp_adjustpublisheridentityrange. Otherwise i'm not sure what could have caused the issue, maybe someone manually reset the identity seed on the dbs?