Reseting Indexes

I just migrated a Microsoft Access 2002 database to SQL Server 2005. So far
everything is working great. While updating my Access.mdb file to an
Access.ADP file I've been testing adding, editing and deleting records to
various tables so that I can ensure my Stored Procedures are working
properly.
Now, I have deleted much of my sample data and want all of my indexes to
restart with the lowest number, typically being Number 1. In Access, I would
empty the table, then compact/repair the database and the indexes would be
reset. How do I achieve this with SQL Server 2005 for the following two
examples.
1. No records in the table, so I want to have the index restart at Number 1.
2. I have records in the table (i.e. records 1 to 9) and I want the next
index to start at 10. In this example, my index is currently starting at 23
becuase I've added records to the table and deleted some.
Thank You.<DIV>"Greg" <AccessVBAnet@.newsgroups.nospam> wrote in
message news:BFDAB950-91CD-4248-B042-6008C35A05ED@.microsoft.com...</DIV>>I
just migrated a Microsoft Access 2002 database to SQL Server 2005. So far
> everything is working great. While updating my Access.mdb file to an
> Access.ADP file I've been testing adding, editing and deleting records to
> various tables so that I can ensure my Stored Procedures are working
> properly.
> Now, I have deleted much of my sample data and want all of my indexes to
> restart with the lowest number, typically being Number 1. In Access, I
> would
> empty the table, then compact/repair the database and the indexes would be
> reset. How do I achieve this with SQL Server 2005 for the following two
> examples.
> 1. No records in the table, so I want to have the index restart at Number
> 1.
> 2. I have records in the table (i.e. records 1 to 9) and I want the next
> index to start at 10. In this example, my index is currently starting at
> 23
> becuase I've added records to the table and deleted some.
>
You mean "identity columns" not "indexes" (although your identity columns
are probably indexed).
DBCC CHECKIDENT (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms176057.aspx
David|||Excellent. That accomplished what I was looking for. And, yes, I was
referring to Identity Columns, which are indexed.
Thank You
"David Browne" wrote:
>
> <DIV>"Greg" <AccessVBAnet@.newsgroups.nospam> wrote in
> message news:BFDAB950-91CD-4248-B042-6008C35A05ED@.microsoft.com...</DIV>>I
> just migrated a Microsoft Access 2002 database to SQL Server 2005. So far
> > everything is working great. While updating my Access.mdb file to an
> > Access.ADP file I've been testing adding, editing and deleting records to
> > various tables so that I can ensure my Stored Procedures are working
> > properly.
> >
> > Now, I have deleted much of my sample data and want all of my indexes to
> > restart with the lowest number, typically being Number 1. In Access, I
> > would
> > empty the table, then compact/repair the database and the indexes would be
> > reset. How do I achieve this with SQL Server 2005 for the following two
> > examples.
> >
> > 1. No records in the table, so I want to have the index restart at Number
> > 1.
> > 2. I have records in the table (i.e. records 1 to 9) and I want the next
> > index to start at 10. In this example, my index is currently starting at
> > 23
> > becuase I've added records to the table and deleted some.
> >
> You mean "identity columns" not "indexes" (although your identity columns
> are probably indexed).
> DBCC CHECKIDENT (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms176057.aspx
> David
>

Reseting Indexes

I just migrated a Microsoft Access 2002 database to SQL Server 2005. So far
everything is working great. While updating my Access.mdb file to an
Access.ADP file I've been testing adding, editing and deleting records to
various tables so that I can ensure my Stored Procedures are working
properly.
Now, I have deleted much of my sample data and want all of my indexes to
restart with the lowest number, typically being Number 1. In Access, I would
empty the table, then compact/repair the database and the indexes would be
reset. How do I achieve this with SQL Server 2005 for the following two
examples.
1. No records in the table, so I want to have the index restart at Number 1.
2. I have records in the table (i.e. records 1 to 9) and I want the next
index to start at 10. In this example, my index is currently starting at 23
becuase I've added records to the table and deleted some.
Thank You.<DIV>"Greg" <AccessVBAnet@.newsgroups.nospam> wrote in
message news:BFDAB950-91CD-4248-B042-6008C35A05ED@.microsoft.com...</DIV>>I
just migrated a Microsoft Access 2002 database to SQL Server 2005. So far
> everything is working great. While updating my Access.mdb file to an
> Access.ADP file I've been testing adding, editing and deleting records to
> various tables so that I can ensure my Stored Procedures are working
> properly.
> Now, I have deleted much of my sample data and want all of my indexes to
> restart with the lowest number, typically being Number 1. In Access, I
> would
> empty the table, then compact/repair the database and the indexes would be
> reset. How do I achieve this with SQL Server 2005 for the following two
> examples.
> 1. No records in the table, so I want to have the index restart at Number
> 1.
> 2. I have records in the table (i.e. records 1 to 9) and I want the next
> index to start at 10. In this example, my index is currently starting at
> 23
> becuase I've added records to the table and deleted some.
>
You mean "identity columns" not "indexes" (although your identity columns
are probably indexed).
DBCC CHECKIDENT (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms176057.aspx
David|||Excellent. That accomplished what I was looking for. And, yes, I was
referring to Identity Columns, which are indexed.
Thank You
"David Browne" wrote:

>
> <DIV>"Greg" <AccessVBAnet@.newsgroups.nospam> wrote in
> message news:BFDAB950-91CD-4248-B042-6008C35A05ED@.microsoft.com...</DIV>>I
> just migrated a Microsoft Access 2002 database to SQL Server 2005. So far
> You mean "identity columns" not "indexes" (although your identity columns
> are probably indexed).
> DBCC CHECKIDENT (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms176057.aspx
> David
>

reseeding temporary tables or table data types with identity column

Hi,

I have a indexing problem. I have a sequence that needs to has a index number. I want to use a table data type and have a working sample BUT I cannot reseed the table when needed. How do I do this.

This works only for the first ExitCoilID then I need to RESEED.

Here is my code:

DECLARE

@.EntryCoilCnt AS INT,

@.ExitCoilID AS INT,

@.SubtractedFromEntyCoilCnt AS INT

DECLARE

@.ExitCoilID_NotProcessed TABLE

(ExitCoilID int)

INSERT INTO @.ExitCoilID_NotProcessed

SELECT DISTINCT ExitCoilID

FROM

dbo.TrendEXIT

where

ExitCoilID is not null and

ExitCnt is null

order by

ExitCoilID

DECLARE

@.ExitCoilID_Cnt_Index TABLE

(ExitCoilID int, ExitCnt int IDENTITY (1,1))

IF @.@.ROWCOUNT > 0

BEGIN

DECLARE ExitCoilID_cursor CURSOR FOR

SELECT ExitCoilID FROM @.ExitCoilID_NotProcessed

ORDER BY ExitCoilID

OPEN ExitCoilID_cursor

FETCH NEXT FROM ExitCoilID_cursor

INTO @.ExitCoilID

WHILE @.@.FETCH_STATUS = 0

BEGIN

INSERT INTO @.ExitCoilID_Cnt_Index

SELECT ExitCoilID

FROM dbo.TrendEXIT

WHERE

ExitCoilID = @.ExitCoilID

ORDER BY

EntryCoilID, Cnt

select * from @.ExitCoilID_Cnt_Index

--truncate @.ExitCoilID_Cnt_Index

--DBCC CHECKIDENT ('@.ExitCoilID_Cnt_Index', RESEED, 1)

FETCH NEXT FROM ExitCoilID_cursor

INTO @.ExitCoilID

END

CLOSE ExitCoilID_cursor

DEALLOCATE ExitCoilID_cursor

select * from @.ExitCoilID_Cnt_Index

END --IF @.@.ROWCOUNT <> 0

Is this SQL 2005? Try using ROW_NUMBER() instead of identity.|||

Hi,

Yes I'm using SQL 2005 and I'm new to it. How is ROW_NUMBER() Used? I don't have my onlinbooks with me.

Thanks

|||you can search for it on www.msdn.com.|||

Hi,

This is good when you have 2005. Is there a way to reseed a memory table under 2K ?

Thanks.

|||

Hi,

Look up DBCC CHECKIDENT in the help

DBCC CHECKIDENT ('TableName', RESEED, ReseedValue);

Don

|||

No, there is not method to reseed the local variable table identity value. If you need this functionality, use a local temporary table instead. They are not as convienient but they give you everything you need, and have very favorable scoping that you should be able to get what you need from them.

set nocount on
create table #table (tableId int identity)
insert into #table default values
insert into #table default values
insert into #table default values
insert into #table default values

select *
from #table

truncate table #table

insert into #table default values
insert into #table default values
insert into #table default values
insert into #table default values

select *
from #table

drop table #table

tableId
--
1
2
3
4

tableId
--
1
2
3
4

Otherwise, if variable based tables give you some incredible value, stop using identities and manage number creation manually, like using a table of numbers, or a loop, or just hard coding values if you aren't doing too many (I expect you are, but this is the easiest to code:

set nocount on
declare @.table table (tableId int )
insert into @.table select 1
insert into @.table select 2
insert into @.table select 3
insert into @.table select 4

select *
from @.table

delete from @.table

insert into @.table select 1
insert into @.table select 2
insert into @.table select 3
insert into @.table select 4

select *
from @.table

reseeding temporary tables or table data types with identity column

Hi,

I have a indexing problem. I have a sequence that needs to has a index number. I want to use a table data type and have a working sample BUT I cannot reseed the table when needed. How do I do this.

This works only for the first ExitCoilID then I need to RESEED.

Here is my code:

DECLARE

@.EntryCoilCnt AS INT,

@.ExitCoilID AS INT,

@.SubtractedFromEntyCoilCnt AS INT

DECLARE

@.ExitCoilID_NotProcessed TABLE

(ExitCoilID int)

INSERT INTO @.ExitCoilID_NotProcessed

SELECT DISTINCT ExitCoilID

FROM

dbo.TrendEXIT

where

ExitCoilID is not null and

ExitCnt is null

order by

ExitCoilID

DECLARE

@.ExitCoilID_Cnt_Index TABLE

(ExitCoilID int, ExitCnt int IDENTITY (1,1))

IF @.@.ROWCOUNT > 0

BEGIN

DECLARE ExitCoilID_cursor CURSOR FOR

SELECT ExitCoilID FROM @.ExitCoilID_NotProcessed

ORDER BY ExitCoilID

OPEN ExitCoilID_cursor

FETCH NEXT FROM ExitCoilID_cursor

INTO @.ExitCoilID

WHILE @.@.FETCH_STATUS = 0

BEGIN

INSERT INTO @.ExitCoilID_Cnt_Index

SELECT ExitCoilID

FROM dbo.TrendEXIT

WHERE

ExitCoilID = @.ExitCoilID

ORDER BY

EntryCoilID, Cnt

select * from @.ExitCoilID_Cnt_Index

--truncate @.ExitCoilID_Cnt_Index

--DBCC CHECKIDENT ('@.ExitCoilID_Cnt_Index', RESEED, 1)

FETCH NEXT FROM ExitCoilID_cursor

INTO @.ExitCoilID

END

CLOSE ExitCoilID_cursor

DEALLOCATE ExitCoilID_cursor

select * from @.ExitCoilID_Cnt_Index

END --IF @.@.ROWCOUNT <> 0

Is this SQL 2005? Try using ROW_NUMBER() instead of identity.|||

Hi,

Yes I'm using SQL 2005 and I'm new to it. How is ROW_NUMBER() Used? I don't have my onlinbooks with me.

Thanks

|||you can search for it on www.msdn.com.|||

Hi,

This is good when you have 2005. Is there a way to reseed a memory table under 2K ?

Thanks.

|||

Hi,

Look up DBCC CHECKIDENT in the help

DBCC CHECKIDENT ('TableName', RESEED, ReseedValue);

Don

|||

No, there is not method to reseed the local variable table identity value. If you need this functionality, use a local temporary table instead. They are not as convienient but they give you everything you need, and have very favorable scoping that you should be able to get what you need from them.

set nocount on
create table #table (tableId int identity)
insert into #table default values
insert into #table default values
insert into #table default values
insert into #table default values

select *
from #table

truncate table #table

insert into #table default values
insert into #table default values
insert into #table default values
insert into #table default values

select *
from #table

drop table #table

tableId
--
1
2
3
4

tableId
--
1
2
3
4

Otherwise, if variable based tables give you some incredible value, stop using identities and manage number creation manually, like using a table of numbers, or a loop, or just hard coding values if you aren't doing too many (I expect you are, but this is the easiest to code:

set nocount on
declare @.table table (tableId int )
insert into @.table select 1
insert into @.table select 2
insert into @.table select 3
insert into @.table select 4

select *
from @.table

delete from @.table

insert into @.table select 1
insert into @.table select 2
insert into @.table select 3
insert into @.table select 4

select *
from @.table

sql

Re-seeding Indexes

I've been working with a SQL 2000 test database extensively and am about
ready to move the database from development to production. I'm going to
delete all of the test data that has built up. What I'd like to do is to be
able to reset the various indexes within the tables so that they start again
from 1 or whatever the first seed number would be for the index. Is there a
way to do this other than remove the seed from each table, save the table,
and then set up the seed again?
ThanksYou could try this... I haven't tried it or even looked at the code so I
take no responsibility if it doesn't work or breaks something :)
http://www.sqlservercentral.com/scripts/contributions/507.asp
"D Long" <msnewsaspen.5.dlong@.spamgourmet.com> wrote in message
news:#TN3wdvnDHA.2732@.TK2MSFTNGP11.phx.gbl...
> I've been working with a SQL 2000 test database extensively and am about
> ready to move the database from development to production. I'm going to
> delete all of the test data that has built up. What I'd like to do is to
be
> able to reset the various indexes within the tables so that they start
again
> from 1 or whatever the first seed number would be for the index. Is there
a
> way to do this other than remove the seed from each table, save the table,
> and then set up the seed again?
> Thanks
>|||Indexes don't have "seeds". They are made up from the data in the column(s)
that the index expression is on. If you remove all the data then there is
nothing in the index. If your talking about an column in the table that has
an IDENTITY property you can issue DBCC CHECKIDENT to reseed the next value.
If your removing ALL the rows you can also use TRUNCATE TABLE which not only
deletes all the data in a snap but resets the IDENTITY value as well.
--
Andrew J. Kelly
SQL Server MVP
"D Long" <msnewsaspen.5.dlong@.spamgourmet.com> wrote in message
news:%23TN3wdvnDHA.2732@.TK2MSFTNGP11.phx.gbl...
> I've been working with a SQL 2000 test database extensively and am about
> ready to move the database from development to production. I'm going to
> delete all of the test data that has built up. What I'd like to do is to
be
> able to reset the various indexes within the tables so that they start
again
> from 1 or whatever the first seed number would be for the index. Is there
a
> way to do this other than remove the seed from each table, save the table,
> and then set up the seed again?
> Thanks
>

reseed required after solving conflicts

I have set-up several replications, all of them working on the same
way. But on the last one a strange thing occured: It seems that when a
conflict is solved by choosing to use the displayed data from the
looser, I need to reseed the identity column.
The replication is working on the merge model. On the two databases,
the identity is not for replication, with different seed ( 1 for the
main database, 2 for the other one) and an increment of 10.
The databases are SQL Server 2000, and the operating system is Windows
2000 server.
Could you please tell me why this reseed is required.
Regards
Patrick
Why do you say you need to reseed the identity column? What error message is
generated?
Or is what happens is that subsequent inserts pick the last inserted value?
For example you have an identity range of 10 on one side 1000 on another.
The 1001 row is merged to the publisher but is logged as a conflict and
rolled back. Then you select this conflict to win, and the 1001 row gets
inserted in the publisher. Then does the publisher's next value start at
1002?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Patrick" <pmenage@.intnet.mu> wrote in message
news:1160991102.318000.68140@.i3g2000cwc.googlegrou ps.com...
>I have set-up several replications, all of them working on the same
> way. But on the last one a strange thing occured: It seems that when a
> conflict is solved by choosing to use the displayed data from the
> looser, I need to reseed the identity column.
> The replication is working on the merge model. On the two databases,
> the identity is not for replication, with different seed ( 1 for the
> main database, 2 for the other one) and an increment of 10.
> The databases are SQL Server 2000, and the operating system is Windows
> 2000 server.
> Could you please tell me why this reseed is required.
> Regards
> Patrick
>
|||After solving a confict on a table with an identity, I need to ressed
the identity of this table else, the identity of next record inserted
by the application takes the value just after the one of the identity
of the record on which there were conflicts. As there are other exiting
records, it is rejected due to primary key problem. It seems that when
solving a conflict, the seed is changed to the value of the identity of
the record on wich the conflict was solved.
To answer your second question, yes, the publishers's next value start
at 1002.
I set up the identity to start at 1 on the main database and at 2 on
the other database. On both side the increment is 10.
Looking forward to reading your thoughts on the above.
Best Regards
Patrick
Hilary Cotter wrote:[vbcol=seagreen]
> Why do you say you need to reseed the identity column? What error message is
> generated?
> Or is what happens is that subsequent inserts pick the last inserted value?
> For example you have an identity range of 10 on one side 1000 on another.
> The 1001 row is merged to the publisher but is logged as a conflict and
> rolled back. Then you select this conflict to win, and the 1001 row gets
> inserted in the publisher. Then does the publisher's next value start at
> 1002?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Patrick" <pmenage@.intnet.mu> wrote in message
> news:1160991102.318000.68140@.i3g2000cwc.googlegrou ps.com...

Required Suggestion

Hi Friends

I required a suggestion from your side

Actually I am working in MS Access from last 6 months. Now I want to convert my database into SQL Server

I have a knowledge of SQL Server and I don't have any problem with this.

But what I want to know how to maintain a report in SQL Server I don't have any knowledge for creating a report. My freinds suggest me a Visual Studio 2005 for creating a Crystal Report.

So Please suggest me it is helpful or not and onething more is that I have to work on SQL Server 2000 or SQL Server 2005 which is more beneficial.

Please reply me for this

Waiting for your any kind of reply

Please

Thanks

Ashish

Hi Ashish

U can use Reporting services which come default with SQL Server 2005. this helps to access reports thru browser.|||

Thanks for your reply dear

Can you tell me Reporting Service in SQL Server 2005 is work same as a MS Access

because in Access it is very easy to make a reports so it is same as in MS Access or it

work with some other software.

Please reply me

I am waiting for your reply

Thanks

Ashish

Required permissions for running TextCopy?

I posted this last week, but unfortunately it was lost. So here 'tis again.

I am having trouble with working out what permissions a user needs to run the Textcopy function.

I have setup a stored procedure on the server which runs Textcopy to insert/export a GIF file to/from a network location to an Image field of a database. The stored procedure is called from an Access97 frontend.

It all works fine when the user on the client PC has SQL Server System Administrator permissions, however when the client PC is logged in as a normal user without these permission, the operation fails. Giving the user System Admin permissions fixes the problem, but obviously I can't do this for all the users!

Does anyone have any idea exactly what individual items I need to set permissions for to enable Textcopy to run??

Any hints at all, (no matter how small) would be much appreciated.

Thanks,

Ian.
:confused:What messages appear in the event viewer ? What are the current permissions on the textcopy.exe file ?|||Thanks for those thoughts.

Permissions on the file and directory are set to Full Control Everyone. (can't be that)

I can't find any messages being writen to the event log when this error occurs.

Ian.|||Please provide the stored procedure code.|||Here is SQL stored procedure:

CREATE PROCEDURE sp_textcopy (
@.srvname varchar (30),
@.login varchar (30),
@.password varchar (30),
@.dbname varchar (30),
@.tbname varchar (30),
@.colname varchar (30),
@.filename varchar (60),
@.whereclause varchar (50),
@.direction char(1))
AS
DECLARE @.exec_str varchar (255)
SELECT @.exec_str =
'D:\Temp\textcopy /S ' +@.srvname +
' /U ' + @.login +
' /P ' + @.password+
' /D ' + @.dbname+
' /T ' + @.tbname +
' /C ' + @.colname +
' /W "' + @.whereclause+
'" /F ' +@.filename+
' /' + @.direction+
' /Z'
EXEC master..xp_cmdshell @.exec_str
GO|||What are the permissions on xp_cmdshell ?|||The permission on xp_cmdshell (in the master DB) are set to enable the cleint user run it.

(They weren't before but they are now. Still won't work)|||Did you create a sql server agent proxy account ?|||Do you mean a Proxy Server account for the same account that SQL Server agent runs under?

How does this effect it?

I would have thought that the permissions are within SQL as setting the account to be a SQL administrator fixes the problem? (I could be wrong)|||SQL server uses this account to execute the xp_cmdshell - it also uses this account for execution of an agent job (both of these apply to non-sysadmin accounts). For non-sysadmin accounts, these commands will be executed under the security context of this account. So you have to create a sql server agent proxy account.|||You were Right!!

I had not created a SQL Server Agent Proxy Account.

When I eventually found this and created an account it works.

Thanks a lot!

Ian.|||Originally posted by rnealejr
SQL server uses this account to execute the xp_cmdshell - it also uses this account for execution of an agent job (both of these apply to non-sysadmin accounts). For non-sysadmin accounts, these commands will be executed under the security context of this account. So you have to create a sql server agent proxy account.

how do you create a sql server agent proxy account?

Requesting a attribute + registering info when availible

I'm working with VB.NET. I have three questions an i hope somone will answer.
I want to reguest this pice ## IC25N040ATMR04-0 ## How can i do that?
<Hard_disks>
<primary_disk size="40007761920"
sernum="MRG257K2EBGTUH">IC25N040ATMR04-0</primary_disk>
<disk name="C:" sys="Ntfs" size="10.0 Gb" freePr="23.5" free="2.35 Gb" />
<disk name="D:" sys="Ntfs" size="28.1 Gb" freePr="32.7" free="9.21 Gb" />
</Hard_disks>
I have tried to do it like this just like otherones i have don but this one
is different. I don't know how to do it?!!!
'-- Harddisk Informatie
node = XMLDocument.SelectSingleNode("report/Hard_disks")
AttrNode = node.Attributes.GetNamedItem("primary_disk")
strGrootte = AttrNode.Value
MsgBox(strSerienummer)
Exit Sub
i get a debug massage that says:
An unhandled exception of type 'System.NullReferenceException' occurred in
WindowsApplication5.exe
Additional information: De objectverwijzing is niet op een exemplaar van een
object ingesteld.
what should i do ? can you show me?
Second Question: image you have this as a XML file:
<Hard_disks>
<primary_disk size="30733516800" sernum="N40W3BRC">Maxtor
33073H4</primary_disk>
<disk name="C:" sys="Ntfs" size="5.00 Gb" freePr="47.6" free="2.38 Gb" />
<disk name="D:" sys="Ntfs" size="12.0 Gb" freePr="26.7" free="3.20 Gb" />
<disk name="E:" sys="Fat32" size="12.3 Gb" freePr="3.3" free="408.9 Mb" />
<secondary_disk size="1083801600" sernum="WD-WM3531743957">WDC
AC21000H</secondary_disk>
<disk name="F:" sys="Fat" size="1.03 Gb" freePr="11.3" free="116.4 Mb" />
</Hard_disks>
How can i make it so that when there is a second HD that it will registere
it and when is not there it don't? Somebody a clue
nodeList = XMLDocument.SelectNodes("/report/ juiste info /juiste info")
For i = 0 To nodeList.Count - 1
node = nodeList.Item(i)
Third Question is:
When i have more than one table where i want to insert information, what is
the correct way to do it. I can insert one table with more values but i can't
insert more table i get a syntaxis wrong!!
this is my SQL line:
strSQL = "INSERT INTO software, operatings (naam, versie, grootte,
datum),(os) VALUES ('" & strNaam & "','" & strVersie & "','" & strGrootte &
"','" & StrDatum & "'),('" & strNaam & "',)"
Beginner Programmer!
Your best bet would be to ask this question in the VB.Net newsgroups.
This newsgroup is for SQL Server XML questions.
Bryant

Request Length Exceeded

I'm re-posting under an updated subscriber ID so Microsoft techs will answer...
I am working in Visual Studio 2005. I have a Report Model project with a
data source, a data source view which contains all of the tables in my
database (SQL Server 2005), and a Report Model that contains the items that I
want the users to be able to work with.
When I deploy the project, I get the following error:
Error 2 System.Web.Services.Protocols.SoapException: There was an exception
running the extensions specified in the config file. -->
System.Web.HttpException: Maximum request length exceeded. at
System.Web.HttpRequest.GetEntireRawContent() at
System.Web.HttpRequest.get_InputStream() at
System.Web.Services.Protocols.SoapServerProtocol.Initialize() -- End of
inner exception stack trace -- at
System.Web.Services.Protocols.SoapServerProtocol.Initialize() at
System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type,
HttpContext context, HttpRequest request, HttpResponse response, Boolean&
abortProcessing) c:\crimson\wmsdev\vbapps2005\desktopweb\wmsreportmodels\CrimsonWMS.smdl 0 0
How do I fix it?
Thank you,
Mark LauserI second that. Ran into the same problem today.
Don Olsen
"Mark Lauser" wrote:
> I'm re-posting under an updated subscriber ID so Microsoft techs will answer...
>
> I am working in Visual Studio 2005. I have a Report Model project with a
> data source, a data source view which contains all of the tables in my
> database (SQL Server 2005), and a Report Model that contains the items that I
> want the users to be able to work with.
> When I deploy the project, I get the following error:
> Error 2 System.Web.Services.Protocols.SoapException: There was an exception
> running the extensions specified in the config file. -->
> System.Web.HttpException: Maximum request length exceeded. at
> System.Web.HttpRequest.GetEntireRawContent() at
> System.Web.HttpRequest.get_InputStream() at
> System.Web.Services.Protocols.SoapServerProtocol.Initialize() -- End of
> inner exception stack trace -- at
> System.Web.Services.Protocols.SoapServerProtocol.Initialize() at
> System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type,
> HttpContext context, HttpRequest request, HttpResponse response, Boolean&
> abortProcessing) c:\crimson\wmsdev\vbapps2005\desktopweb\wmsreportmodels\CrimsonWMS.smdl 0 0
> How do I fix it?
> Thank you,
> Mark Lauser
>|||After re-reading this error and looking at the smdl file I suspected that the
problem was the XML being sent to the SOAP service was too long. Started
deleting some unused tables and viola it worked after deleting about 6
tables. Your milage may vary.
Hope that helps.
-Don
"Mark Lauser" wrote:
> I'm re-posting under an updated subscriber ID so Microsoft techs will answer...
>
> I am working in Visual Studio 2005. I have a Report Model project with a
> data source, a data source view which contains all of the tables in my
> database (SQL Server 2005), and a Report Model that contains the items that I
> want the users to be able to work with.
> When I deploy the project, I get the following error:
> Error 2 System.Web.Services.Protocols.SoapException: There was an exception
> running the extensions specified in the config file. -->
> System.Web.HttpException: Maximum request length exceeded. at
> System.Web.HttpRequest.GetEntireRawContent() at
> System.Web.HttpRequest.get_InputStream() at
> System.Web.Services.Protocols.SoapServerProtocol.Initialize() -- End of
> inner exception stack trace -- at
> System.Web.Services.Protocols.SoapServerProtocol.Initialize() at
> System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type,
> HttpContext context, HttpRequest request, HttpResponse response, Boolean&
> abortProcessing) c:\crimson\wmsdev\vbapps2005\desktopweb\wmsreportmodels\CrimsonWMS.smdl 0 0
> How do I fix it?
> Thank you,
> Mark Lauser
>|||Thanks for your input Don. I tried deleting all I could stand to, but the
problem persisted. I'm hoping for a different solution that will allow a
larger request length.
Best Regards,
Mark Lauser
"Don Olsen" wrote:
> After re-reading this error and looking at the smdl file I suspected that the
> problem was the XML being sent to the SOAP service was too long. Started
> deleting some unused tables and viola it worked after deleting about 6
> tables. Your milage may vary.
> Hope that helps.
> -Don
> "Mark Lauser" wrote:
> > I'm re-posting under an updated subscriber ID so Microsoft techs will answer...
> >
> >
> > I am working in Visual Studio 2005. I have a Report Model project with a
> > data source, a data source view which contains all of the tables in my
> > database (SQL Server 2005), and a Report Model that contains the items that I
> > want the users to be able to work with.
> >
> > When I deploy the project, I get the following error:
> >
> > Error 2 System.Web.Services.Protocols.SoapException: There was an exception
> > running the extensions specified in the config file. -->
> > System.Web.HttpException: Maximum request length exceeded. at
> > System.Web.HttpRequest.GetEntireRawContent() at
> > System.Web.HttpRequest.get_InputStream() at
> > System.Web.Services.Protocols.SoapServerProtocol.Initialize() -- End of
> > inner exception stack trace -- at
> > System.Web.Services.Protocols.SoapServerProtocol.Initialize() at
> > System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type,
> > HttpContext context, HttpRequest request, HttpResponse response, Boolean&
> > abortProcessing) c:\crimson\wmsdev\vbapps2005\desktopweb\wmsreportmodels\CrimsonWMS.smdl 0 0
> >
> > How do I fix it?
> >
> > Thank you,
> > Mark Lauser
> >|||Hey, Mark
I think I just replied to your original post, so see if you can dig back a
couple of posts and read the answer...but it has to do with the <httpRuntime
maxRequestLength="" /> element in the web.config file of the ReportServer and
Report Manager apps.
--
Regards,
Thiago Silva
"Mark Lauser" wrote:
> Thanks for your input Don. I tried deleting all I could stand to, but the
> problem persisted. I'm hoping for a different solution that will allow a
> larger request length.
> Best Regards,
> Mark Lauser
>
> "Don Olsen" wrote:
> > After re-reading this error and looking at the smdl file I suspected that the
> > problem was the XML being sent to the SOAP service was too long. Started
> > deleting some unused tables and viola it worked after deleting about 6
> > tables. Your milage may vary.
> >
> > Hope that helps.
> >
> > -Don
> >
> > "Mark Lauser" wrote:
> >
> > > I'm re-posting under an updated subscriber ID so Microsoft techs will answer...
> > >
> > >
> > > I am working in Visual Studio 2005. I have a Report Model project with a
> > > data source, a data source view which contains all of the tables in my
> > > database (SQL Server 2005), and a Report Model that contains the items that I
> > > want the users to be able to work with.
> > >
> > > When I deploy the project, I get the following error:
> > >
> > > Error 2 System.Web.Services.Protocols.SoapException: There was an exception
> > > running the extensions specified in the config file. -->
> > > System.Web.HttpException: Maximum request length exceeded. at
> > > System.Web.HttpRequest.GetEntireRawContent() at
> > > System.Web.HttpRequest.get_InputStream() at
> > > System.Web.Services.Protocols.SoapServerProtocol.Initialize() -- End of
> > > inner exception stack trace -- at
> > > System.Web.Services.Protocols.SoapServerProtocol.Initialize() at
> > > System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type,
> > > HttpContext context, HttpRequest request, HttpResponse response, Boolean&
> > > abortProcessing) c:\crimson\wmsdev\vbapps2005\desktopweb\wmsreportmodels\CrimsonWMS.smdl 0 0
> > >
> > > How do I fix it?
> > >
> > > Thank you,
> > > Mark Lauser
> > >

