Resetting the pagenumber at new every group

I need to print multi-paged invoices. Therefore I'd like to
reset the page number to 1 whenever the customer changes
(i.e. pagenumbering on group-level). The whole print-job
encompasses hundreds of pages.
How can I do this?This cannot be done in the current release.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Daniel Aebi" <maus2001@.bluewin.ch> wrote in message
news:Ompzea5eEHA.2544@.TK2MSFTNGP10.phx.gbl...
> I need to print multi-paged invoices. Therefore I'd like to
> reset the page number to 1 whenever the customer changes
> (i.e. pagenumbering on group-level). The whole print-job
> encompasses hundreds of pages.
> How can I do this?
>
>

Resetting Page numbers on each Group

Hi
I have designed a report on Invoice details and grouped by Invoice Numbers
and also applied page break on this group. I need to reset page numbers on
each Invoice number.
Is this possible?
Anyone know how to do this?
I am using SQL Server 2000 Reporting Services.
Thnx
DimpuHi Dimpu,
Not supported in RS 2000, but there is a workaround using a little code
- check out Chris Hay's RS Sleazy Hacks BLOG entry at
http://blogs.msdn.com/chrishays/archive/2006/01/05/ResetPageNumberOnGroup.aspx
It works great!
MattA
www.reportarchitex.com|||Hi Matt
It worked! Many thnx for your valuable information.
Regards
dimpu
"MattA" wrote:
> Hi Dimpu,
> Not supported in RS 2000, but there is a workaround using a little code
> - check out Chris Hay's RS Sleazy Hacks BLOG entry at
> http://blogs.msdn.com/chrishays/archive/2006/01/05/ResetPageNumberOnGroup.aspx
>
> It works great!
> MattA
> www.reportarchitex.com
>

Resetting Page Number in SQL Reporting Services

Does anyone know of a way to reset page numbering when the group changes?
Thanks,
DennyDenny:
Try this blog that I found on this same group.
http://blogs.msdn.com/bwelcker/archive/2005/05/19/420046.aspx
Sher
Denny wrote:
> Does anyone know of a way to reset page numbering when the group changes?
> Thanks,
> Denny

Resetting page counter

