marked transactions

I am running the following script in Query Analizer:
begin transaction xxxx with mark
update logmarks set logmark=2
commit transaction xxxx
go
The script runs without error and the logmarks table is updated correctly,
but when I check the logmarkhistory table to see if a row was inserted there
is nothing in there. What am I doing wrong or why does this happen?
Thanks in advance for any help.The transaction information will be stored in the logmarkhistory table
only if there is a active log backup chain.sql

Margin rendering

When running a SSRS 2005 Report on local machines (running Windows XP) A certain Textbox Label is not wrapping and is sized with .25 inch blank spacing at the end of the text. When running thru a datadriven Subscription this label is wrapping to next line on the PDF file that is produced.

We went to the SSRS 2005 server( Windows server 2003) and opened the report and it shows this label wrapping and the text is not fitting in the label box. But on every client box that we open this report on it shows it not wrapping. Is there a setting on the Server that we need to set for this report to render correctly.

Hi Brad,

A couple questions for you:

1) Are all machines running the same version of SSRS?

2) Are you exporting to PDF in all cases?

3) Do the various servers on which the different reports run have differing DPI settings?

Many thanks,

Chris

Mapping from flatfile to SQL server database...

Hi!
Im trying to find existing software to map from flatfiles (mostly .txt) to a
database running on MS SQL Server. The files are recieved by mail, and made
available to my program, and are supposed to be read and processed in a way
that extracts the data from the files and inserts it into the database.
If I had the time and skill, I would love to do this myself, but under the
current circumstances i need to find some sort of existing SDK to do this (o
r
an easy way to program it).
Any suggestions?Christian Johansen wrote:

> Hi!
> Im trying to find existing software to map from flatfiles (mostly .txt) to
a
> database running on MS SQL Server. The files are recieved by mail, and mad
e
> available to my program, and are supposed to be read and processed in a wa
y
> that extracts the data from the files and inserts it into the database.
> If I had the time and skill, I would love to do this myself, but under the
> current circumstances i need to find some sort of existing SDK to do this
(or
> an easy way to program it).
> Any suggestions?
Have you considered using DTS or Integration Services?
http://www.sqldts.com/
www.microsoft.com/sql/technologies/...on/default.mspx
Alternatively, there are many integration tool vendors whose products
will do the job. Here is a selection:
www.abinitio.com
ibm.ascential.com
www.datamirror.com
www.datawatch.com
www.embarcadero.com
www.informatica.com
www.pervasive.com
David Portas
SQL Server MVP
--|||
"David Portas" wrote:

> Christian Johansen wrote:
>
> Have you considered using DTS or Integration Services?
> http://www.sqldts.com/
> www.microsoft.com/sql/technologies/...on/default.mspx
> Alternatively, there are many integration tool vendors whose products
> will do the job. Here is a selection:
> www.abinitio.com
> ibm.ascential.com
> www.datamirror.com
> www.datawatch.com
> www.embarcadero.com
> www.informatica.com
> www.pervasive.com
> --
> David Portas
> SQL Server MVP
> --
>
Ahh, just what I was looking for!
Thank you very much. Merry Christmas :)

Mapping Active Directory Group members to SQL Server Roles

My question is I have a SQL Server running on Web Server which is a member of a 2000 Active Directory, I only grant access to the database via Global Groups from the Active Directory. When I log onto the database via Windows Authentication the actual user shows up in the master.dbo.sysprocesses table, I can tell what database that process is going to but not how that user is being translated to the Global Group that was actually given access. I need the actual database user name which is the Global Group name that had permissions granted via user defined database roles so that I can do some pre-processing in an ASP.NET application so that I know what parts of a form are updatable or not.No, a Server Role container for users with a specific set of permission. The group has to be mapped to a principal in SQL Server to be included in a role.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.desql

Mapped Drives not Showing Up

Scenario:
Just connected a 2000 server running SQL 2000 to an Iomega NAS via a
crossover. Set both machines to be in the same workgroup and both have the
same local admin user and password. Also set the SQL service and the agent
service to run as the local admin.
Setup the NAS box to share and mapped the drives within explorer and all is
well. When I copied the database to one of the shared drives on the NAS
box and then try to restore it within Enterprise Manager the drive doesn't
show up?!?!?!?!
Any help would be greatly appreciated!
Enterprise Manager will show the local drives but not mapped
drives. It's usually better to use UNC paths rather than
mapped drives.
Just an FYI...if you are planning on storing database files
on the NAS, database files on network storage devices are
not recommended (but are supported using trace flag 1807).
See the following:
INF: Support for Network Database Files
http://support.microsoft.com/?id=304261
-Sue
On Wed, 31 Mar 2004 11:21:43 -0800, "Bob Smith"
<spamfree@.spam.com> wrote:

>Scenario:
>Just connected a 2000 server running SQL 2000 to an Iomega NAS via a
>crossover. Set both machines to be in the same workgroup and both have the
>same local admin user and password. Also set the SQL service and the agent
>service to run as the local admin.
>Setup the NAS box to share and mapped the drives within explorer and all is
>well. When I copied the database to one of the shared drives on the NAS
>box and then try to restore it within Enterprise Manager the drive doesn't
>show up?!?!?!?!
>Any help would be greatly appreciated!
>

Mapped Drives not Showing Up

Scenario:
Just connected a 2000 server running SQL 2000 to an Iomega NAS via a
crossover. Set both machines to be in the same workgroup and both have the
same local admin user and password. Also set the SQL service and the agent
service to run as the local admin.
Setup the NAS box to share and mapped the drives within explorer and all is
well. When I copied the database to one of the shared drives on the NAS
box and then try to restore it within Enterprise Manager the drive doesn't
show up?!?!?!?!
Any help would be greatly appreciated!Enterprise Manager will show the local drives but not mapped
drives. It's usually better to use UNC paths rather than
mapped drives.
Just an FYI...if you are planning on storing database files
on the NAS, database files on network storage devices are
not recommended (but are supported using trace flag 1807).
See the following:
INF: Support for Network Database Files
http://support.microsoft.com/?id=304261
-Sue
On Wed, 31 Mar 2004 11:21:43 -0800, "Bob Smith"
<spamfree@.spam.com> wrote:

>Scenario:
>Just connected a 2000 server running SQL 2000 to an Iomega NAS via a
>crossover. Set both machines to be in the same workgroup and both have the
>same local admin user and password. Also set the SQL service and the agent
>service to run as the local admin.
>Setup the NAS box to share and mapped the drives within explorer and all is
>well. When I copied the database to one of the shared drives on the NAS
>box and then try to restore it within Enterprise Manager the drive doesn't
>show up?!?!?!?!
>Any help would be greatly appreciated!
>

MAPI xp_sendmail vulnerabilities...?