Request Length Exceeded

I am working in Visual Studio 2005. I have a Report Model project with a
data source, a data source view which contains all of the tables in my
database (SQL Server 2005), and a Report Model that contains the items that I
want the users to be able to work with.
When I deploy the project, I get the following error:
Error 2 System.Web.Services.Protocols.SoapException: There was an exception
running the extensions specified in the config file. -->
System.Web.HttpException: Maximum request length exceeded. at
System.Web.HttpRequest.GetEntireRawContent() at
System.Web.HttpRequest.get_InputStream() at
System.Web.Services.Protocols.SoapServerProtocol.Initialize() -- End of
inner exception stack trace -- at
System.Web.Services.Protocols.SoapServerProtocol.Initialize() at
System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type,
HttpContext context, HttpRequest request, HttpResponse response, Boolean&
abortProcessing) c:\crimson\wmsdev\vbapps2005\desktopweb\wmsreportmodels\CrimsonWMS.smdl 0 0
How do I fix it?
Thank you,
Mark LauserHi, Mark
It sounds like your report model file is kinda large so when you
upload/deploy it to the server, the asp.net http request times out before the
file is fully uploaded (it uses the web service).
You are gonna have to change the value in the maxRequestLength attribute of
the httpRuntime element in the web.config file of your ReportServer and
Report Manager so it doesn't time out. The default size is 4096 KB (4 MB),
but I bet your file is much larger, so change it to something like:
<httpRuntime ... maxRequestLength="151200" .../>
Hope this helps you!
--
Regards,
Thiago Silva
"Mark Lauser" wrote:
> I am working in Visual Studio 2005. I have a Report Model project with a
> data source, a data source view which contains all of the tables in my
> database (SQL Server 2005), and a Report Model that contains the items that I
> want the users to be able to work with.
> When I deploy the project, I get the following error:
> Error 2 System.Web.Services.Protocols.SoapException: There was an exception
> running the extensions specified in the config file. -->
> System.Web.HttpException: Maximum request length exceeded. at
> System.Web.HttpRequest.GetEntireRawContent() at
> System.Web.HttpRequest.get_InputStream() at
> System.Web.Services.Protocols.SoapServerProtocol.Initialize() -- End of
> inner exception stack trace -- at
> System.Web.Services.Protocols.SoapServerProtocol.Initialize() at
> System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type,
> HttpContext context, HttpRequest request, HttpResponse response, Boolean&
> abortProcessing) c:\crimson\wmsdev\vbapps2005\desktopweb\wmsreportmodels\CrimsonWMS.smdl 0 0
> How do I fix it?
> Thank you,
> Mark Lauser|||Thanks, that worked. My problem was that my web.config file didn't have a
maxRequestLength attribute for the httpRuntime element, so my 'Find' didn't
find anything. Now that I know where it is supposed to be, I added the
attribute and it worked fine.
There must be a default that is used when the attribute is not present.
Thanks again!
Mark Lauser
"HC" wrote:
> Hi, Mark
> It sounds like your report model file is kinda large so when you
> upload/deploy it to the server, the asp.net http request times out before the
> file is fully uploaded (it uses the web service).
> You are gonna have to change the value in the maxRequestLength attribute of
> the httpRuntime element in the web.config file of your ReportServer and
> Report Manager so it doesn't time out. The default size is 4096 KB (4 MB),
> but I bet your file is much larger, so change it to something like:
> <httpRuntime ... maxRequestLength="151200" .../>
> Hope this helps you!
> --
> Regards,
> Thiago Silva
> "Mark Lauser" wrote:
> > I am working in Visual Studio 2005. I have a Report Model project with a
> > data source, a data source view which contains all of the tables in my
> > database (SQL Server 2005), and a Report Model that contains the items that I
> > want the users to be able to work with.
> >
> > When I deploy the project, I get the following error:
> >
> > Error 2 System.Web.Services.Protocols.SoapException: There was an exception
> > running the extensions specified in the config file. -->
> > System.Web.HttpException: Maximum request length exceeded. at
> > System.Web.HttpRequest.GetEntireRawContent() at
> > System.Web.HttpRequest.get_InputStream() at
> > System.Web.Services.Protocols.SoapServerProtocol.Initialize() -- End of
> > inner exception stack trace -- at
> > System.Web.Services.Protocols.SoapServerProtocol.Initialize() at
> > System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type,
> > HttpContext context, HttpRequest request, HttpResponse response, Boolean&
> > abortProcessing) c:\crimson\wmsdev\vbapps2005\desktopweb\wmsreportmodels\CrimsonWMS.smdl 0 0
> >
> > How do I fix it?
> >
> > Thank you,
> > Mark Lauser|||Yeah, the default is in the machine.config file
(C:\WINDOWS\Microsoft.NET\Framework\[VERSION_NUMBER]\CONFIG).
The aspnet engine follows a hierarchy of config files. If it doesn't
find a setting in the inner-most config, it will keep searching up the
hierarchy until it finds it.
Most settings are defined with a default in the machine.config file.
So if you don't override a setting in your application folder's
web.config, it will use whatever is defined in the machine.config file.
Regards,
Thiago Silva|||Thanks, that makes sense.
My problem was further complicated by the fact that there was no
maxRequestLength in my machine.config for the version of the .net framework
that I am working in. It must have defaulted all the way up the hierarchy.
I noticed that the notes in the file say to only include a setting if you
want to override the default (for better performance).
Best Regards,
Mark Lauser
"tafs7" wrote:
> Yeah, the default is in the machine.config file
> (C:\WINDOWS\Microsoft.NET\Framework\[VERSION_NUMBER]\CONFIG).
> The aspnet engine follows a hierarchy of config files. If it doesn't
> find a setting in the inner-most config, it will keep searching up the
> hierarchy until it finds it.
> Most settings are defined with a default in the machine.config file.
> So if you don't override a setting in your application folder's
> web.config, it will use whatever is defined in the machine.config file.
> Regards,
> Thiago Silva
>