I have a page counter in the page footer. How can I reset it once a whole group has been shown? I know its in the section expert, but what formula do I use?
ThanksOne way to do this is to format the section to add a new page after each group and go into the formula and type <> OnLastRecord. This will give a new page after each record (except for the last page). You can then use a running total that is evaluated on change of that group. If you show the running total at the bottom of the Crystal Report (http://www.shelko.com) page, you will get the effect that you are looking for.sql

Reset Total page number in a group

I know how to reset the page numbers with each group, but how do you reset the total page number within each group.

EX. Code for page of total pages

="Page " & Globals.PageNumber & " of " & Globals.TotalPages

EX. Code to reset within a group

Custom Code:

Shared offset as Integer

Shared currentgroup as object

Public Function GetGroupPageNumber(group as Object, pagenumber as Integer) as Object

If not (group = currentgroup)

offset = pagenumber - 1

currentgroup= group

end if

return pagenumber - offset

end function

=Code.GetGroupPageNumber(ReportItems!Category.Value(grouping),Globals!PageNumber)

What I need is code for a combination of the two...to display code for page of total pages that resets within a group.

Any help is greatly appreciated.

Thanks!

Hello,

I am facing the exact same need. Anyone solve this?

Reset Total page number in a group

I know how to reset the page numbers with each group, but how do you reset the total page number within each group.

EX. Code for page of total pages

="Page " & Globals.PageNumber & " of " & Globals.TotalPages

EX. Code to reset within a group

Custom Code:

Shared offset as Integer

Shared currentgroup as object

Public Function GetGroupPageNumber(group as Object, pagenumber as Integer) as Object

If not (group = currentgroup)

offset = pagenumber - 1

currentgroup= group

end if

return pagenumber - offset

end function

=Code.GetGroupPageNumber(ReportItems!Category.Value(grouping),Globals!PageNumber)

What I need is code for a combination of the two...to display code for page of total pages that resets within a group.

Any help is greatly appreciated.

Thanks!

Hello,

I am facing the exact same need. Anyone solve this?

Reset PageNumber On Group Change

I need to reset the page number when a group changes. The group is controlled
in a list. PageNumber works well for overall numbering, but not inside the
group, furthermore, I cannot access the current pagenumber from inside the
group.
I am using RS 2005.Add this to your report code:
Shared offset as Integer
Public Function GrpPN(reset As Boolean, pagenumber As Integer) As Integer
If reset
offset = pagenumber - 1
End if
Return pagenumber - offset
End Function
Then place a text box in the page footer with this expression - assuming you
have the group set with page break at end and have labeled your Group cell.
="Page " & Code.GrpPN(Not(ReportItems!txtGroup.Value Is Nothing),
Globals!PageNumber)
Hope that helps (this was in 2000)
--
Allison
"Rick" wrote:
> I need to reset the page number when a group changes. The group is controlled
> in a list. PageNumber works well for overall numbering, but not inside the
> group, furthermore, I cannot access the current pagenumber from inside the
> group.
> I am using RS 2005.

Reset Page Numbering Total

I am wanting to reset the page number on the break of each group.
For example
Group A - 3 pages
Group B - 4 pages
So for group B on the 4th page it will start with 1, using the following
code this does this fine
Shared offset As Integer
Public Function GetPN(reset As Boolean, pagenumber As Integer) As Integer
If reset
offset = pagenumber - 1
End If
Return pagenumber - offset
End Function
I am wanting to do this for the total of the pages as well so for example
page 4 would show as 1 of 4 instead of 1 of 7
How do I get this working for the total pages as well as the individual pagesI have the same problem, did you come up with any solution?
"Mark Stanley" wrote:
> I am wanting to reset the page number on the break of each group.
> For example
> Group A - 3 pages
> Group B - 4 pages
> So for group B on the 4th page it will start with 1, using the following
> code this does this fine
> Shared offset As Integer
> Public Function GetPN(reset As Boolean, pagenumber As Integer) As Integer
> If reset
> offset = pagenumber - 1
> End If
> Return pagenumber - offset
> End Function
> I am wanting to do this for the total of the pages as well so for example
> page 4 would show as 1 of 4 instead of 1 of 7
> How do I get this working for the total pages as well as the individual pages|||Jim,
See my earlier posting in this thread.
It's not a simple solution but it might help.
Chris
JIM.H. wrote:
> I have the same problem, did you come up with any solution?
> "Mark Stanley" wrote:
> > I am wanting to reset the page number on the break of each group.
> >
> > For example
> >
> > Group A - 3 pages
> > Group B - 4 pages
> >
> > So for group B on the 4th page it will start with 1, using the
> > following code this does this fine
> >
> > Shared offset As Integer
> >
> > Public Function GetPN(reset As Boolean, pagenumber As Integer) As
> > Integer If reset
> > offset = pagenumber - 1
> > End If
> > Return pagenumber - offset
> > End Function
> >
> > I am wanting to do this for the total of the pages as well so for
> > example page 4 would show as 1 of 4 instead of 1 of 7
> >
> > How do I get this working for the total pages as well as the
> > individual pages|||Hi Chris,
Did you mean you attached something in the previous message, I am using web
interface and do not know how to see attachments.
"Chris McGuigan" wrote:
> Jim,
> See my earlier posting in this thread.
> It's not a simple solution but it might help.
> Chris
>
> JIM.H. wrote:
> > I have the same problem, did you come up with any solution?
> >
> > "Mark Stanley" wrote:
> >
> > > I am wanting to reset the page number on the break of each group.
> > >
> > > For example
> > >
> > > Group A - 3 pages
> > > Group B - 4 pages
> > >
> > > So for group B on the 4th page it will start with 1, using the
> > > following code this does this fine
> > >
> > > Shared offset As Integer
> > >
> > > Public Function GetPN(reset As Boolean, pagenumber As Integer) As
> > > Integer If reset
> > > offset = pagenumber - 1
> > > End If
> > > Return pagenumber - offset
> > > End Function
> > >
> > > I am wanting to do this for the total of the pages as well so for
> > > example page 4 would show as 1 of 4 instead of 1 of 7
> > >
> > > How do I get this working for the total pages as well as the
> > > individual pages
>|||Jim,
Here is the contents of the file;
----
Unfortunately, there's currently no good way to build a table of
contents.
And even adding defined execution order isn't going to help, since
pagination happens as a completely separate step from the rest of the
report. This is because you can request the same report with different
page
size/margin settings (and even an entirely different rendering target,
which
could radically change pagination). Under normal circumstances, we don't
want to force reexecution of the entire report just because you change
your
margins.
To handle table of contents type operations, we're going to (eventually)
have to implement an entirely new mode wherein we know that the body of
the
report contains references to page numbers, which will force the entire
report to reexecute if pagination information changes.
In the mean time, there's no general solution for the table of contents
problem.
But... Since you're doing this in a batch once a month, you may not
need a
fully general solution.
Without further ado, here's Sleazy Hack #792 ("Simulating Table of
Contents
in Batch Reporting Scenarios"):
1. Write a custom assembly containing a class that can write rows to a
TableOfContents table in your database.
Have shared methods for both initializing the TOC and writing a new
row
to the TOC.
Note: Don't forget to give it database permissions in the CAS file
when
you deploy it to your server.
See the documentation for details on custom assemblies.
2. In the Code section of the report, call the TOC initialization in
the
OnInit() event.
3. At the start of each section you want to appear in your TOC, put a
hidden textbox that contains the label you want to appear in your TOC.
4. In the page header, put a hidden textbox with something like the
following expression:
=Code.MyTOCClass.AddToTOC(Globals.PageNumber,ReportItems!Textbox1.Value
& ReportItems!Textbox2.Value & ReportItems!Textbox3.Value)
Texbox1, Textbox2 and Textbox3 are the names of the hidden textboxes
from step 3 (since only one will appear on any given page, the rest
will be
empty)
5. Add a dataset to your report which selects from the TableOfContents
table
6. Display the results of the TOC data set in a table at the end of
your
report*
7. Run the report twice. The first time will initialize the TOC. The
second time will use the values from the previous run.
* If you want it at the beginning, you'll either need to run the report
three times (the first to get some rows into the TOC table, the second
to
get the numbers populated correctly and the third to use those numbers)
or
you'll need to make sure the TOC table has the right number of rows to
begin
with (perhaps by not emptying it from the previous month) otherwise your
page numbers will be incorrect due to the TOC pushing things around.
This
isn't an issue if your TOC is only one page long and has PageBreakAtEnd,
however.
JIM.H. wrote:
> Hi Chris,
> Did you mean you attached something in the previous message, I am
> using web interface and do not know how to see attachments.
> "Chris McGuigan" wrote:
> > Jim,
> > See my earlier posting in this thread.
> > It's not a simple solution but it might help.
> >
> > Chris
> >
> >
> > JIM.H. wrote:
> >
> > > I have the same problem, did you come up with any solution?
> > >
> > > "Mark Stanley" wrote:
> > >
> > > > I am wanting to reset the page number on the break of each
> > > > group.
> > > >
> > > > For example
> > > >
> > > > Group A - 3 pages
> > > > Group B - 4 pages
> > > >
> > > > So for group B on the 4th page it will start with 1, using the
> > > > following code this does this fine
> > > >
> > > > Shared offset As Integer
> > > >
> > > > Public Function GetPN(reset As Boolean, pagenumber As Integer)
> > > > As Integer If reset
> > > > offset = pagenumber - 1
> > > > End If
> > > > Return pagenumber - offset
> > > > End Function
> > > >
> > > > I am wanting to do this for the total of the pages as well so
> > > > for example page 4 would show as 1 of 4 instead of 1 of 7
> > > >
> > > > How do I get this working for the total pages as well as the
> > > > individual pages
> >
> >|||Yes there was an attachment. You'll need a "proper" newsreader - I
recommend XanaNews, which can be downloaded at
http://www.wilsonc.demon.co.uk/d9xananews.htm
I've put the contents in another post for you.
Chris
JIM.H. wrote:
> Hi Chris,
> Did you mean you attached something in the previous message, I am
> using web interface and do not know how to see attachments.
> "Chris McGuigan" wrote:
> > Jim,
> > See my earlier posting in this thread.
> > It's not a simple solution but it might help.
> >
> > Chris
> >
> >
> > JIM.H. wrote:
> >
> > > I have the same problem, did you come up with any solution?
> > >
> > > "Mark Stanley" wrote:
> > >
> > > > I am wanting to reset the page number on the break of each
> > > > group.
> > > >
> > > > For example
> > > >
> > > > Group A - 3 pages
> > > > Group B - 4 pages
> > > >
> > > > So for group B on the 4th page it will start with 1, using the
> > > > following code this does this fine
> > > >
> > > > Shared offset As Integer
> > > >
> > > > Public Function GetPN(reset As Boolean, pagenumber As Integer)
> > > > As Integer If reset
> > > > offset = pagenumber - 1
> > > > End If
> > > > Return pagenumber - offset
> > > > End Function
> > > >
> > > > I am wanting to do this for the total of the pages as well so
> > > > for example page 4 would show as 1 of 4 instead of 1 of 7
> > > >
> > > > How do I get this working for the total pages as well as the
> > > > individual pages
> >
> >

Reset page # after a "specific" group

http://blogs.msdn.com/chrishays/archive/2006/01/05/ResetPageNumberOnGroup.aspx
the above custom code works when reset is needed on change of each
group, How do I modify this code to reset pagenumber after a particular
group say "group 3".Shared offset as Integer
Shared currentgroup as Object
Public Function GetGroupPageNumber(group as Object, pagenumber as
Integer) as Object
If Not (group = currentgroup)
offset = pagenumber - 1
End If
Return pagenumber - offset
End Function
The above code works!
googlerptgsvcs wrote:
> http://blogs.msdn.com/chrishays/archive/2006/01/05/ResetPageNumberOnGroup.aspx
> the above custom code works when reset is needed on change of each
> group, How do I modify this code to reset pagenumber after a particular
> group say "group 3".sql

Reseeding to a value that exists

Hi group,
If I have a table, SomeTable like so:
(SQL Server 2000)
[ID] [int] IDENTITY (1, 1) NOT NULL
[SomeValue] [varchar] (50)
ID SomeValue
1 something
2 something other
3 something else
And I then execute:
DBCC CHECKIDENT (SomeTable, RESEED, 2)
And I then execute:
INSERT INTO SomeTable VALUES('something more')
This will leave me with two values with the same ID of 3. What I expected
was that I'd receive an error when trying to reseed at 2, when 2 already
exists, but this is not the case. Is this a DB option that I do not have
set that allows duplicate identities, and/or is there something that I can
change so that if I am ever stupid enough to reseed at an existing value,
that I will be denied with an error?
Thank you,
Ray at workAny of the following will guarantee uniqueness:
1) primary key constraint
2) unique constraint
3) unique index
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:%23Z7Db0gqDHA.1444@.tk2msftngp13.phx.gbl...
> Hi group,
> If I have a table, SomeTable like so:
> (SQL Server 2000)
> [ID] [int] IDENTITY (1, 1) NOT NULL
> [SomeValue] [varchar] (50)
> ID SomeValue
> 1 something
> 2 something other
> 3 something else
> And I then execute:
> DBCC CHECKIDENT (SomeTable, RESEED, 2)
> And I then execute:
> INSERT INTO SomeTable VALUES('something more')
> This will leave me with two values with the same ID of 3. What I
expected
> was that I'd receive an error when trying to reseed at 2, when 2
already
> exists, but this is not the case. Is this a DB option that I do not
have
> set that allows duplicate identities, and/or is there something that I
can
> change so that if I am ever stupid enough to reseed at an existing
value,
> that I will be denied with an error?
> Thank you,
> Ray at work
>
>