What are risks of 1) granting exec on master..xp_sendmail to a sql user acco
unt (web application front end) 2) running mapi protocol on the db server i
n order to use xp_sendmail? Will limiting to outbound mail only provide need
ed security?
Thanks in advance,
ChrisYou "may" be vulnerable to any security holes in the MAPI client as sending
mail invokes the client process in memory. To confirm this, monitor the run
ning processes then send mail. If using Outlook as the MAPI client you will
notice outlook.exe starts
as a process. If using an Exchange mailbox, Outlook will (by default) exami
ne the mail headers of the incoming mail (you may need to block incoming mai
l in Exchange or at the gateway to prevent unsolicited emails).
If using xp_sendmail and/or the SQL Agent mail then I suggest you keep your MAPI client
software patched. If you only need to send mail from an extended stored procedure you
may wish to consider xp_smtp_sendmail (www.sqldev.net) which has a smaller
attac
k area (doesn't use a MAPI client like Outlook).

many small queries vs one large query

I just had a discussion with my boss, we are running a query that fetches
about 5000 objects from the database using MSSQL and DotNet 2.0.
My boss thinks that the recommendation of keeping the connections open for
as short a time as possible means that one shoule open a connection, fetch
one row, close the connection, open another connection, fetch one row and
close the connection and so on.
I belive that the extra overhead of opening and closing connections all the
time, of generating execution plans for each query, and running the queries
many times gives his solution a large performance hit. My recommendation
will be to fetch all the rows needed in one large SQL query.
I've done several other projects where I have proven this to be true, there
is indeed a large performance hit from generating many small queries instead
of one large. But my boss just says "no" and disagrees.
Can you give me some good arguments and/or point me to some best practice
documents that describe this so I can convince my boss he's wrong?
Kind Regards,
Allan Ebdrup
FUT: microsoft.public.sqlserver.programming"Allan Ebdrup" <ebdrup@.noemail.noemail> wrote in message
news:%23kIhprXWGHA.3332@.TK2MSFTNGP02.phx.gbl...
>I just had a discussion with my boss, we are running a query that fetches
>about 5000 objects from the database using MSSQL and DotNet 2.0.
> My boss thinks that the recommendation of keeping the connections open for
> as short a time as possible means that one shoule open a connection, fetch
> one row, close the connection, open another connection, fetch one row and
> close the connection and so on.
> I belive that the extra overhead of opening and closing connections all
> the time, of generating execution plans for each query, and running the
> queries many times gives his solution a large performance hit. My
> recommendation will be to fetch all the rows needed in one large SQL
> query.
> I've done several other projects where I have proven this to be true,
> there is indeed a large performance hit from generating many small queries
> instead of one large. But my boss just says "no" and disagrees.
> Can you give me some good arguments and/or point me to some best practice
> documents that describe this so I can convince my boss he's wrong?
>
Don't argue with your boss. Implement it both ways and show him.
David|||"Allan Ebdrup" <ebdrup@.noemail.noemail> wrote in
news:#kIhprXWGHA.3332@.TK2MSFTNGP02.phx.gbl:

> I've done several other projects where I have proven this to be true,
> there is indeed a large performance hit from generating many small
> queries instead of one large. But my boss just says "no" and disagrees
I would have submitted one large bulk query as well.
Perhaps multiple small queries would reduce the time a table is locked?
Any other solutions? Maybe open a connection - execute a long running SP...
and fetch the results after the fact?|||> Don't argue with your boss. Implement it both ways and show him.
Hi David
Unfortunately it's him who's doing the implementing.|||My post just now seems to have got lost (wonder where it went).
To summarise what I said:
your boss is making a massive mistake. I've seen this tactic destroy a
large application.
it's fine at first, your queries all run nearly instantaneously, and
you'll load test it later, no need to worry about that right now.
then a table gets big. suddenly the instant query takes 50 milliseconds
to get the data. not a problem if you're only doing one select. if
you're doing 5000 separate ones it takes 5000x50 milliseconds.|||IF your query is executing 5000 times against the same tables and retrieving
the same columns with selection criteria against the same columns, then
running a single query to retreive all 5000 rows would be faster, without
question.
i.e.
select employee_id, employee_name from employees
Will have a connection open for much less time than cycling through each
employee with:
select employee_id, employee_name from employee where employee_id =
'SOMEID'
The first one will also use less CPU on the database, less IO, less network
traffic, etc. You can play with the code to control how you open the
dataset once you have retrieved it, but the first SQL will almost always be
faster. The only exception I can think of is if your client does not have
enough memory to handle the entire dataset.
If you have more complicated logic, and you are running against all
different tables, and combining this into one query means extensive joins
and business logic in the where clause, then you need to test each approach
and see what the performance gain is with the view.
Opening and closing connections for each communication wiht the database is
usually most efficient in my experience. Connection pooling manages the
resources really well. The connections do not really get opened and closed,
rather they stay open and available, but the client is able to free up some
of the resources associated with them between each call. This assumes that
you actually need to execute multiple queries to begin with.
Making 5000 seperate calls to the database will use much more network
resources than making a single call to the database to retreive the same
data. That part is a no brainer. Dependign on what you have for SQL, it
will not necessarily need to generate a plan every time (one would hope you
are using stored procedures with parameters and the plans are cached).
However, there would be some small overhead associated with running each
command.
"Allan Ebdrup" <ebdrup@.noemail.noemail> wrote in message
news:%23kIhprXWGHA.3332@.TK2MSFTNGP02.phx.gbl...
> I just had a discussion with my boss, we are running a query that fetches
> about 5000 objects from the database using MSSQL and DotNet 2.0.
> My boss thinks that the recommendation of keeping the connections open for
> as short a time as possible means that one shoule open a connection, fetch
> one row, close the connection, open another connection, fetch one row and
> close the connection and so on.
> I belive that the extra overhead of opening and closing connections all
the
> time, of generating execution plans for each query, and running the
queries
> many times gives his solution a large performance hit. My recommendation
> will be to fetch all the rows needed in one large SQL query.
> I've done several other projects where I have proven this to be true,
there
> is indeed a large performance hit from generating many small queries
instead
> of one large. But my boss just says "no" and disagrees.
> Can you give me some good arguments and/or point me to some best practice
> documents that describe this so I can convince my boss he's wrong?
> Kind Regards,
> Allan Ebdrup
> FUT: microsoft.public.sqlserver.programming
>|||SQL Server is quite capable of handling long connections, and as long as
they're actually doing something they make perfect sense. Many short lasting
connections may seem to put less stress on the server, but in fact the
overhead of many connections versus a single connection, when calls to the
server constitute a single business operation, must be multiplied with the
number of connections to give a true estimate of the actual (and expected)
stress on the server.
Will puts it very simply (and very true): 5000 connections where each lasts
50ms might kick the server pretty hard. 2nd grade mathematics.
ML
http://milambda.blogspot.com/|||If the calls to the database are made against isolated objects (i.e. against
different tables or to procedures that target different tables), then
individual connections may benefit from being initiated asynchronously. But
it would make more sense in issuing a single call to related objects for a
single business operation. At least IMHO.
ML
http://milambda.blogspot.com/|||All posts made a good point and I agree that your boss is wrong. It's a no
wonder he is a not a SQL Programmer but he is your boss. He signs your
paycheck so you may not have a choice. I often had to bow to what my boss
wants, hate it and it sucks but what can I do.
Grant
Who gives a {censored} if I am wrong.
"Allan Ebdrup" <ebdrup@.noemail.noemail> wrote in message
news:%23kIhprXWGHA.3332@.TK2MSFTNGP02.phx.gbl...
>I just had a discussion with my boss, we are running a query that fetches
>about 5000 objects from the database using MSSQL and DotNet 2.0.
> My boss thinks that the recommendation of keeping the connections open for
> as short a time as possible means that one shoule open a connection, fetch
> one row, close the connection, open another connection, fetch one row and
> close the connection and so on.
> I belive that the extra overhead of opening and closing connections all
> the time, of generating execution plans for each query, and running the
> queries many times gives his solution a large performance hit. My
> recommendation will be to fetch all the rows needed in one large SQL
> query.
> I've done several other projects where I have proven this to be true,
> there is indeed a large performance hit from generating many small queries
> instead of one large. But my boss just says "no" and disagrees.
> Can you give me some good arguments and/or point me to some best practice
> documents that describe this so I can convince my boss he's wrong?
> Kind Regards,
> Allan Ebdrup
> FUT: microsoft.public.sqlserver.programming
>|||If responsiveness of your application is important, you might consider to
fetch a certain amount of objects per fetch (say 100 objects, depending on
the size of your objects), instead of fetching 5000 objects together.
If you are going to get 5000 objects at once, while responsiveness is not a
problem, you should fetch all rows using one query.
Martin C K Poon
Senior Analyst Programmer
====================================
"Allan Ebdrup" <ebdrup@.noemail.noemail> bl
news:%23kIhprXWGHA.3332@.TK2MSFTNGP02.phx.gbl g...
> I just had a discussion with my boss, we are running a query that fetches
> about 5000 objects from the database using MSSQL and DotNet 2.0.
> My boss thinks that the recommendation of keeping the connections open for
> as short a time as possible means that one shoule open a connection, fetch
> one row, close the connection, open another connection, fetch one row and
> close the connection and so on.
> I belive that the extra overhead of opening and closing connections all
the
> time, of generating execution plans for each query, and running the
queries
> many times gives his solution a large performance hit. My recommendation
> will be to fetch all the rows needed in one large SQL query.
> I've done several other projects where I have proven this to be true,
there
> is indeed a large performance hit from generating many small queries
instead
> of one large. But my boss just says "no" and disagrees.
> Can you give me some good arguments and/or point me to some best practice
> documents that describe this so I can convince my boss he's wrong?
> Kind Regards,
> Allan Ebdrup
> FUT: microsoft.public.sqlserver.programming
>sql