Reprting Services 2000 Printing

Hi,
I am using Reporting services 2000 in asp.net application.Everything is working fine ,but when I am trying to get a print using Internet Explorer Printing options ,the toolbar of the report is also coming into the print preview.I want to get rid of it.I want only the report no toolbar.
Can anybody help me?
Thanks!!I am not sure if IE Printing options will allow you to do that. You might need to write some code to do that. I'll see if I can cook up something for you if I get the time.

representative sub-data

Since indexed views aren't going to work for me, I'm working on
revamping a large, ugly query that hits a large, ugly database.
The database stores inventory.
Some items in inventory are single items (e.g. a calendar), while
others are grouped (e.g. a shirt with several color/size options).
The schema of the relevant tables looks like:
product:
--
ID
sku
Color
Description
Price
productGroup:
--
ID
sku
Name
productGroupElement:
--
ID
productID
productGroupID
I need a query that returns both the standalone and grouped items
together. I'm doing this with a union. The tricky part (for me) is in
getting the grouped items, grabbing representative data from one of the
standalone items that comprise the group.
Here's the current query I'm using:
select 'standalone' as flag, id, sku, color, description
from product
where id NOT IN (select productID from productGroupElement)
UNION
select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id =
pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id = (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON
pge1.productID = p1.id
WHERE pge1.productGroupID =
pge.productGroupID
)
So what I'd like to know is:
1) Is the union approach a reasonably efficient way to combine
standalone and grouped items?
2) is the sub query in the WHERE clause of the second query the best
way to grab representative data?
Thanks,
ErikHI Erik
"voldengen@.gmail.com" wrote:

> Since indexed views aren't going to work for me, I'm working on
> revamping a large, ugly query that hits a large, ugly database.
> The database stores inventory.
> Some items in inventory are single items (e.g. a calendar), while
> others are grouped (e.g. a shirt with several color/size options).
> The schema of the relevant tables looks like:
> product:
> --
> ID
> sku
> Color
> Description
> Price
>
> productGroup:
> --
> ID
> sku
> Name
>
> productGroupElement:
> --
> ID
> productID
> productGroupID
>
You should check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to
post useful DDL and sample data

> I need a query that returns both the standalone and grouped items
> together. I'm doing this with a union. The tricky part (for me) is in
> getting the grouped items, grabbing representative data from one of the
> standalone items that comprise the group.
> Here's the current query I'm using:
> select 'standalone' as flag, id, sku, color, description
> from product
> where id NOT IN (select productID from productGroupElement)
> UNION
> select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
> from product p inner join productGroupElement pge on p.id =
> pge.productID
> INNER JOIN productGroup pg on pg.id = pge.productGroupID
> where p.id = (
> select max(pge1.ItemID)
> FROM productGroupElement pge1
> INNER JOIN product p1 ON
> pge1.productID = p1.id
> WHERE pge1.productGroupID =
> pge.productGroupID
> )
>
What happens to the items returned by:
select 'others' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id =
pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id <> (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON pge1.productID = p1.id
WHERE pge1.productGroupID = pge.productGroupID
)