Repost: sql7 maintenance jobs hang

Hi group,
the following has been posted and did not receive any response. In hope to
get some leads I am reposting it here.
we have a sql7 server,
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86)
May 29 2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
For a while now, it has been seeing hanging db maintenance jobs. The jobs
are initiated, but never goes beyond that -- the maintenance task is not
done before the hanging of the job. EM shows that the jobs are running but
loses the ability of managing them. One needs to end the processes with the
windows task manager. When manually initiated, the jobs can run through.
The jobs are not necessarily the same(different days can have different jobs
experiencing the problem, some can be the same), but all came from db
maintenance plan. A job hung yesterday may not hang today. Recreating the
db maint plan does not solve the problem.
Any suggestions are appreciated.
QuentinQuentin
Are you using the repair minor errors option? That tends
to cause problems in maint plans.
Any other problems on the server, I had a similar problem
and it was being caused by another process clashing with
the checkpoint process, but that eventually killed the
whole system.
Regards
John|||John,
thanks for the response.
The repair minor errors option is not being used. We are aware of that
problem.
We are trying to identify other problems on the server but came out empty
handed so far.
Quentin
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
news:003201c3a86e$ccafad40$a301280a@.phx.gbl...
> Quentin
> Are you using the repair minor errors option? That tends
> to cause problems in maint plans.
> Any other problems on the server, I had a similar problem
> and it was being caused by another process clashing with
> the checkpoint process, but that eventually killed the
> whole system.
> Regards
> John

Repost: Polling the network for SQL servers, including named instances

Hi group,
I am trying to get a list of SQL Server installations running on the
network. I tried to use sqlcmd -L (or osql -L). However, when I do this
from my box the named instances do not show up, whereas when it is run from
a server box those instances do get listed.
What is required to make the command return the full list? Is there other
(simple) way to do it?
Thanks.
QuentinHi Quentin
Check out SQLPing or SQLRecon at
http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx
John
"Quentin Ran" wrote:
> Hi group,
> I am trying to get a list of SQL Server installations running on the
> network. I tried to use sqlcmd -L (or osql -L). However, when I do this
> from my box the named instances do not show up, whereas when it is run from
> a server box those instances do get listed.
> What is required to make the command return the full list? Is there other
> (simple) way to do it?
> Thanks.
> Quentin
>
>

Repost: Databases with different collation