Many pubs or single pub with dynamic filters ??

Hello...
Some background: We have merge replication set up between SQL 2000 and
mobile devices running SQL Mobile. Each of our customers has a unique
customer id and currently we have a separate publication for each one...that
has a static filter such as "WHERE CustomerID = 12345". And on each device,
it subscribes to its own publication...based on the Customer ID.
What I am thinking about...in the near future, as our customer base grows,
is that we'll be faced with maintaining lots of Publications on the server -
one for each Customer ID.
So, would it be better to have a single publication that has dynamic
filters...based on the HostName property...which I'd set in code on the
device (based on the Customer ID) or keep things the way we have them now?
I am mostly concerned about replication/synchronization performance between
the server and the mobile devices...some of which use GPRS for connectivity.
I don't want to sacrifce any performance gains, if there are any, by having
lots of publications.
thanks for any thoughts.
- will
Here are the issues that you need to consider when moving to a single
publisher model:
1. Concurrency: syncing multiple subscribers at the same time is not
optimized in SQL2000, which will end up serializing the merge process. In
SQL2005, performance is greatly improved by allowing multiple subscribers to
sync at the same time.
2. Partitioning: When a subscriber with a given ID syncs, SQL2000 will scan
the publisher's articles for changes pertaining to that subscriber. This
process has its performance implications for large publication with large
number of changes. In SQL2005, this issue was also addressed by the use of
partition groups.
Regards,
Rafik
This posting is provided "AS IS" with no warranties, and confers no rights.
"dw" <dw@.discussions.microsoft.com> wrote in message
news:7730CDFB-81A8-437B-80A5-C5B0131814B9@.microsoft.com...
> Hello...
> Some background: We have merge replication set up between SQL 2000 and
> mobile devices running SQL Mobile. Each of our customers has a unique
> customer id and currently we have a separate publication for each
> one...that
> has a static filter such as "WHERE CustomerID = 12345". And on each
> device,
> it subscribes to its own publication...based on the Customer ID.
> What I am thinking about...in the near future, as our customer base
> grows,
> is that we'll be faced with maintaining lots of Publications on the
> server -
> one for each Customer ID.
> So, would it be better to have a single publication that has dynamic
> filters...based on the HostName property...which I'd set in code on the
> device (based on the Customer ID) or keep things the way we have them now?
> I am mostly concerned about replication/synchronization performance
> between
> the server and the mobile devices...some of which use GPRS for
> connectivity.
> I don't want to sacrifce any performance gains, if there are any, by
> having
> lots of publications.
> thanks for any thoughts.
> - will
|||Hi...
Thanks for the quotes from the BOL. However, we cannot move to SQL 2005 for
a while...so these won't help me now and for the near future.
Any other thoughts regarding my question...which is simply, "Is it better to
have one publication that multiple subscribers sync with...using their
Customer ID in the filtering of the data, or is it better to have multiple
publications that are unique to each subscriber?" And, when I ask which is
"better", I mean from a replication/synchronization performance aspect...from
the subscriber's end of the things.
thanks
- will
"Rafik Robeal" wrote:

> Here are the issues that you need to consider when moving to a single
> publisher model:
>
> 1. Concurrency: syncing multiple subscribers at the same time is not
> optimized in SQL2000, which will end up serializing the merge process. In
> SQL2005, performance is greatly improved by allowing multiple subscribers to
> sync at the same time.
>
> 2. Partitioning: When a subscriber with a given ID syncs, SQL2000 will scan
> the publisher's articles for changes pertaining to that subscriber. This
> process has its performance implications for large publication with large
> number of changes. In SQL2005, this issue was also addressed by the use of
> partition groups.
>
> Regards,
> Rafik
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "dw" <dw@.discussions.microsoft.com> wrote in message
> news:7730CDFB-81A8-437B-80A5-C5B0131814B9@.microsoft.com...
>
>
|||I have found that static filters perform better than dynamic ones. Obviously
however, there is more management (DBA work) to do.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the reply and info. That is what I was thinking...that the
performance would be better forindividual publications based on a static
filter...but figured I should toss this question out to the group, in case I
was missing something.
thanks
- will
"Paul Ibison" wrote:

> I have found that static filters perform better than dynamic ones. Obviously
> however, there is more management (DBA work) to do.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Manually Creating and Resetting Running Total in CR 8.5

Hi could anyone pls help me in manually creating and resetting running totals in Crystal Reports 8.5

Problem :

I have a table in foxpro which stores purchase or sales,product name,qty,rate. I need to calculate the cost price of balance qty in hand. say for example :

PU/SL--Name--Qty--Rate

Purchase--prodA--10---100
Sale---prodA--5---150
Sale---prodA-- 5---130
Purchase--prodA--25---90

and so on
desired output

PU/SL--Name--Qty--Rate--Amount--Bal Qty--Bal Rate

Purchase--prodA--10---100---1000---10----100
Sale---prodA--5---150---750----5----50
Sale---prodA--5---130---650----0----0
Purchase--prodA--25---90---2250---25----90

It is something similar to avg priceing that people generally use it for stock after making several transactions for same script to know what price does each stock cost.

Hence after the qty becomes 0 and new purchase made the purchase price should be the cost price and the profit made shouldnt reduce the cost price.

I have tried explaining the best possible way yet if you have any other query pls feel free to contact me on isicles@.hotmail.com

PLS HELPYou have to use Formulas in calculating running totals. See you help documentation regarding running totals you will get help.

Also see functions like
BeforeReadingRecords, WhileReadingRecords, WhilePrintingRecords & Evaluate(X). You will need these extensively for calculating running totals.

Revert if you need more help

Regards
Amarjit|||Thank You Sir,

Running totals are created using formula. But i wanna know how can i reset these totals on 2 conditions.

1) The running total value goes to 0 and
2) On change of Group

Thanks|||Why dont you use If then Else in the formula before running totals.

----See this tutotial From Crystal Reports Help Documentation --------

Example report using evaluation time function

This example report illustrates the use of an evaluation time function to take a formula that would normally be evaluated while reading records and force it instead to be evaluated while printing records.

Scenario

You want to create an order detail report that:

shows the extended price for each line item,

calculates a running total for each line, and

resets itself to 0 for each new order.

Unless you disabled the Samples and Examples option during installation, the report, called EVALTIME.RPT, is one of the sample reports that was installed on your system. An example showing partial data from that report follows:

Order# Item# Qty Price Extension RunTotal
Order #2203

2203 1002 3 276.00 828.00 828.00
2203 1102 1 1,230.00 1,230.00 2,058.00
Total for order 2203 2,058.00
Order # 2204

2204 1001 7 192.00 1,344.00 1,344.00
2204 1002 3 276.00 828.00 2,172.00
2204 1003 1 484.00 484.00 2,656.00
2204 1102 3 1,230.00 3,690.00 6,346.00
Total for order 2204 6,346.00

The report uses four database fields:

Order# {detail.ORDERNUM}
Item# {detail.ITEMNUM}
Qty {detail.QTY}
Price {detail.PRICE}
It uses three formulas:

@.extend {detail.QTY} * {detail.PRICE}

@.extend simply calculates the extended price for a line item (quantity ordered times price per item). This provides the values in the Extension column.

@.initialize NumberVar Runtotal;
If {detail.ORDERNUM} <> Previous ({detail.ORDERNUM}) Then
Runtotal := 0
Else
Runtotal := Runtotal

@.initialize resets the variable Runtotal each time the order number changes so the running total for each order begins at 0.

@.runningWhilePrintingRecords;
NumberVar Runtotal;
Runtotal:= Runtotal + {@.extend}

@.running calculates the running total for each order by adding the extended price for each line item to the existing running total for the order. This provides the values in the Running Total column.

And it includes one subtotal:

Sum of @.extend End group #1: detail.ORDER
Sum of @.extend

It subtotals the extended price each time the order number changes, thus providing the Total for order nnnn order total.

Evaluation Times

Formulas are normally evaluated at the following times:

If no database or group field is included in the formula, the formula is evaluated before the program reads database records.
If a database is included in the formula, the formula is evaluated while the program reads database records.
If a group field, page # field, subtotal, etc. is included in the formula, the formula is evaluated after database records are read and while the data from the records is being printed in the report.

The evaluation time problem which is solved with one of the evaluation time functions involves the relative evaluation time of two formulas, @.initialize and @.running.