> Thanks,
> Erik
Would this give what you want?
SELECT CASE WHEN g.productID IS NULL THEN 'standalone' ELSE 'grouped' END AS
flag, p.id, p.sku, p.color, p.description
from product p
LEFT JOIN productGroupElement g ON p.id = g.productID
John|||On 29 Dec 2006 11:59:13 -0800, voldengen@.gmail.com wrote:
(snip)

>1) Is the union approach a reasonably efficient way to combine
>standalone and grouped items?
Hi Erik,
Since the constants in the SELECT lists makes the two sides of the UNION
disjunct, you should change it to UNION ALL. Without the ALL, SQL Server
will do extra work to find and remove duplicates.

>2) is the sub query in the WHERE clause of the second query the best
>way to grab representative data?
Only if a sample that is achieved by always using the highest ItemID can
be considered representative. Most statisticians would disagree. (Would
you trust a political popularity poll that is done by interviewing the
oldest member of each household?)
Hugo Kornelis, SQL Server MVP

representative sub-data

Since indexed views aren't going to work for me, I'm working on
revamping a large, ugly query that hits a large, ugly database.
The database stores inventory.
Some items in inventory are single items (e.g. a calendar), while
others are grouped (e.g. a shirt with several color/size options).
The schema of the relevant tables looks like:
product:
ID
sku
Color
Description
Price
productGroup:
ID
sku
Name
productGroupElement:
ID
productID
productGroupID
I need a query that returns both the standalone and grouped items
together. I'm doing this with a union. The tricky part (for me) is in
getting the grouped items, grabbing representative data from one of the
standalone items that comprise the group.
Here's the current query I'm using:
select 'standalone' as flag, id, sku, color, description
from product
where id NOT IN (select productID from productGroupElement)
UNION
select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id =
pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id = (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON
pge1.productID = p1.id
WHERE pge1.productGroupID =
pge.productGroupID
)
So what I'd like to know is:
1) Is the union approach a reasonably efficient way to combine
standalone and grouped items?
2) is the sub query in the WHERE clause of the second query the best
way to grab representative data?
Thanks,
Erik
HI Erik
"voldengen@.gmail.com" wrote:

> Since indexed views aren't going to work for me, I'm working on
> revamping a large, ugly query that hits a large, ugly database.
> The database stores inventory.
> Some items in inventory are single items (e.g. a calendar), while
> others are grouped (e.g. a shirt with several color/size options).
> The schema of the relevant tables looks like:
> product:
> --
> ID
> sku
> Color
> Description
> Price
>
> productGroup:
> --
> ID
> sku
> Name
>
> productGroupElement:
> --
> ID
> productID
> productGroupID
>
You should check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to
post useful DDL and sample data

> I need a query that returns both the standalone and grouped items
> together. I'm doing this with a union. The tricky part (for me) is in
> getting the grouped items, grabbing representative data from one of the
> standalone items that comprise the group.
> Here's the current query I'm using:
> select 'standalone' as flag, id, sku, color, description
> from product
> where id NOT IN (select productID from productGroupElement)
> UNION
> select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
> from product p inner join productGroupElement pge on p.id =
> pge.productID
> INNER JOIN productGroup pg on pg.id = pge.productGroupID
> where p.id = (
> select max(pge1.ItemID)
> FROM productGroupElement pge1
> INNER JOIN product p1 ON
> pge1.productID = p1.id
> WHERE pge1.productGroupID =
> pge.productGroupID
> )
>
What happens to the items returned by:
select 'others' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id =
pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id <> (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON pge1.productID = p1.id
WHERE pge1.productGroupID = pge.productGroupID
)

> Thanks,
> Erik
Would this give what you want?
SELECT CASE WHEN g.productID IS NULL THEN 'standalone' ELSE 'grouped' END AS
flag, p.id, p.sku, p.color, p.description
from product p
LEFT JOIN productGroupElement g ON p.id = g.productID
John

representative sub-data

Since indexed views aren't going to work for me, I'm working on
revamping a large, ugly query that hits a large, ugly database.
The database stores inventory.
Some items in inventory are single items (e.g. a calendar), while
others are grouped (e.g. a shirt with several color/size options).
The schema of the relevant tables looks like:
product:
--
ID
sku
Color
Description
Price
productGroup:
--
ID
sku
Name
productGroupElement:
--
ID
productID
productGroupID
I need a query that returns both the standalone and grouped items
together. I'm doing this with a union. The tricky part (for me) is in
getting the grouped items, grabbing representative data from one of the
standalone items that comprise the group.
Here's the current query I'm using:
select 'standalone' as flag, id, sku, color, description
from product
where id NOT IN (select productID from productGroupElement)
UNION
select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id = pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id = (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON
pge1.productID = p1.id
WHERE pge1.productGroupID = pge.productGroupID
)
So what I'd like to know is:
1) Is the union approach a reasonably efficient way to combine
standalone and grouped items?
2) is the sub query in the WHERE clause of the second query the best
way to grab representative data?
Thanks,
ErikHI Erik
"voldengen@.gmail.com" wrote:
> Since indexed views aren't going to work for me, I'm working on
> revamping a large, ugly query that hits a large, ugly database.
> The database stores inventory.
> Some items in inventory are single items (e.g. a calendar), while
> others are grouped (e.g. a shirt with several color/size options).
> The schema of the relevant tables looks like:
> product:
> --
> ID
> sku
> Color
> Description
> Price
>
> productGroup:
> --
> ID
> sku
> Name
>
> productGroupElement:
> --
> ID
> productID
> productGroupID
>
You should check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to
post useful DDL and sample data
> I need a query that returns both the standalone and grouped items
> together. I'm doing this with a union. The tricky part (for me) is in
> getting the grouped items, grabbing representative data from one of the
> standalone items that comprise the group.
> Here's the current query I'm using:
> select 'standalone' as flag, id, sku, color, description
> from product
> where id NOT IN (select productID from productGroupElement)
> UNION
> select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
> from product p inner join productGroupElement pge on p.id => pge.productID
> INNER JOIN productGroup pg on pg.id = pge.productGroupID
> where p.id = (
> select max(pge1.ItemID)
> FROM productGroupElement pge1
> INNER JOIN product p1 ON
> pge1.productID = p1.id
> WHERE pge1.productGroupID => pge.productGroupID
> )
>
What happens to the items returned by:
select 'others' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id =pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id <> (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON pge1.productID = p1.id
WHERE pge1.productGroupID = pge.productGroupID
)
> Thanks,
> Erik
Would this give what you want?
SELECT CASE WHEN g.productID IS NULL THEN 'standalone' ELSE 'grouped' END AS
flag, p.id, p.sku, p.color, p.description
from product p
LEFT JOIN productGroupElement g ON p.id = g.productID
John|||On 29 Dec 2006 11:59:13 -0800, voldengen@.gmail.com wrote:
(snip)
>1) Is the union approach a reasonably efficient way to combine
>standalone and grouped items?
Hi Erik,
Since the constants in the SELECT lists makes the two sides of the UNION
disjunct, you should change it to UNION ALL. Without the ALL, SQL Server
will do extra work to find and remove duplicates.
>2) is the sub query in the WHERE clause of the second query the best
>way to grab representative data?
Only if a sample that is achieved by always using the highest ItemID can
be considered representative. Most statisticians would disagree. (Would
you trust a political popularity poll that is done by interviewing the
oldest member of each household?)
--
Hugo Kornelis, SQL Server MVP