Let me try this one last time.
Hi group,
we have databases now residing on different servers or instances with
different collations. We are considering bringing the databases on the same
server, keeping their respective collation.
Some of our applications access databases with different collations. We
have been using linked server and specifying "Use remote collation" (or
not). But when putting the DBs with different collation on the same server,
you loss this feature. To go over all the applications and specify the
collation on the query statement level is of course too big a task. Is
there a way to do such a blanket declaration on the database level?
Or any other way of doing this efficiently?
Your responses are greatly appreciated.
QuentinQuentin,
I've been thinking about your problem and the only somewhat-feasible
solution I can think of is to create new databases with your server's
default collation and then use DTS to transfer the old databases in,
ignoring collation (I believe that's an option?)
This would, of course, be slower than just doing a backup/restore, but you'd
only have to do it once and it will definitely fix your issues.
"Quentin Ran" <ab@.who.com> wrote in message
news:uQvtBxdaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Let me try this one last time.
> Hi group,
> we have databases now residing on different servers or instances with
> different collations. We are considering bringing the databases on the
same
> server, keeping their respective collation.
> Some of our applications access databases with different collations. We
> have been using linked server and specifying "Use remote collation" (or
> not). But when putting the DBs with different collation on the same
server,
> you loss this feature. To go over all the applications and specify the
> collation on the query statement level is of course too big a task. Is
> there a way to do such a blanket declaration on the database level?
> Or any other way of doing this efficiently?
> Your responses are greatly appreciated.
> Quentin
>
>
>|||Thanks Adam.
I believe it still does not work. Say I have db1 with case sensetive, db2
case insensitive. The server default is case sensetive. I can put whatever
data into DB2 on the case sensetive server and make DB2 case sensetive.
However, my application dictates that a query run from db1 accessing db2
requires case insensitive -- currently achieved by linked server with use
remote collation -- and the requirement can not be satisfied.
Nevertheless, thanks again for keeping thinking about the problem.
Quentin
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#NRk40daEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Quentin,
> I've been thinking about your problem and the only somewhat-feasible
> solution I can think of is to create new databases with your server's
> default collation and then use DTS to transfer the old databases in,
> ignoring collation (I believe that's an option?)
> This would, of course, be slower than just doing a backup/restore, but
you'd
> only have to do it once and it will definitely fix your issues.
>
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uQvtBxdaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> same
> server,
>|||"Quentin Ran" <ab@.who.com> wrote in message
news:udZ1GXfaEHA.712@.TK2MSFTNGP11.phx.gbl...
> I believe it still does not work. Say I have db1 with case sensetive, db2
> case insensitive. The server default is case sensetive. I can put
whatever
> data into DB2 on the case sensetive server and make DB2 case sensetive.
> However, my application dictates that a query run from db1 accessing db2
> requires case insensitive -- currently achieved by linked server with use
> remote collation -- and the requirement can not be satisfied.
How many databases are already running on the server? Can you re-build
it and make it CI? CS is a hassle to work with anyway
At least, if you can re-collate most of the stuff, you'll only have to
mess with the queries that require CI... So that would be a step in the
right direction!|||
> How many databases are already running on the server? Can you
re-build
> it and make it CI? CS is a hassle to work with anyway
That's exactly the problem -- we are developing a new wide reaching
application that is case sensitive, required by the software vendor --
Peoplesoft. And it has interaction with our existing DBs that are CI.

Repost: Databases with different collation

Let me try this one last time.
Hi group,
we have databases now residing on different servers or instances with
different collations. We are considering bringing the databases on the same
server, keeping their respective collation.
Some of our applications access databases with different collations. We
have been using linked server and specifying "Use remote collation" (or
not). But when putting the DBs with different collation on the same server,
you loss this feature. To go over all the applications and specify the
collation on the query statement level is of course too big a task. Is
there a way to do such a blanket declaration on the database level?
Or any other way of doing this efficiently?
Your responses are greatly appreciated.
Quentin
Quentin,
I've been thinking about your problem and the only somewhat-feasible
solution I can think of is to create new databases with your server's
default collation and then use DTS to transfer the old databases in,
ignoring collation (I believe that's an option?)
This would, of course, be slower than just doing a backup/restore, but you'd
only have to do it once and it will definitely fix your issues.
"Quentin Ran" <ab@.who.com> wrote in message
news:uQvtBxdaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Let me try this one last time.
> Hi group,
> we have databases now residing on different servers or instances with
> different collations. We are considering bringing the databases on the
same
> server, keeping their respective collation.
> Some of our applications access databases with different collations. We
> have been using linked server and specifying "Use remote collation" (or
> not). But when putting the DBs with different collation on the same
server,
> you loss this feature. To go over all the applications and specify the
> collation on the query statement level is of course too big a task. Is
> there a way to do such a blanket declaration on the database level?
> Or any other way of doing this efficiently?
> Your responses are greatly appreciated.
> Quentin
>
>
>
|||Thanks Adam.
I believe it still does not work. Say I have db1 with case sensetive, db2
case insensitive. The server default is case sensetive. I can put whatever
data into db2 on the case sensetive server and make db2 case sensetive.
However, my application dictates that a query run from db1 accessing db2
requires case insensitive -- currently achieved by linked server with use
remote collation -- and the requirement can not be satisfied.
Nevertheless, thanks again for keeping thinking about the problem.
Quentin
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#NRk40daEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Quentin,
> I've been thinking about your problem and the only somewhat-feasible
> solution I can think of is to create new databases with your server's
> default collation and then use DTS to transfer the old databases in,
> ignoring collation (I believe that's an option?)
> This would, of course, be slower than just doing a backup/restore, but
you'd
> only have to do it once and it will definitely fix your issues.
>
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uQvtBxdaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> same
> server,
>
|||"Quentin Ran" <ab@.who.com> wrote in message
news:udZ1GXfaEHA.712@.TK2MSFTNGP11.phx.gbl...
> I believe it still does not work. Say I have db1 with case sensetive, db2
> case insensitive. The server default is case sensetive. I can put
whatever
> data into db2 on the case sensetive server and make db2 case sensetive.
> However, my application dictates that a query run from db1 accessing db2
> requires case insensitive -- currently achieved by linked server with use
> remote collation -- and the requirement can not be satisfied.
How many databases are already running on the server? Can you re-build
it and make it CI? CS is a hassle to work with anyway
At least, if you can re-collate most of the stuff, you'll only have to
mess with the queries that require CI... So that would be a step in the
right direction!
|||
> How many databases are already running on the server? Can you
re-build
> it and make it CI? CS is a hassle to work with anyway
That's exactly the problem -- we are developing a new wide reaching
application that is case sensitive, required by the software vendor --
Peoplesoft. And it has interaction with our existing DBs that are CI.

Repost: Databases with different collation

Hi group,
we have databases now residing on different servers or instances with
different collations. We are considering bringing the databases on the same
server, keeping their respective collation.
Some of our applications access databases with different collations. We
have been using linked server and specifying "Use remote collation" (or
not). But when putting the DBs with different collation on the same server,
you loss this feature. To go over all the applications and specify the
collation on the query statement level is of course too big a task. Is
there a way to do such a blanket declaration on the database level?
Or any other way of doing this efficiently?
Your responses are greatly appreciated.
QuentinYes, use unicode.
The problem is you can only have one collation per
database table, so combining two will cause a problem. The
answer is to use unicode ie ncar, nvarhar, ntext which is
collation independant.
J
>--Original Message--
>Hi group,
>we have databases now residing on different servers or
instances with
>different collations. We are considering bringing the
databases on the same
>server, keeping their respective collation.
>Some of our applications access databases with different
collations. We
>have been using linked server and specifying "Use remote
collation" (or
>not). But when putting the DBs with different collation
on the same server,
>you loss this feature. To go over all the applications
and specify the
>collation on the query statement level is of course too
big a task. Is
>there a way to do such a blanket declaration on the
database level?
>Or any other way of doing this efficiently?
>Your responses are greatly appreciated.
>Quentin
>
>
>.
>|||"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:2a99d01c46820$c3396fe0$a601280a@.phx.gbl...
> Yes, use unicode.
> The problem is you can only have one collation per
> database table, so combining two will cause a problem. The
> answer is to use unicode ie ncar, nvarhar, ntext which is
> collation independant.
Julie,
Are you suggesting that unicode columns ignore collation settings? That
is not true:
create table #un1 (a nvarchar(20) collate sql_latin1_general_cp1_ci_as)
create table #un2 (a nvarchar(20) collate sql_latin1_general_cp1_cs_as)
select * from #un1
union
select * from #un2
-- Server: Msg 446, Level 16, State 9, Line 5
-- Cannot resolve collation conflict for UNION operation.
If that wasn't what you meant, please explain in more detail.|||Sorry thats not what I meant. from the description two
different databases are being combined, the point I was
trying (unsuccessfully obviously) was that adding the data
from collation table x to table with collation y will
cause problems, so the best way is to convert them first
to unicode, then combine them.
J
>--Original Message--
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2a99d01c46820$c3396fe0$a601280a@.phx.gbl...
>> Yes, use unicode.
>> The problem is you can only have one collation per
>> database table, so combining two will cause a problem.
The
>> answer is to use unicode ie ncar, nvarhar, ntext which
is
>> collation independant.
> Julie,
> Are you suggesting that unicode columns ignore
collation settings? That
>is not true:
>
>create table #un1 (a nvarchar(20) collate
sql_latin1_general_cp1_ci_as)
>create table #un2 (a nvarchar(20) collate
sql_latin1_general_cp1_cs_as)
>select * from #un1
>union
>select * from #un2
>-- Server: Msg 446, Level 16, State 9, Line 5
>-- Cannot resolve collation conflict for UNION operation.
>
> If that wasn't what you meant, please explain in more
detail.
>
>.
>|||Julie/Adam,
Thanks for the responses.
Using unicode (or whatever casting) does not solve the problem. The problem
is that there are existing applications that use the linked server where
with the "use remote collation", you have the whole database covered, but
with any casting, you have only the column covered. You will have to go
over the entire code to do the change. What I am trying to find is rather a
solution that do such a blanket change so you do not need to worry where a
change is missed -- let alone to do all the changes.
Again thanks for the discussion. Any further comments are well come.
Quentin
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:2bdab01c4682d$cf07d2f0$a301280a@.phx.gbl...
> Sorry thats not what I meant. from the description two
> different databases are being combined, the point I was
> trying (unsuccessfully obviously) was that adding the data
> from collation table x to table with collation y will
> cause problems, so the best way is to convert them first
> to unicode, then combine them.
> J
> >--Original Message--
> >
> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2a99d01c46820$c3396fe0$a601280a@.phx.gbl...
> >> Yes, use unicode.
> >>
> >> The problem is you can only have one collation per
> >> database table, so combining two will cause a problem.
> The
> >> answer is to use unicode ie ncar, nvarhar, ntext which
> is
> >> collation independant.
> >
> > Julie,
> >
> > Are you suggesting that unicode columns ignore
> collation settings? That
> >is not true:
> >
> >
> >create table #un1 (a nvarchar(20) collate
> sql_latin1_general_cp1_ci_as)
> >
> >create table #un2 (a nvarchar(20) collate
> sql_latin1_general_cp1_cs_as)
> >
> >select * from #un1
> >union
> >select * from #un2
> >
> >-- Server: Msg 446, Level 16, State 9, Line 5
> >-- Cannot resolve collation conflict for UNION operation.
> >
> >
> > If that wasn't what you meant, please explain in more
> detail.
> >
> >
> >.
> >