@.initialize includes the Previous function which is evaluated while records are printed. Any formula that includes the Previous function, therefore, will be evaluated while the records are printing as well.
@.running, on the other hand, is normally evaluated while records are read. @.running includes the formula @.extend in its calculations, and @.extend involves a database field. Whenever a database field is involved in a formula, the formula is evaluated while reading records (unless there is something in the formula a subtotal, a page # field, etc. that causes the formula to be evaluated later).

If we leave both formulas to be evaluated at their normal times,

@.running is evaluated first (during record read time) and it outputs running totals for each line item.
Then, after it is finished calculating the running totals, @.initialize is evaluated (during record print time). This formula initializes (sets to 0) the Runtotal variable each time the order number changes.

By this time it's too late. The running totals have already been calculated without being initialized between orders. The final printed report shows running totals getting bigger with each line item; they are not reset from order to order.

Your report comes out looking like this:

NOTE: Order detail report @.running doesn't include an evaluation time function.

Order# Item# Qty Price Extension RunTotal
Order #2203

2203 1002 3 276.00 828.00 828.00
2203 1102 1 1,230.00 1,230.00 2,058.00
Total for order 2203 2,058.00

Order # 2204

2204 1001 7 192.00 1,344.00 3,402.00
2204 1002 3 276.00 828.00 4,230.00
2204 1003 1 484.00 484.00 4,714.00
2204 1102 3 1,230.00 3,690.00 8,404.00
Total for order 2204 6,346.00

To solve this problem, you must make certain that @.initialize is evaluated at the same time as @.running. You can't force a print time evaluation formula (@.initialize) to evaluate at read time (forcing it to be evaluated before the required data is available), but you can force a read time evaluation formula (@.running) to evaluate later, at print time. You do this by starting the formula (as we did) with the function WhilePrintingRecords;

WhilePrintingRecords;
NumberVar Runtotal;
Runtotal:= Runtotal + {@.extend}

When you do this, your report comes out looking like you want it, like this:

NOTE: Order detail report@.running includes evaluation time function.

Order# Item# Qty Price Extension RunTotal
Order #2203

2203 1002 3 276.00 828.00 828.00
2203 1102 1 1,230.00 1,230.00 2,058.00
Total for order 2203 2,058.00

Order # 2204

2204 1001 7 192.00 1,344.00 1,344.00
2204 1002 3 276.00 828.00 2,172.00
2204 1003 1 484.00 484.00 2,656.00
2204 1102 3 1,230.00 3,690.00 6,346.00

--------------------------

Hope it helps

Regards
Amarjit|||THANK YOU SIR

Your effort is highly appreciated. The code helped me learn get new ideas and ultimately solved the issue.

Thank You so much once again.

Hope If need further help you would help the young boy.|||Thank you very much that i could help you.

Please rate this post.

Regards
Amarjit

Manually configuring SPN when SQL Service account is local non-adm

Hi,
We are running SQL Server 2005 with a local low-privileged service account.
I would like to register the SPN (manually) to enable kerberos authentication.
The domain admin gets the error message "Unable to locate account <Service
Account Name>" when running the setspn command.
I can only find examples for configuring the SPN when the SQL Service
account is a (non-admin) domain user account or a local administrator account.
Does anyone know whether it is possible or not to enable kerberos
authentication when running SQL Server 2005 with a local low-privileged
service account?
Thanks!
Kai A
No. A local account is not an Active Directory account.
-Sue
On Tue, 13 Feb 2007 06:01:02 -0800, Kai A
<KaiA@.discussions.microsoft.com> wrote:

>Hi,
>We are running SQL Server 2005 with a local low-privileged service account.
>I would like to register the SPN (manually) to enable kerberos authentication.
>The domain admin gets the error message "Unable to locate account <Service
>Account Name>" when running the setspn command.
>I can only find examples for configuring the SPN when the SQL Service
>account is a (non-admin) domain user account or a local administrator account.
>Does anyone know whether it is possible or not to enable kerberos
>authentication when running SQL Server 2005 with a local low-privileged
>service account?
>Thanks!
>Kai A
|||Thank your very much for your answer!
However, I do not understand why one can register a local admin account in
AD and not a local low-privileged one? A local admin account is not an AD
account either?
This describes how one can register a local admin sql server service account
in AD:
http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx :
"d. If your sql server is running under a local machine admin account, you
can (...)run setspn under your domain credential to add the SPN."
Thanks!
Kai A
"Sue Hoegemeier" wrote:

> No. A local account is not an Active Directory account.
> -Sue
> On Tue, 13 Feb 2007 06:01:02 -0800, Kai A
> <KaiA@.discussions.microsoft.com> wrote:
>
>

Manually configuring SPN when SQL Service account is local non-adm

Hi,
We are running SQL Server 2005 with a local low-privileged service account.
I would like to register the SPN (manually) to enable kerberos authenticatio
n.
The domain admin gets the error message "Unable to locate account <Service
Account Name>" when running the setspn command.
I can only find examples for configuring the SPN when the SQL Service
account is a (non-admin) domain user account or a local administrator accoun
t.
Does anyone know whether it is possible or not to enable kerberos
authentication when running SQL Server 2005 with a local low-privileged
service account?
Thanks!
Kai ANo. A local account is not an Active Directory account.
-Sue
On Tue, 13 Feb 2007 06:01:02 -0800, Kai A
<KaiA@.discussions.microsoft.com> wrote:

>Hi,
>We are running SQL Server 2005 with a local low-privileged service account.
>I would like to register the SPN (manually) to enable kerberos authenticati
on.
>The domain admin gets the error message "Unable to locate account <Service
>Account Name>" when running the setspn command.
>I can only find examples for configuring the SPN when the SQL Service
>account is a (non-admin) domain user account or a local administrator accou
nt.
>Does anyone know whether it is possible or not to enable kerberos
>authentication when running SQL Server 2005 with a local low-privileged
>service account?
>Thanks!
>Kai A

Manual Configuration - Installation

I have installed Reporting Services on a computer running Windows Server 2000
with SP4. At the end of the installation it told me it could not
automatically configure/start reporting services and that it needed to be
done. The only thing I can find in the readme file is in regards to
permissions dor IWAM on a domain controller, however this server is not a
domain controller. When I go to the page for ReportManager I get an access
denied and the ReportingServices page has a page cannot be displayed error.
Any help is greatly appreciated.
Thanks,
IanI have made it past that error but now I am getting: The Report Server Web
service has not generated a public key. The service may have not have
started successfully. Check the log files for more information.
I have done everything here:
http://www.sqlreportingservices.net/Ask/354.aspx with no luck thus far.
Please help.
Ian
"Ian T. Jones" wrote:
> I have installed Reporting Services on a computer running Windows Server 2000
> with SP4. At the end of the installation it told me it could not
> automatically configure/start reporting services and that it needed to be
> done. The only thing I can find in the readme file is in regards to
> permissions dor IWAM on a domain controller, however this server is not a
> domain controller. When I go to the page for ReportManager I get an access
> denied and the ReportingServices page has a page cannot be displayed error.
> Any help is greatly appreciated.
> Thanks,
> Ian

Managing SQL 2000 databases on Vista...

I have one database at an ISP running on a SQL 2000 server. Under Windows XP Pro, I installed SQL 2000 administrative tools then I could use the database manager as well as the query tool to manage my database.

I have upgraded my machine to Windows Vista Ultimate. SQL 2000 client services won't install under Windows Vista.

Under Windows Vista, I was able to create an ODBC component that connected successfully to the SQL 2000 remote database, but I no longer have a transaction tool or a gui to manage the tables.

My goals are simple: I want to be able to view/add/drop tables and data using some sort of GUI. I would also like to have some sort of SQL transaction client. I don't need to do any high level database management, just view/change/add/delete tables in the one database that I own on that server.

My isp is NOT going to upgrade their SQL. I do not own Microsoft Access or Microsoft Excel. What FREE software options do I have, given my goals?

You can use SQL Server Management Studio Express, which you can obtain for free at http://msdn.microsoft.com/vstudio/express/sql/download/

Thanks,

Peter Saddow

|||That works absolutely perfectly. Thank you!
|||Although now I have another question since I've played with it a bit.

Is there an easy way to import and export data from tables using this tool? Like... if the data was in a comma delimited text file, for example? So far I've not been able to find a way to do this... I can edit the table data which is good but what if i have a massive text file I want to import....?
|||

You can use the bcp utility: http://msdn2.microsoft.com/en-us/library/ms162802.aspx

Additional, you can download the eval version of the full SQL Server product.

Thanks,

Peter Saddow

|||The BCP utility is part of the database install disk for SQL server. I only have the 2000 version, not the 2005 version.

Downloading Eval version isnt' a permanent solution...

I did purchase MS Office Excel 2007... and I can create an ODBC link to the database and grab table data... but if I edit the table I can't seem to find a way to get it to save those changes back to the table directly?
|||Hmm anybody? No response yet....

Managing SQL 2000 databases on Vista...

I have one database at an ISP running on a SQL 2000 server. Under Windows XP Pro, I installed SQL 2000 administrative tools then I could use the database manager as well as the query tool to manage my database.

I have upgraded my machine to Windows Vista Ultimate. SQL 2000 client services won't install under Windows Vista.

Under Windows Vista, I was able to create an ODBC component that connected successfully to the SQL 2000 remote database, but I no longer have a transaction tool or a gui to manage the tables.

My goals are simple: I want to be able to view/add/drop tables and data using some sort of GUI. I would also like to have some sort of SQL transaction client. I don't need to do any high level database management, just view/change/add/delete tables in the one database that I own on that server.

My isp is NOT going to upgrade their SQL. I do not own Microsoft Access or Microsoft Excel. What FREE software options do I have, given my goals?

You can use SQL Server Management Studio Express, which you can obtain for free at http://msdn.microsoft.com/vstudio/express/sql/download/

Thanks,

Peter Saddow

|||That works absolutely perfectly. Thank you!|||Although now I have another question since I've played with it a bit.

Is there an easy way to import and export data from tables using this tool? Like... if the data was in a comma delimited text file, for example? So far I've not been able to find a way to do this... I can edit the table data which is good but what if i have a massive text file I want to import....?|||

You can use the bcp utility: http://msdn2.microsoft.com/en-us/library/ms162802.aspx

Additional, you can download the eval version of the full SQL Server product.

Thanks,

Peter Saddow

|||The BCP utility is part of the database install disk for SQL server. I only have the 2000 version, not the 2005 version.

Downloading Eval version isnt' a permanent solution...

I did purchase MS Office Excel 2007... and I can create an ODBC link to the database and grab table data... but if I edit the table I can't seem to find a way to get it to save those changes back to the table directly?|||Hmm anybody? No response yet....

Managing SQL 2000 databases on Vista...

I have one database at an ISP running on a SQL 2000 server. Under Windows XP Pro, I installed SQL 2000 administrative tools then I could use the database manager as well as the query tool to manage my database.

I have upgraded my machine to Windows Vista Ultimate. SQL 2000 client services won't install under Windows Vista.

Under Windows Vista, I was able to create an ODBC component that connected successfully to the SQL 2000 remote database, but I no longer have a transaction tool or a gui to manage the tables.

My goals are simple: I want to be able to view/add/drop tables and data using some sort of GUI. I would also like to have some sort of SQL transaction client. I don't need to do any high level database management, just view/change/add/delete tables in the one database that I own on that server.

My isp is NOT going to upgrade their SQL. I do not own Microsoft Access or Microsoft Excel. What FREE software options do I have, given my goals?

You can use SQL Server Management Studio Express, which you can obtain for free at http://msdn.microsoft.com/vstudio/express/sql/download/

Thanks,

Peter Saddow

|||That works absolutely perfectly. Thank you!|||Although now I have another question since I've played with it a bit.

Is there an easy way to import and export data from tables using this tool? Like... if the data was in a comma delimited text file, for example? So far I've not been able to find a way to do this... I can edit the table data which is good but what if i have a massive text file I want to import....?|||

You can use the bcp utility: http://msdn2.microsoft.com/en-us/library/ms162802.aspx

Additional, you can download the eval version of the full SQL Server product.

Thanks,

Peter Saddow

|||The BCP utility is part of the database install disk for SQL server. I only have the 2000 version, not the 2005 version.

Downloading Eval version isnt' a permanent solution...

I did purchase MS Office Excel 2007... and I can create an ODBC link to the database and grab table data... but if I edit the table I can't seem to find a way to get it to save those changes back to the table directly?|||Hmm anybody? No response yet....

Managing Scheduled Jobs from Management Studio Express

I'm working on a backup solution for my company. Right now we have three servers in three locations running SQL Server 2000. For testing purposes, I created a scheduled job on one of these servers. It worked fine but I'd like to tweak the job some, tinker with the timing and save locations. I'm using Management Studio Express on my laptop to remotely work with these databases but I can't seem to find a decent way to work with existing jobs. Am I missing something or does SSMSE lack the "manage jobs" functionality?

SSMSE only exposes functionality that is available in SQL Express. Since Express doesn't include Agent, SSMSE doesn't include the ability to manage Agent jobs.

The SQL 2005 Feature Pack includes some DTS 2000 add-in that may meet your needs. I've not worked with them, but they may work for you.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

Managing Insert with Identity

Hi,
I am new at stored procedures.
I am running SQL Server 2005 standard on XP Pro sp2.
I have a database that has about 100 tables. For example I have a table
called 'People' with a primary key called 'PeopleID', which is an int and
also of column type 'Identity'. This 'People' table also has a
'PeopleAttributesID' column, also of int nature, and relates to a table name
d
'PeopleAttributes' of which the primary key and Identity column is name
'PeopleAttributesID'. PeopleAttributes table also has a column named
'PeopleInterfaceListID' of int that also relates to a table called
PeopleInterfaceList that has a primary key, int, identity, called
PeopleInterfaceListID.
You get the idea. :-)
My question is how to create a stored procedure for inserting new people in
the 'People' table. My issues is dealing with the identity columns of the
'People' table and it's related tables.
How do I add a row into a table that has an identity primary key and has
foreign keys into tables that also have identity primary keys. Can you give
me an example of how this should be done in your opinion?
Thank you very, very much :)
Antoine Dubuc
MSN Messenger : banquo_ws@.hotmail.com
514-761-1832Antoine wrote:
> Hi,
> I am new at stored procedures.
> I am running SQL Server 2005 standard on XP Pro sp2.
> I have a database that has about 100 tables. For example I have a
> table called 'People' with a primary key called 'PeopleID', which is
> an int and also of column type 'Identity'. This 'People' table also
> has a 'PeopleAttributesID' column, also of int nature, and relates to
> a table named 'PeopleAttributes' of which the primary key and
> Identity column is name 'PeopleAttributesID'. PeopleAttributes table
> also has a column named 'PeopleInterfaceListID' of int that also
> relates to a table called PeopleInterfaceList that has a primary key,
> int, identity, called PeopleInterfaceListID.
> You get the idea. :-)
> My question is how to create a stored procedure for inserting new
> people in the 'People' table. My issues is dealing with the identity
> columns of the 'People' table and it's related tables.
> How do I add a row into a table that has an identity primary key and
> has foreign keys into tables that also have identity primary keys.
> Can you give me an example of how this should be done in your opinion?
> Thank you very, very much :)
> Antoine Dubuc
> MSN Messenger : banquo_ws@.hotmail.com
> 514-761-1832
Youinsert by leaving off the identity column. Once the insert is complete,
you can get the new value using the SCOPE_IDENTITY() function. if you need
to insert FK values, you need to pass them into the stored procedure and use
them in the insert. You'll need to get those values from your application
first.
David Gugick
Quest Software|||Hi David,
When I try to execute this, I get the cannot leave null for this column erro
r.
DI have to manually insert it using something like:
DECLARE PeopleID INT
PeopleID = SCOPE_IDENTITY
...
Exactly how do you do this?
thank you,
Antoine
"David Gugick" wrote:

> Antoine wrote:
> Youinsert by leaving off the identity column. Once the insert is complete,
> you can get the new value using the SCOPE_IDENTITY() function. if you need
> to insert FK values, you need to pass them into the stored procedure and u
se
> them in the insert. You'll need to get those values from your application
> first.
> --
> David Gugick
> Quest Software
>
>|||> When I try to execute this, I get the cannot leave null for this column
> error.
> DI have to manually insert it using something like:
> DECLARE PeopleID INT
> PeopleID = SCOPE_IDENTITY
Did you mean:
DECLARE @.PeopleID INT
... INSERT statement here
SET @.PeopleID = SCOPE_IDENTITY()
?|||>> have a database that has about 100 tables. For example I have a table ca
lled 'People' with a primary key called 'PeopleID', which is an int and also
of column type 'Identity'. This 'People' table also has a 'PeopleAttribute
sID' column, also of int na
ture, and relates to a table named 'PeopleAttributes' of which the primary k
ey and Identity column is named 'PeopleAttributesID'. PeopleAttributes table
also has a column named 'PeopleInterfaceListID' of int that also relates to
a table called PeopleInter
faceList that has a primary key, int, identity, called PeopleInterfaceListID
. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
However, these data element names and the improper use of IDENTITY as a
key imply that you have never written an RDBMS before. IDENTITY is not
a key by definition and you are mixing data and metadata. It looks
like the typhical newbie disaster with IDENTITY columns used as pointer
chains to mimic a 1970's IDMS database. Please get help instead of
newsgroup kludges.|||In order for a column with the IDENTITY 'property' to become a key then you
need only make it such using a CONSTRAINT, either PRIMARY KEY where no
natural key exists or UNIQUE if you are making it an artificial or surrogate
key.
Get a grip and be more constructive, the poster said his level of experience
was beginner so be polite and don't rant your rude rubbish.
There are many objects in the world that do not have a natural key, my
favourite is the message board example.
It's quite concievable that the author wants to create an auto generated
number and use that as a key, afterall, its becoming very dodgy to say the
least in the US using social security numbers and capturing that info on a
website, well - would you honestly give over your national insurance number
(uk equiv) to a company - nope.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1132258326.365651.286720@.g14g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are.
> However, these data element names and the improper use of IDENTITY as a
> key imply that you have never written an RDBMS before. IDENTITY is not
> a key by definition and you are mixing data and metadata. It looks
> like the typhical newbie disaster with IDENTITY columns used as pointer
> chains to mimic a 1970's IDMS database. Please get help instead of
> newsgroup kludges.
>