Repost:The processing of filter expression for the chart "MyChart" cannot be performed. Th

I got the following error when I try to luanch the report ,it is working on
another server with SP1 installed on it .Could it be the problem?(Not havinf
Sp1)
The processing of filter expression for the chart "MyChart" cannot be
performed. The comparison failed
I removed the filter and it works,why this filter is working in the other
server and not in this server.
here is the filters that I use:
=Fields!monthName.Value
!= Total
ThanksDoes installing SP1 resolve it on the other server too?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"ALI-R" <newbie@.microsoft.com> wrote in message
news:OkIHP4KCFHA.1392@.tk2msftngp13.phx.gbl...
> I got the following error when I try to luanch the report ,it is working
on
> another server with SP1 installed on it .Could it be the problem?(Not
havinf
> Sp1)
> The processing of filter expression for the chart "MyChart" cannot be
> performed. The comparison failed
> I removed the filter and it works,why this filter is working in the other
> server and not in this server.
> here is the filters that I use:
>
> =Fields!monthName.Value
> !=> Total
> Thanks
>|||I haven't had the chance to install SP1 yet,Do you think it might be
relevant?
Thanks
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23vIDaCOCFHA.268@.TK2MSFTNGP10.phx.gbl...
> Does installing SP1 resolve it on the other server too?
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "ALI-R" <newbie@.microsoft.com> wrote in message
> news:OkIHP4KCFHA.1392@.tk2msftngp13.phx.gbl...
> > I got the following error when I try to luanch the report ,it is working
> on
> > another server with SP1 installed on it .Could it be the problem?(Not
> havinf
> > Sp1)
> >
> > The processing of filter expression for the chart "MyChart" cannot
be
> > performed. The comparison failed
> > I removed the filter and it works,why this filter is working in the
other
> > server and not in this server.
> > here is the filters that I use:
> >
> >
> > =Fields!monthName.Value
> > !=> > Total
> >
> > Thanks
> >
> >
>|||While there is no specific change for chart filters in SP1, there are some
improvements to make chart processing more efficient. Also from your
description, the same report with the same data works fine on the SP1
server.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"ALI-R" <newbie@.microsoft.com> wrote in message
news:exH0YqUCFHA.2960@.TK2MSFTNGP14.phx.gbl...
> I haven't had the chance to install SP1 yet,Do you think it might be
> relevant?
> Thanks
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:%23vIDaCOCFHA.268@.TK2MSFTNGP10.phx.gbl...
> > Does installing SP1 resolve it on the other server too?
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "ALI-R" <newbie@.microsoft.com> wrote in message
> > news:OkIHP4KCFHA.1392@.tk2msftngp13.phx.gbl...
> > > I got the following error when I try to luanch the report ,it is
working
> > on
> > > another server with SP1 installed on it .Could it be the problem?(Not
> > havinf
> > > Sp1)
> > >
> > > The processing of filter expression for the chart "MyChart" cannot
> be
> > > performed. The comparison failed
> > > I removed the filter and it works,why this filter is working in the
> other
> > > server and not in this server.
> > > here is the filters that I use:
> > >
> > >
> > > =Fields!monthName.Value
> > > !=> > > Total
> > >
> > > Thanks
> > >
> > >
> >
> >
>|||Yes,It's working fine in the server with SP1.I willl let you know as soon as
I install SP1 and should the problem is solved.
Thanks
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:e9QjDfaCFHA.1936@.TK2MSFTNGP14.phx.gbl...
> While there is no specific change for chart filters in SP1, there are some
> improvements to make chart processing more efficient. Also from your
> description, the same report with the same data works fine on the SP1
> server.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "ALI-R" <newbie@.microsoft.com> wrote in message
> news:exH0YqUCFHA.2960@.TK2MSFTNGP14.phx.gbl...
> > I haven't had the chance to install SP1 yet,Do you think it might be
> > relevant?
> > Thanks
> > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> > news:%23vIDaCOCFHA.268@.TK2MSFTNGP10.phx.gbl...
> > > Does installing SP1 resolve it on the other server too?
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > > "ALI-R" <newbie@.microsoft.com> wrote in message
> > > news:OkIHP4KCFHA.1392@.tk2msftngp13.phx.gbl...
> > > > I got the following error when I try to luanch the report ,it is
> working
> > > on
> > > > another server with SP1 installed on it .Could it be the
problem?(Not
> > > havinf
> > > > Sp1)
> > > >
> > > > The processing of filter expression for the chart "MyChart"
cannot
> > be
> > > > performed. The comparison failed
> > > > I removed the filter and it works,why this filter is working in the
> > other
> > > > server and not in this server.
> > > > here is the filters that I use:
> > > >
> > > >
> > > > =Fields!monthName.Value
> > > > !=> > > > Total
> > > >
> > > > Thanks
> > > >
> > > >
> > >
> > >
> >
> >
>

Repost - RS e-mailing attachments via SMTP