Repost: Databases with different collation

Let me try this one last time.
Hi group,
we have databases now residing on different servers or instances with
different collations. We are considering bringing the databases on the same
server, keeping their respective collation.
Some of our applications access databases with different collations. We
have been using linked server and specifying "Use remote collation" (or
not). But when putting the DBs with different collation on the same server,
you loss this feature. To go over all the applications and specify the
collation on the query statement level is of course too big a task. Is
there a way to do such a blanket declaration on the database level?
Or any other way of doing this efficiently?
Your responses are greatly appreciated.
QuentinQuentin,
I've been thinking about your problem and the only somewhat-feasible
solution I can think of is to create new databases with your server's
default collation and then use DTS to transfer the old databases in,
ignoring collation (I believe that's an option?)
This would, of course, be slower than just doing a backup/restore, but you'd
only have to do it once and it will definitely fix your issues.
"Quentin Ran" <ab@.who.com> wrote in message
news:uQvtBxdaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Let me try this one last time.
> Hi group,
> we have databases now residing on different servers or instances with
> different collations. We are considering bringing the databases on the
same
> server, keeping their respective collation.
> Some of our applications access databases with different collations. We
> have been using linked server and specifying "Use remote collation" (or
> not). But when putting the DBs with different collation on the same
server,
> you loss this feature. To go over all the applications and specify the
> collation on the query statement level is of course too big a task. Is
> there a way to do such a blanket declaration on the database level?
> Or any other way of doing this efficiently?
> Your responses are greatly appreciated.
> Quentin
>
>
>|||Thanks Adam.
I believe it still does not work. Say I have db1 with case sensetive, db2
case insensitive. The server default is case sensetive. I can put whatever
data into db2 on the case sensetive server and make db2 case sensetive.
However, my application dictates that a query run from db1 accessing db2
requires case insensitive -- currently achieved by linked server with use
remote collation -- and the requirement can not be satisfied.
Nevertheless, thanks again for keeping thinking about the problem.
Quentin
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#NRk40daEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Quentin,
> I've been thinking about your problem and the only somewhat-feasible
> solution I can think of is to create new databases with your server's
> default collation and then use DTS to transfer the old databases in,
> ignoring collation (I believe that's an option?)
> This would, of course, be slower than just doing a backup/restore, but
you'd
> only have to do it once and it will definitely fix your issues.
>
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uQvtBxdaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> > Let me try this one last time.
> >
> > Hi group,
> >
> > we have databases now residing on different servers or instances with
> > different collations. We are considering bringing the databases on the
> same
> > server, keeping their respective collation.
> >
> > Some of our applications access databases with different collations. We
> > have been using linked server and specifying "Use remote collation" (or
> > not). But when putting the DBs with different collation on the same
> server,
> > you loss this feature. To go over all the applications and specify the
> > collation on the query statement level is of course too big a task. Is
> > there a way to do such a blanket declaration on the database level?
> > Or any other way of doing this efficiently?
> >
> > Your responses are greatly appreciated.
> >
> > Quentin
> >
> >
> >
> >
> >
> >
>|||"Quentin Ran" <ab@.who.com> wrote in message
news:udZ1GXfaEHA.712@.TK2MSFTNGP11.phx.gbl...
> I believe it still does not work. Say I have db1 with case sensetive, db2
> case insensitive. The server default is case sensetive. I can put
whatever
> data into db2 on the case sensetive server and make db2 case sensetive.
> However, my application dictates that a query run from db1 accessing db2
> requires case insensitive -- currently achieved by linked server with use
> remote collation -- and the requirement can not be satisfied.
How many databases are already running on the server? Can you re-build
it and make it CI? CS is a hassle to work with anyway :)
At least, if you can re-collate most of the stuff, you'll only have to
mess with the queries that require CI... So that would be a step in the
right direction!|||> How many databases are already running on the server? Can you
re-build
> it and make it CI? CS is a hassle to work with anyway :)
That's exactly the problem -- we are developing a new wide reaching
application that is case sensitive, required by the software vendor --
Peoplesoft. And it has interaction with our existing DBs that are CI.

Repost - Linked server security - Help!

I have 2 questions.
1. Can I map a local NT group (not a user) to a remote login (SQL account)?
(local server is SQL7, remote server is SQL 2K)
2. I kept add new login mappings from the security tab. Then I clicked OK
and closed it. No error messages. Next time when I opened the tab, it only
shows the first mapping I did. The rest disappears. I used
sp_helplinkedsrvlogin, which still does NOT show the rest. Is this a bug?
Can anyone help?
Thank you!
Hi
Is this a SQL 7 instance linked to SQL 2000 or vice versa? Which version of
EM are you using to do this?
Can you use Query Analyser (sp_addlinkedsrvlogin) to add this login?
John
"Min" <Min@.discussions.microsoft.com> wrote in message
news:97B19B1E-B858-4433-8642-F9DE841DB341@.microsoft.com...
>I have 2 questions.
> 1. Can I map a local NT group (not a user) to a remote login (SQL
> account)?
> (local server is SQL7, remote server is SQL 2K)
> 2. I kept add new login mappings from the security tab. Then I clicked OK
> and closed it. No error messages. Next time when I opened the tab, it only
> shows the first mapping I did. The rest disappears. I used
> sp_helplinkedsrvlogin, which still does NOT show the rest. Is this a bug?
> Can anyone help?
> Thank you!
>
|||Local server is sql 7. linked server is sql 2k. I checked the version of the
EM, which is 8.0
"John Bell" wrote:

> Hi
> Is this a SQL 7 instance linked to SQL 2000 or vice versa? Which version of
> EM are you using to do this?
> Can you use Query Analyser (sp_addlinkedsrvlogin) to add this login?
> John
> "Min" <Min@.discussions.microsoft.com> wrote in message
> news:97B19B1E-B858-4433-8642-F9DE841DB341@.microsoft.com...
>
>
|||Yes. I can use sp_addlinkedsrvlogin to add login, although EM still did not
show it. If I add thru EM, it will only show the first mapping. Even
sp_helplinkedsrvlogin will not show it. I have done test to prove that the
mapping was added. However neither thru EM or SP, I can see it.