Managing 2000 and 2005

Dear all
I have now a default instance of SQL Server 2005 running on one PC and an
old default instance of SQL Server 2000 running on another PC.
I would like to administer both SQL Servers with the new SQL Server 2005
Management Studio. However, I am unable to register the SQL Server 2000 in
the SQL Server 2005 Management Studio.
Any help is appreciated.
Best regards
Adrian
Firewalls?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4525139C.1010502@.realsqlguy.com...
> Adrian wrote:
> In the connection dialog in Management Studio, try specifying the server
> name like this:
> serverDNSname,2433
> And in the Options of that connection dialog, make sure the "Encrypt
> Connection" checkbox is marked...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Deal all
Thank you very much for the excellent and very quick help.
Yes, the connection is established.
The trick is:
i) to follow this advice:
> In the connection dialog in Management Studio, try specifying the server
> name like this:
> serverDNSname,2433
but
ii) not to follow this one:
> And in the Options of that connection dialog, make sure the "Encrypt
> Connection" checkbox is marked...
Now my sleep will be peaceful.
Best regards
Adrian
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4525139C.1010502@.realsqlguy.com...
> Adrian wrote:
> In the connection dialog in Management Studio, try specifying the server
> name like this:
> serverDNSname,2433
> And in the Options of that connection dialog, make sure the "Encrypt
> Connection" checkbox is marked...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||You should be able to.
What problems are you seeing?
"Adrian" <adrian@.iai.uni-bonn.de> wrote in message
news:Ofpo5yH6GHA.4620@.TK2MSFTNGP02.phx.gbl...
> Dear all
> I have now a default instance of SQL Server 2005 running on one PC and an
> old default instance of SQL Server 2000 running on another PC.
> I would like to administer both SQL Servers with the new SQL Server 2005
> Management Studio. However, I am unable to register the SQL Server 2000 in
> the SQL Server 2005 Management Studio.
> Any help is appreciated.
> Best regards
> Adrian
>
|||Adrian wrote:
> Dear all
> I have now a default instance of SQL Server 2005 running on one PC and an
> old default instance of SQL Server 2000 running on another PC.
> I would like to administer both SQL Servers with the new SQL Server 2005
> Management Studio. However, I am unable to register the SQL Server 2000 in
> the SQL Server 2005 Management Studio.
> Any help is appreciated.
> Best regards
> Adrian
>
"Unable to register" means what exactly? Are you getting an error
message? Management Studio is perfectly capable of working with a 2000
server, I do it all day long every day...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4525017F.8020007@.realsqlguy.com...
> Adrian wrote:
> "Unable to register" means what exactly? Are you getting an error
> message? Management Studio is perfectly capable of working with a 2000
> server, I do it all day long every day...
Well, it is great to hear it. So I just need to figure out how.
I can connect to the SQL Server 2000 with the sqlcmd program within an eye's
blink. However, when I enter precisely the same connect data to the
register-dialog of the Management Studio it returns after quite a long time
a message with the error 10061.
So what am I doing wrong?
Adrian