I can configure a working subscription to e-mail through my SMTP server when
sending only the LINK to a report BUT NOT if I attach the report to the
e-mail in say excel or xml format.
I have tested using a command line SMTP utility and have been able to send
both a plain text message and a plain text message wth an attachment so I
know the Mail server will accept it.
I'll include a copy of the log file from when the error occurs.
Anyone out there have something for me to check? I have done my research on
the web but not been able to find any solutions.
Travis
Here's the error from the log... I can't make much of it... Other than the
part that says "more details in the log files -->please see the log
files..." :-)
ReportingServicesService!emailextension!728!07/29/2004-07:32:05:: Error
sending email.
Microsoft.ReportingServices.Diagnostics.Utilities.RSException: The Report
Server has encountered a configuration error; more details in the log
files -->
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorEx
ception: The Report Server has encountered a configuration error; more
details in the log files
at
Microsoft.ReportingServices.Authorization.Native.GetAuthzContextForUser(IntP
tr userSid)
at Microsoft.ReportingServices.Authorization.Native.IsAdmin(String
userName)
at
Microsoft.ReportingServices.Authorization.WindowsAuthorization.IsAdmin(Strin
g userName, IntPtr userToken)
at
Microsoft.ReportingServices.Authorization.WindowsAuthorization.CheckAccess(S
tring userName, IntPtr userToken, Byte[] secDesc, ReportOperation
requiredOperation)
at Microsoft.ReportingServices.Library.Security.CheckAccess(ItemType
catItemType, Byte[] secDesc, ReportOperation rptOper)
at
Microsoft.ReportingServices.Library.RSService._GetReportParameterDefinitionF
romCatalog(CatalogItemContext reportContext, String historyID, Boolean
forRendering, Guid& reportID, Int32& executionOption, String&
savedParametersXml, ReportSnapshot& compiledDefinition, ReportSnapshot&
snapshotData, Guid& linkID, DateTime& historyDate)
at
Microsoft.ReportingServices.Library.RSService._GetReportParameters(String
report, String historyID, Boolean forRendering, NameValueCollection values,
DatasourceCredentialsCollection credentials)
at
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(Catalog
ItemContext reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters)
at
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters, String[]&
secondaryStreamNames)
at
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
at
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
-- End of inner exception stack trace --
at
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
at
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RS
Service rs, CatalogItemContext reportContext, ClientRequest session,
JobTypeEnum type, Warning[]& warnings, ParameterInfoCollection&
effectiveParameters, String[]& secondaryStreamNames)
at Microsoft.ReportingServices.Library.ReportImpl.Render(String
renderFormat, String deviceInfo)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.ConstructMes
sageBody(IMessage message, Notification notification, SubscriptionData data)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.CreateMessag
e(Notification notification)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Noti
fication notification)Is this with Lotus? I know there are some problems with Lotus discussed on
this newsgroup.
Bruce L-C
"REM7600" <rem7600@.hotmail.com> wrote in message
news:OibDe9JeEHA.996@.TK2MSFTNGP12.phx.gbl...
> I can configure a working subscription to e-mail through my SMTP server
when
> sending only the LINK to a report BUT NOT if I attach the report to the
> e-mail in say excel or xml format.
> I have tested using a command line SMTP utility and have been able to send
> both a plain text message and a plain text message wth an attachment so I
> know the Mail server will accept it.
> I'll include a copy of the log file from when the error occurs.
> Anyone out there have something for me to check? I have done my research
on
> the web but not been able to find any solutions.
> Travis
>
> Here's the error from the log... I can't make much of it... Other than
the
> part that says "more details in the log files -->please see the log
> files..." :-)
> ReportingServicesService!emailextension!728!07/29/2004-07:32:05:: Error
> sending email.
> Microsoft.ReportingServices.Diagnostics.Utilities.RSException: The Report
> Server has encountered a configuration error; more details in the log
> files -->
>
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorEx
> ception: The Report Server has encountered a configuration error; more
> details in the log files
> at
>
Microsoft.ReportingServices.Authorization.Native.GetAuthzContextForUser(IntP
> tr userSid)
> at Microsoft.ReportingServices.Authorization.Native.IsAdmin(String
> userName)
> at
>
Microsoft.ReportingServices.Authorization.WindowsAuthorization.IsAdmin(Strin
> g userName, IntPtr userToken)
> at
>
Microsoft.ReportingServices.Authorization.WindowsAuthorization.CheckAccess(S
> tring userName, IntPtr userToken, Byte[] secDesc, ReportOperation
> requiredOperation)
> at Microsoft.ReportingServices.Library.Security.CheckAccess(ItemType
> catItemType, Byte[] secDesc, ReportOperation rptOper)
> at
>
Microsoft.ReportingServices.Library.RSService._GetReportParameterDefinitionF
> romCatalog(CatalogItemContext reportContext, String historyID, Boolean
> forRendering, Guid& reportID, Int32& executionOption, String&
> savedParametersXml, ReportSnapshot& compiledDefinition, ReportSnapshot&
> snapshotData, Guid& linkID, DateTime& historyDate)
> at
> Microsoft.ReportingServices.Library.RSService._GetReportParameters(String
> report, String historyID, Boolean forRendering, NameValueCollection
values,
> DatasourceCredentialsCollection credentials)
> at
>
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(Catalog
> ItemContext reportContext, ClientRequest session, Warning[]& warnings,
> ParameterInfoCollection& effectiveParameters)
> at
>
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
> reportContext, ClientRequest session, Warning[]& warnings,
> ParameterInfoCollection& effectiveParameters, String[]&
> secondaryStreamNames)
> at
> Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
> at
>
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
> -- End of inner exception stack trace --
> at
>
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
> at
>
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RS
> Service rs, CatalogItemContext reportContext, ClientRequest session,
> JobTypeEnum type, Warning[]& warnings, ParameterInfoCollection&
> effectiveParameters, String[]& secondaryStreamNames)
> at Microsoft.ReportingServices.Library.ReportImpl.Render(String
> renderFormat, String deviceInfo)
> at
>
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.ConstructMes
> sageBody(IMessage message, Notification notification, SubscriptionData
data)
> at
>
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.CreateMessag
> e(Notification notification)
> at
>
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Noti
> fication notification)
>
>|||Bruce,
It's MS Exchange 2000
Thanks
TR
> Is this with Lotus? I know there are some problems with Lotus discussed on
> this newsgroup.
> Bruce L-C|||Hi Travis,
Are you using the local \Pickup folder for your SMTP service? There
are several threads in this group discussing how to set this more
efficient delivery method up. There's a setting in the
RSReportServer.config file (in the \ReportServer folder of your
default SRS installation path) that will let you specify the path to
the local SMTP pickup folder. Search that config file for "SMTP" and
check the other postings in this group that have more detail on how to
change those settings.
If you already have the Pickup folder specified, you may have to
change the SMTP Delivery properties via the IIS snap-in. From the
Delivery tab, click the Advanced button, then put the IP address [in
square brackets] of your Exchange server in the "Smart host" box. In
addition, you may have to un-check the "Attempt direct delivery"
option for this to work, as I did.
-- Ian
"REM7600" <rem7600@.hotmail.com> wrote in message news:<OibDe9JeEHA.996@.TK2MSFTNGP12.phx.gbl>...
> I can configure a working subscription to e-mail through my SMTP server when
> sending only the LINK to a report BUT NOT if I attach the report to the
> e-mail in say excel or xml format.
> I have tested using a command line SMTP utility and have been able to send
> both a plain text message and a plain text message wth an attachment so I
> know the Mail server will accept it.
> I'll include a copy of the log file from when the error occurs.
> Anyone out there have something for me to check? I have done my research on
> the web but not been able to find any solutions.
> Travis|||Thanks... I'm out of office for a week. But I'll re-read when I get back
in and give it a whirl.
TR
> Are you using the local \Pickup folder for your SMTP service? There
> are several threads in this group discussing how to set this more
> efficient delivery method up. There's a setting in the
> RSReportServer.config file (in the \ReportServer folder of your
> default SRS installation path) that will let you specify the path to
> the local SMTP pickup folder. Search that config file for "SMTP" and
> check the other postings in this group that have more detail on how to
> change those settings.
> If you already have the Pickup folder specified, you may have to
> change the SMTP Delivery properties via the IIS snap-in. From the
> Delivery tab, click the Advanced button, then put the IP address [in
> square brackets] of your Exchange server in the "Smart host" box. In
> addition, you may have to un-check the "Attempt direct delivery"
> option for this to work, as I did.
> -- Ian
>
> "REM7600" <rem7600@.hotmail.com> wrote in message
news:<OibDe9JeEHA.996@.TK2MSFTNGP12.phx.gbl>...
> > I can configure a working subscription to e-mail through my SMTP server
when
> > sending only the LINK to a report BUT NOT if I attach the report to the
> > e-mail in say excel or xml format.
> >
> > I have tested using a command line SMTP utility and have been able to
send
> > both a plain text message and a plain text message wth an attachment so
I
> > know the Mail server will accept it.
> >
> > I'll include a copy of the log file from when the error occurs.
> >
> > Anyone out there have something for me to check? I have done my
research on
> > the web but not been able to find any solutions.
> >
> > Travis

Reportviewer working over ssl(fortress) using Firefox, but not over ssl using IE

Hello,

I have a local report - using the reportviewer control - being displayed to internet users over an ssl connection (Fortress). The report works find in Safari and Firefox web browsers, but does not display in I.E.

What happens is the reportviewer acts like it is going to display the report properly, but as soon as the "Report is being Generated" message ends, the report viewer control tool bar drops to the bottom of the page effectively not showing the report. I can click export on the tool bar - that works as expected and allows me to download the report as an excel or pdf file, but inside I.E. I cannot see the report.

Any suggestions?

Thanks!!!!!!!!!!

I solved the problem. The issue was the reportviewer control was embedded inside an aspx table cell. In the process of troubleshooting, I deleted the aspx table, so the control was just inside an aspx panel. Deleting the table allowed the reportviewer to render properly over ssl.

What made the hard to figure was it worked great over plain http with IE, but not https(ssl). Ironically, the viewer worked great over ssl when using firefox.

Cheers!