> Can you use Query Analyser (sp_addlinkedsrvlogin) to add this login?
> John
> "Min" <Min@.discussions.microsoft.com> wrote in message
> news:97B19B1E-B858-4433-8642-F9DE841DB341@.microsoft.com...
>
>
|||Hi
You may want to check on a system that has the SQL 7 tools.
John
"Min" <Min@.discussions.microsoft.com> wrote in message
news:7290F8F4-58AD-4FFC-A4B6-E9600767D23C@.microsoft.com...[vbcol=seagreen]
> Yes. I can use sp_addlinkedsrvlogin to add login, although EM still did
> not
> show it. If I add thru EM, it will only show the first mapping. Even
> sp_helplinkedsrvlogin will not show it. I have done test to prove that the
> mapping was added. However neither thru EM or SP, I can see it.
|||Thanks! I will check. Do you know the asnwer to my first question?
1. Can I map a local NT group (not a user) to a remote login (SQL account)?
(local server is SQL7, remote server is SQL 2K)
Thanks again.
"John Bell" wrote:

> Hi
> You may want to check on a system that has the SQL 7 tools.
> John
> "Min" <Min@.discussions.microsoft.com> wrote in message
> news:7290F8F4-58AD-4FFC-A4B6-E9600767D23C@.microsoft.com...
>
>
|||Hi
You should be able to do this, but I don't have a system I can check it on!
In general my preference is to use NT groups wherever possible then when a
new user comes along it is part of the NT administration to set them up
(without touching the databases!).
John
"Min" <Min@.discussions.microsoft.com> wrote in message
news:80ACE6F9-9F60-471C-8E2C-A1A2D3E47534@.microsoft.com...[vbcol=seagreen]
> Thanks! I will check. Do you know the asnwer to my first question?
> 1. Can I map a local NT group (not a user) to a remote login (SQL
> account)?
> (local server is SQL7, remote server is SQL 2K)
> Thanks again.
> "John Bell" wrote:
|||Thanks for the reply. I tried. It was mapped OK but did not work. A user
belongs to the NT group connects to the remote server as the generic account
I have (which is for everyone not in the mapping table). After couple tests,
it seems that I can only map a NT user to a remote SQL account.
"John Bell" wrote:

> Hi
> You should be able to do this, but I don't have a system I can check it on!
> In general my preference is to use NT groups wherever possible then when a
> new user comes along it is part of the NT administration to set them up
> (without touching the databases!).
> John
> "Min" <Min@.discussions.microsoft.com> wrote in message
> news:80ACE6F9-9F60-471C-8E2C-A1A2D3E47534@.microsoft.com...
>
>

Repost - Linked server security - Help!