>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||You should be able to connect to a SQL Server 2000 box from the SQL Server
2005 tools (including SSMS, Profiler, and the Replication Monitor
(sqlmonitor).
Keith Kratochvil
"Adrian" <adrian@.iai.uni-bonn.de> wrote in message
news:Ofpo5yH6GHA.4620@.TK2MSFTNGP02.phx.gbl...
> Dear all
> I have now a default instance of SQL Server 2005 running on one PC and an
> old default instance of SQL Server 2000 running on another PC.
> I would like to administer both SQL Servers with the new SQL Server 2005
> Management Studio. However, I am unable to register the SQL Server 2000 in
> the SQL Server 2005 Management Studio.
> Any help is appreciated.
> Best regards
> Adrian
>
|||Adrian wrote:
> Well, it is great to hear it. So I just need to figure out how.
> I can connect to the SQL Server 2000 with the sqlcmd program within an eye's
> blink. However, when I enter precisely the same connect data to the
> register-dialog of the Management Studio it returns after quite a long time
> a message with the error 10061.
> So what am I doing wrong?
That error indicates that the connection was refused by the server. Is
SQL Server using TCP/IP? Listening on what port?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45250DE5.8030609@.realsqlguy.com...
> Adrian wrote:
> That error indicates that the connection was refused by the server. Is
> SQL Server using TCP/IP? Listening on what port?
Yes, it listens on TCP/IP 2433 and requires encryption. The client libraries
on the SQL Server 2005 PC are set appriopriately, so sqlcmd has no problems
connecting to it. It is only the Management Studio that makes me ...
aaarrrgh.
Any ideas?
Adrian

>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Adrian wrote:
> Yes, it listens on TCP/IP 2433 and requires encryption. The client libraries
> on the SQL Server 2005 PC are set appriopriately, so sqlcmd has no problems
> connecting to it. It is only the Management Studio that makes me ...
> aaarrrgh.
> Any ideas?
>
In the connection dialog in Management Studio, try specifying the server
name like this:
serverDNSname,2433
And in the Options of that connection dialog, make sure the "Encrypt
Connection" checkbox is marked...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Managing 2000 and 2005

Dear all
I have now a default instance of SQL Server 2005 running on one PC and an
old default instance of SQL Server 2000 running on another PC.
I would like to administer both SQL Servers with the new SQL Server 2005
Management Studio. However, I am unable to register the SQL Server 2000 in
the SQL Server 2005 Management Studio.
Any help is appreciated.
Best regards
AdrianYou should be able to.
What problems are you seeing?
"Adrian" <adrian@.iai.uni-bonn.de> wrote in message
news:Ofpo5yH6GHA.4620@.TK2MSFTNGP02.phx.gbl...
> Dear all
> I have now a default instance of SQL Server 2005 running on one PC and an
> old default instance of SQL Server 2000 running on another PC.
> I would like to administer both SQL Servers with the new SQL Server 2005
> Management Studio. However, I am unable to register the SQL Server 2000 in
> the SQL Server 2005 Management Studio.
> Any help is appreciated.
> Best regards
> Adrian
>|||Adrian wrote:
> Dear all
> I have now a default instance of SQL Server 2005 running on one PC and an
> old default instance of SQL Server 2000 running on another PC.
> I would like to administer both SQL Servers with the new SQL Server 2005
> Management Studio. However, I am unable to register the SQL Server 2000 in
> the SQL Server 2005 Management Studio.
> Any help is appreciated.
> Best regards
> Adrian
>
"Unable to register" means what exactly? Are you getting an error
message? Management Studio is perfectly capable of working with a 2000
server, I do it all day long every day...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4525017F.8020007@.realsqlguy.com...
> Adrian wrote:
> "Unable to register" means what exactly? Are you getting an error
> message? Management Studio is perfectly capable of working with a 2000
> server, I do it all day long every day...
Well, it is great to hear it. So I just need to figure out how.
I can connect to the SQL Server 2000 with the sqlcmd program within an eye's
blink. However, when I enter precisely the same connect data to the
register-dialog of the Management Studio it returns after quite a long time
a message with the error 10061.
So what am I doing wrong?
Adrian

>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||You should be able to connect to a SQL Server 2000 box from the SQL Server
2005 tools (including SSMS, Profiler, and the Replication Monitor
(sqlmonitor).
Keith Kratochvil
"Adrian" <adrian@.iai.uni-bonn.de> wrote in message
news:Ofpo5yH6GHA.4620@.TK2MSFTNGP02.phx.gbl...
> Dear all
> I have now a default instance of SQL Server 2005 running on one PC and an
> old default instance of SQL Server 2000 running on another PC.
> I would like to administer both SQL Servers with the new SQL Server 2005
> Management Studio. However, I am unable to register the SQL Server 2000 in
> the SQL Server 2005 Management Studio.
> Any help is appreciated.
> Best regards
> Adrian
>|||Adrian wrote:
> Well, it is great to hear it. So I just need to figure out how.
> I can connect to the SQL Server 2000 with the sqlcmd program within an eye
's
> blink. However, when I enter precisely the same connect data to the
> register-dialog of the Management Studio it returns after quite a long tim
e
> a message with the error 10061.
> So what am I doing wrong?
That error indicates that the connection was refused by the server. Is
SQL Server using TCP/IP? Listening on what port?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45250DE5.8030609@.realsqlguy.com...
> Adrian wrote:
> That error indicates that the connection was refused by the server. Is
> SQL Server using TCP/IP? Listening on what port?
Yes, it listens on TCP/IP 2433 and requires encryption. The client libraries
on the SQL Server 2005 PC are set appriopriately, so sqlcmd has no problems
connecting to it. It is only the Management Studio that makes me ...
aaarrrgh.
Any ideas?
Adrian

>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Adrian wrote:
> Yes, it listens on TCP/IP 2433 and requires encryption. The client librari
es
> on the SQL Server 2005 PC are set appriopriately, so sqlcmd has no problem
s
> connecting to it. It is only the Management Studio that makes me ...
> aaarrrgh.
> Any ideas?
>
In the connection dialog in Management Studio, try specifying the server
name like this:
serverDNSname,2433
And in the Options of that connection dialog, make sure the "Encrypt
Connection" checkbox is marked...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Firewalls?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4525139C.1010502@.realsqlguy.com...
> Adrian wrote:
> In the connection dialog in Management Studio, try specifying the server
> name like this:
> serverDNSname,2433
> And in the Options of that connection dialog, make sure the "Encrypt
> Connection" checkbox is marked...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Deal all
Thank you very much for the excellent and very quick help.
Yes, the connection is established.
The trick is:
i) to follow this advice:
> In the connection dialog in Management Studio, try specifying the server
> name like this:
> serverDNSname,2433
but
ii) not to follow this one:
> And in the Options of that connection dialog, make sure the "Encrypt
> Connection" checkbox is marked...
Now my sleep will be peaceful.
Best regards
Adrian
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4525139C.1010502@.realsqlguy.com...
> Adrian wrote:
> In the connection dialog in Management Studio, try specifying the server
> name like this:
> serverDNSname,2433
> And in the Options of that connection dialog, make sure the "Encrypt
> Connection" checkbox is marked...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com