I have 2 questions.
1. Can I map a local NT group (not a user) to a remote login (SQL account)?
(local server is SQL7, remote server is SQL 2K)
2. I kept add new login mappings from the security tab. Then I clicked OK
and closed it. No error messages. Next time when I opened the tab, it only
shows the first mapping I did. The rest disappears. I used
sp_helplinkedsrvlogin, which still does NOT show the rest. Is this a bug?
Can anyone help?
Thank you!Hi
Is this a SQL 7 instance linked to SQL 2000 or vice versa? Which version of
EM are you using to do this?
Can you use Query Analyser (sp_addlinkedsrvlogin) to add this login?
John
"Min" <Min@.discussions.microsoft.com> wrote in message
news:97B19B1E-B858-4433-8642-F9DE841DB341@.microsoft.com...
>I have 2 questions.
> 1. Can I map a local NT group (not a user) to a remote login (SQL
> account)?
> (local server is SQL7, remote server is SQL 2K)
> 2. I kept add new login mappings from the security tab. Then I clicked OK
> and closed it. No error messages. Next time when I opened the tab, it only
> shows the first mapping I did. The rest disappears. I used
> sp_helplinkedsrvlogin, which still does NOT show the rest. Is this a bug?
> Can anyone help?
> Thank you!
>|||Local server is sql 7. linked server is sql 2k. I checked the version of the
EM, which is 8.0
"John Bell" wrote:
> Hi
> Is this a SQL 7 instance linked to SQL 2000 or vice versa? Which version of
> EM are you using to do this?
> Can you use Query Analyser (sp_addlinkedsrvlogin) to add this login?
> John
> "Min" <Min@.discussions.microsoft.com> wrote in message
> news:97B19B1E-B858-4433-8642-F9DE841DB341@.microsoft.com...
> >I have 2 questions.
> >
> > 1. Can I map a local NT group (not a user) to a remote login (SQL
> > account)?
> > (local server is SQL7, remote server is SQL 2K)
> >
> > 2. I kept add new login mappings from the security tab. Then I clicked OK
> > and closed it. No error messages. Next time when I opened the tab, it only
> > shows the first mapping I did. The rest disappears. I used
> > sp_helplinkedsrvlogin, which still does NOT show the rest. Is this a bug?
> >
> > Can anyone help?
> >
> > Thank you!
> >
>
>|||Yes. I can use sp_addlinkedsrvlogin to add login, although EM still did not
show it. If I add thru EM, it will only show the first mapping. Even
sp_helplinkedsrvlogin will not show it. I have done test to prove that the
mapping was added. However neither thru EM or SP, I can see it.
> Can you use Query Analyser (sp_addlinkedsrvlogin) to add this login?
> John
> "Min" <Min@.discussions.microsoft.com> wrote in message
> news:97B19B1E-B858-4433-8642-F9DE841DB341@.microsoft.com...
> >I have 2 questions.
> >
> > 1. Can I map a local NT group (not a user) to a remote login (SQL
> > account)?
> > (local server is SQL7, remote server is SQL 2K)
> >
> > 2. I kept add new login mappings from the security tab. Then I clicked OK
> > and closed it. No error messages. Next time when I opened the tab, it only
> > shows the first mapping I did. The rest disappears. I used
> > sp_helplinkedsrvlogin, which still does NOT show the rest. Is this a bug?
> >
> > Can anyone help?
> >
> > Thank you!
> >
>
>|||Hi
You may want to check on a system that has the SQL 7 tools.
John
"Min" <Min@.discussions.microsoft.com> wrote in message
news:7290F8F4-58AD-4FFC-A4B6-E9600767D23C@.microsoft.com...
> Yes. I can use sp_addlinkedsrvlogin to add login, although EM still did
> not
> show it. If I add thru EM, it will only show the first mapping. Even
> sp_helplinkedsrvlogin will not show it. I have done test to prove that the
> mapping was added. However neither thru EM or SP, I can see it.
>> Can you use Query Analyser (sp_addlinkedsrvlogin) to add this login?
>> John
>> "Min" <Min@.discussions.microsoft.com> wrote in message
>> news:97B19B1E-B858-4433-8642-F9DE841DB341@.microsoft.com...
>> >I have 2 questions.
>> >
>> > 1. Can I map a local NT group (not a user) to a remote login (SQL
>> > account)?
>> > (local server is SQL7, remote server is SQL 2K)
>> >
>> > 2. I kept add new login mappings from the security tab. Then I clicked
>> > OK
>> > and closed it. No error messages. Next time when I opened the tab, it
>> > only
>> > shows the first mapping I did. The rest disappears. I used
>> > sp_helplinkedsrvlogin, which still does NOT show the rest. Is this a
>> > bug?
>> >
>> > Can anyone help?
>> >
>> > Thank you!
>> >
>>|||Thanks! I will check. Do you know the asnwer to my first question?
1. Can I map a local NT group (not a user) to a remote login (SQL account)?
(local server is SQL7, remote server is SQL 2K)
Thanks again.
"John Bell" wrote:
> Hi
> You may want to check on a system that has the SQL 7 tools.
> John
> "Min" <Min@.discussions.microsoft.com> wrote in message
> news:7290F8F4-58AD-4FFC-A4B6-E9600767D23C@.microsoft.com...
> > Yes. I can use sp_addlinkedsrvlogin to add login, although EM still did
> > not
> > show it. If I add thru EM, it will only show the first mapping. Even
> > sp_helplinkedsrvlogin will not show it. I have done test to prove that the
> > mapping was added. However neither thru EM or SP, I can see it.
> >
> >> Can you use Query Analyser (sp_addlinkedsrvlogin) to add this login?
> >>
> >> John
> >>
> >> "Min" <Min@.discussions.microsoft.com> wrote in message
> >> news:97B19B1E-B858-4433-8642-F9DE841DB341@.microsoft.com...
> >> >I have 2 questions.
> >> >
> >> > 1. Can I map a local NT group (not a user) to a remote login (SQL
> >> > account)?
> >> > (local server is SQL7, remote server is SQL 2K)
> >> >
> >> > 2. I kept add new login mappings from the security tab. Then I clicked
> >> > OK
> >> > and closed it. No error messages. Next time when I opened the tab, it
> >> > only
> >> > shows the first mapping I did. The rest disappears. I used
> >> > sp_helplinkedsrvlogin, which still does NOT show the rest. Is this a
> >> > bug?
> >> >
> >> > Can anyone help?
> >> >
> >> > Thank you!
> >> >
> >>
> >>
> >>
>
>|||Hi
You should be able to do this, but I don't have a system I can check it on!
In general my preference is to use NT groups wherever possible then when a
new user comes along it is part of the NT administration to set them up
(without touching the databases!).
John
"Min" <Min@.discussions.microsoft.com> wrote in message
news:80ACE6F9-9F60-471C-8E2C-A1A2D3E47534@.microsoft.com...
> Thanks! I will check. Do you know the asnwer to my first question?
> 1. Can I map a local NT group (not a user) to a remote login (SQL
> account)?
> (local server is SQL7, remote server is SQL 2K)
> Thanks again.
> "John Bell" wrote:
>> Hi
>> You may want to check on a system that has the SQL 7 tools.
>> John
>> "Min" <Min@.discussions.microsoft.com> wrote in message
>> news:7290F8F4-58AD-4FFC-A4B6-E9600767D23C@.microsoft.com...
>> > Yes. I can use sp_addlinkedsrvlogin to add login, although EM still did
>> > not
>> > show it. If I add thru EM, it will only show the first mapping. Even
>> > sp_helplinkedsrvlogin will not show it. I have done test to prove that
>> > the
>> > mapping was added. However neither thru EM or SP, I can see it.
>> >
>> >> Can you use Query Analyser (sp_addlinkedsrvlogin) to add this login?
>> >>
>> >> John
>> >>
>> >> "Min" <Min@.discussions.microsoft.com> wrote in message
>> >> news:97B19B1E-B858-4433-8642-F9DE841DB341@.microsoft.com...
>> >> >I have 2 questions.
>> >> >
>> >> > 1. Can I map a local NT group (not a user) to a remote login (SQL
>> >> > account)?
>> >> > (local server is SQL7, remote server is SQL 2K)
>> >> >
>> >> > 2. I kept add new login mappings from the security tab. Then I
>> >> > clicked
>> >> > OK
>> >> > and closed it. No error messages. Next time when I opened the tab,
>> >> > it
>> >> > only
>> >> > shows the first mapping I did. The rest disappears. I used
>> >> > sp_helplinkedsrvlogin, which still does NOT show the rest. Is this a
>> >> > bug?
>> >> >
>> >> > Can anyone help?
>> >> >
>> >> > Thank you!
>> >> >
>> >>
>> >>
>> >>
>>|||Thanks for the reply. I tried. It was mapped OK but did not work. A user
belongs to the NT group connects to the remote server as the generic account
I have (which is for everyone not in the mapping table). After couple tests,
it seems that I can only map a NT user to a remote SQL account.
"John Bell" wrote:
> Hi
> You should be able to do this, but I don't have a system I can check it on!
> In general my preference is to use NT groups wherever possible then when a
> new user comes along it is part of the NT administration to set them up
> (without touching the databases!).
> John
> "Min" <Min@.discussions.microsoft.com> wrote in message
> news:80ACE6F9-9F60-471C-8E2C-A1A2D3E47534@.microsoft.com...
> > Thanks! I will check. Do you know the asnwer to my first question?
> >
> > 1. Can I map a local NT group (not a user) to a remote login (SQL
> > account)?
> > (local server is SQL7, remote server is SQL 2K)
> >
> > Thanks again.
> >
> > "John Bell" wrote:
> >
> >> Hi
> >>
> >> You may want to check on a system that has the SQL 7 tools.
> >>
> >> John
> >>
> >> "Min" <Min@.discussions.microsoft.com> wrote in message
> >> news:7290F8F4-58AD-4FFC-A4B6-E9600767D23C@.microsoft.com...
> >> > Yes. I can use sp_addlinkedsrvlogin to add login, although EM still did
> >> > not
> >> > show it. If I add thru EM, it will only show the first mapping. Even
> >> > sp_helplinkedsrvlogin will not show it. I have done test to prove that
> >> > the
> >> > mapping was added. However neither thru EM or SP, I can see it.
> >> >
> >> >> Can you use Query Analyser (sp_addlinkedsrvlogin) to add this login?
> >> >>
> >> >> John
> >> >>
> >> >> "Min" <Min@.discussions.microsoft.com> wrote in message
> >> >> news:97B19B1E-B858-4433-8642-F9DE841DB341@.microsoft.com...
> >> >> >I have 2 questions.
> >> >> >
> >> >> > 1. Can I map a local NT group (not a user) to a remote login (SQL
> >> >> > account)?
> >> >> > (local server is SQL7, remote server is SQL 2K)
> >> >> >
> >> >> > 2. I kept add new login mappings from the security tab. Then I
> >> >> > clicked
> >> >> > OK
> >> >> > and closed it. No error messages. Next time when I opened the tab,
> >> >> > it
> >> >> > only
> >> >> > shows the first mapping I did. The rest disappears. I used
> >> >> > sp_helplinkedsrvlogin, which still does NOT show the rest. Is this a
> >> >> > bug?
> >> >> >
> >> >> > Can anyone help?
> >> >> >
> >> >> > Thank you!
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Repost - Linked server security - Help!

I have 2 questions.
1. Can I map a local NT group (not a user) to a remote login (SQL account)?
(local server is SQL7, remote server is SQL 2K)
2. I kept add new login mappings from the security tab. Then I clicked OK
and closed it. No error messages. Next time when I opened the tab, it only
shows the first mapping I did. The rest disappears. I used
sp_helplinkedsrvlogin, which still does NOT show the rest. Is this a bug?
Can anyone help?
Thank you!Hi
Is this a SQL 7 instance linked to SQL 2000 or vice versa? Which version of
EM are you using to do this?
Can you use Query Analyser (sp_addlinkedsrvlogin) to add this login?
John
"Min" <Min@.discussions.microsoft.com> wrote in message
news:97B19B1E-B858-4433-8642-F9DE841DB341@.microsoft.com...
>I have 2 questions.
> 1. Can I map a local NT group (not a user) to a remote login (SQL
> account)?
> (local server is SQL7, remote server is SQL 2K)
> 2. I kept add new login mappings from the security tab. Then I clicked OK
> and closed it. No error messages. Next time when I opened the tab, it only
> shows the first mapping I did. The rest disappears. I used
> sp_helplinkedsrvlogin, which still does NOT show the rest. Is this a bug?
> Can anyone help?
> Thank you!
>|||Local server is sql 7. linked server is sql 2k. I checked the version of the
EM, which is 8.0
"John Bell" wrote:

> Hi
> Is this a SQL 7 instance linked to SQL 2000 or vice versa? Which version o
f
> EM are you using to do this?
> Can you use Query Analyser (sp_addlinkedsrvlogin) to add this login?
> John
> "Min" <Min@.discussions.microsoft.com> wrote in message
> news:97B19B1E-B858-4433-8642-F9DE841DB341@.microsoft.com...
>
>|||Yes. I can use sp_addlinkedsrvlogin to add login, although EM still did not
show it. If I add thru EM, it will only show the first mapping. Even
sp_helplinkedsrvlogin will not show it. I have done test to prove that the
mapping was added. However neither thru EM or SP, I can see it.

> Can you use Query Analyser (sp_addlinkedsrvlogin) to add this login?
> John
> "Min" <Min@.discussions.microsoft.com> wrote in message
> news:97B19B1E-B858-4433-8642-F9DE841DB341@.microsoft.com...
>
>|||Hi
You may want to check on a system that has the SQL 7 tools.
John
"Min" <Min@.discussions.microsoft.com> wrote in message
news:7290F8F4-58AD-4FFC-A4B6-E9600767D23C@.microsoft.com...[vbcol=seagreen]
> Yes. I can use sp_addlinkedsrvlogin to add login, although EM still did
> not
> show it. If I add thru EM, it will only show the first mapping. Even
> sp_helplinkedsrvlogin will not show it. I have done test to prove that the
> mapping was added. However neither thru EM or SP, I can see it.
>|||Thanks! I will check. Do you know the asnwer to my first question?
1. Can I map a local NT group (not a user) to a remote login (SQL account)?
(local server is SQL7, remote server is SQL 2K)
Thanks again.
"John Bell" wrote:

> Hi
> You may want to check on a system that has the SQL 7 tools.
> John
> "Min" <Min@.discussions.microsoft.com> wrote in message
> news:7290F8F4-58AD-4FFC-A4B6-E9600767D23C@.microsoft.com...
>
>|||Hi
You should be able to do this, but I don't have a system I can check it on!
In general my preference is to use NT groups wherever possible then when a
new user comes along it is part of the NT administration to set them up
(without touching the databases!).
John
"Min" <Min@.discussions.microsoft.com> wrote in message
news:80ACE6F9-9F60-471C-8E2C-A1A2D3E47534@.microsoft.com...[vbcol=seagreen]
> Thanks! I will check. Do you know the asnwer to my first question?
> 1. Can I map a local NT group (not a user) to a remote login (SQL
> account)?
> (local server is SQL7, remote server is SQL 2K)
> Thanks again.
> "John Bell" wrote:
>|||Thanks for the reply. I tried. It was mapped OK but did not work. A user
belongs to the NT group connects to the remote server as the generic account
I have (which is for everyone not in the mapping table). After couple tests,
it seems that I can only map a NT user to a remote SQL account.
"John Bell" wrote:

> Hi
> You should be able to do this, but I don't have a system I can check it on
!
> In general my preference is to use NT groups wherever possible then when a
> new user comes along it is part of the NT administration to set them up
> (without touching the databases!).
> John
> "Min" <Min@.discussions.microsoft.com> wrote in message
> news:80ACE6F9-9F60-471C-8E2C-A1A2D3E47534@.microsoft.com...
>
>

Reposition Subtotals

Hi,
I have created a very simple tabular report with only one row group which I
have enabled subtotals on. What I am not enjoying is that the subtotals
seem to HAVE to be at the top of the group:
Below is an example. The DEPT header is the row group.
DEPT |INV_NO |VALUE
--
TX10 | - | £600.00
| SI123456 | £200.00
| SI123457 | £200.00
| SI123458 | £200.00
MX10 | - | £900.00
| SI123456 | £300.00
| SI123457 | £300.00
| SI123458 | £300.00
HX10 | - | £300.00
| SI123456 | £100.00
| SI123457 | £100.00
| SI123458 | £100.00
In the above example the subtotal for the group is in the same line as the
row name. Is there a way of getting it to be positioned at the bottom? For
example:
DEPT |INV_NO |VALUE
--
TX10 | - |
| SI123456 | £200.00
| SI123457 | £200.00
| SI123458 | £200.00
| | £600.00
TIA,
JarrydWhen you define a group, by default it shows you the group header, detail
line and group footer.
Have you tried to put subtotals in the group footer?
"Jarryd" <jarryd@.community.nospam> escribió en el mensaje
news:uUWbDFEKIHA.1164@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have created a very simple tabular report with only one row group which
> I have enabled subtotals on. What I am not enjoying is that the subtotals
> seem to HAVE to be at the top of the group:
> Below is an example. The DEPT header is the row group.
> DEPT |INV_NO |VALUE
> --
> TX10 | - | £600.00
> | SI123456 | £200.00
> | SI123457 | £200.00
> | SI123458 | £200.00
> MX10 | - | £900.00
> | SI123456 | £300.00
> | SI123457 | £300.00
> | SI123458 | £300.00
> HX10 | - | £300.00
> | SI123456 | £100.00
> | SI123457 | £100.00
> | SI123458 | £100.00
> In the above example the subtotal for the group is in the same line as the
> row name. Is there a way of getting it to be positioned at the bottom?
> For example:
> DEPT |INV_NO |VALUE
> --
> TX10 | - |
> | SI123456 | £200.00
> | SI123457 | £200.00
> | SI123458 | £200.00
> | | £600.00
> TIA,
> Jarryd
>|||GENIUS!! I knew it had to be something simple. Thanks a mil!
Jarryd
"Mónica" <monica.diaz@.augure.com> wrote in message
news:ejsd0hEKIHA.4752@.TK2MSFTNGP05.phx.gbl...
> When you define a group, by default it shows you the group header, detail
> line and group footer.
> Have you tried to put subtotals in the group footer?
>
> "Jarryd" <jarryd@.community.nospam> escribió en el mensaje
> news:uUWbDFEKIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have created a very simple tabular report with only one row group which
>> I have enabled subtotals on. What I am not enjoying is that the
>> subtotals seem to HAVE to be at the top of the group:
>> Below is an example. The DEPT header is the row group.
>> DEPT |INV_NO |VALUE
>> --
>> TX10 | - | £600.00
>> | SI123456 | £200.00
>> | SI123457 | £200.00
>> | SI123458 | £200.00
>> MX10 | - | £900.00
>> | SI123456 | £300.00
>> | SI123457 | £300.00
>> | SI123458 | £300.00
>> HX10 | - | £300.00
>> | SI123456 | £100.00
>> | SI123457 | £100.00
>> | SI123458 | £100.00
>> In the above example the subtotal for the group is in the same line as
>> the row name. Is there a way of getting it to be positioned at the
>> bottom? For example:
>> DEPT |INV_NO |VALUE
>> --
>> TX10 | - |
>> | SI123456 | £200.00
>> | SI123457 | £200.00
>> | SI123458 | £200.00
>> | | £600.00
>> TIA,
>> Jarryd
>