Showing posts with label machine. Show all posts
Showing posts with label machine. Show all posts

Tuesday, March 27, 2012

Estimated cost shows 300% in query plan

Hello all,
On a SQL Server 2000 sp4, on a 4 Xeon cpus (with hyperthreading) machine
(Windows Server 2003), most of the query plans I see are showing, on the
estimated cost of each step of the plan, more than 100% (e.g. 300% for a
seek).
Does anyone have an explanation or has already seen that ?
tia,
Rudi Bruchez
MCDBAIt's a bug in the graphical query plans when dealing with parallel query
plans. I have seen percentage reaching 4 figures even. I don't think it will
be fixed, considering it wasn't fixed in SP4.
--
Jacco Schalkwijk
SQL Server MVP
"Rudi Bruchez" <rudi#no-spam#at.babaluga.com> wrote in message
news:138syoixibdyn$.14zc2fkwrr1j5.dlg@.40tude.net...
> Hello all,
> On a SQL Server 2000 sp4, on a 4 Xeon cpus (with hyperthreading) machine
> (Windows Server 2003), most of the query plans I see are showing, on the
> estimated cost of each step of the plan, more than 100% (e.g. 300% for a
> seek).
> Does anyone have an explanation or has already seen that ?
> tia,
> Rudi Bruchez
> MCDBA|||On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:
> It's a bug in the graphical query plans when dealing with parallel query
> plans. I have seen percentage reaching 4 figures even. I don't think it will
> be fixed, considering it wasn't fixed in SP4.
Hi,
Thanks for the feedback. My problem is that I've set the maximum degree of
parallelism to 1 at the server level, and there's no mention of parallelism
on the graphical plan. Should this still appear ?
Btw, even with a maxdop to 1, I still see some "degree of parallelism"
event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
Is it explanable ?
thanks again,
Rudi Bruchez|||On Thu, 27 Oct 2005 17:05:35 +0200, Rudi Bruchez
<rudi#no-spam#at.babaluga.com> wrote:
>On a SQL Server 2000 sp4, on a 4 Xeon cpus (with hyperthreading) machine
>(Windows Server 2003), most of the query plans I see are showing, on the
>estimated cost of each step of the plan, more than 100% (e.g. 300% for a
>seek).
>Does anyone have an explanation or has already seen that ?
It happens.
:)
How about the after-query plan?
J.|||Rudi Bruchez wrote:
> On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:
>> It's a bug in the graphical query plans when dealing with parallel
>> query plans. I have seen percentage reaching 4 figures even. I don't
>> think it will be fixed, considering it wasn't fixed in SP4.
> Hi,
> Thanks for the feedback. My problem is that I've set the maximum
> degree of parallelism to 1 at the server level, and there's no
> mention of parallelism on the graphical plan. Should this still
> appear ?
> Btw, even with a maxdop to 1, I still see some "degree of parallelism"
> event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
> Is it explanable ?
> thanks again,
> Rudi Bruchez
What happens with the actual plan, not the estimated one? Does it
display correctly? What happens if you add a MAXDOP (1) to the query?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||On Thu, 27 Oct 2005 18:33:59 -0400, David Gugick wrote:
> Rudi Bruchez wrote:
>> On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:
>> It's a bug in the graphical query plans when dealing with parallel
>> query plans. I have seen percentage reaching 4 figures even. I don't
>> think it will be fixed, considering it wasn't fixed in SP4.
>> Hi,
>> Thanks for the feedback. My problem is that I've set the maximum
>> degree of parallelism to 1 at the server level, and there's no
>> mention of parallelism on the graphical plan. Should this still
>> appear ?
>> Btw, even with a maxdop to 1, I still see some "degree of parallelism"
>> event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
>> Is it explanable ?
>> thanks again,
>> Rudi Bruchez
> What happens with the actual plan, not the estimated one? Does it
> display correctly? What happens if you add a MAXDOP (1) to the query?
Hello,
I was talking about the actual plan, no difference there with the
estimated, on both I see 300% on several seeks. Same if I put the MAXDOP
(1) option on the query.
I'm interested in this also because it is a server hosted by an ISP, and
there are sometimes performances problems I've difficulties to explain form
the SQL server perspective only. I'm tracing peculiarities which could be
signs for problems.

Estimated cost shows 300% in query plan

Hello all,
On a SQL Server 2000 sp4, on a 4 Xeon cpus (with hyperthreading) machine
(Windows Server 2003), most of the query plans I see are showing, on the
estimated cost of each step of the plan, more than 100% (e.g. 300% for a
seek).
Does anyone have an explanation or has already seen that ?
tia,
Rudi Bruchez
MCDBA
It's a bug in the graphical query plans when dealing with parallel query
plans. I have seen percentage reaching 4 figures even. I don't think it will
be fixed, considering it wasn't fixed in SP4.
Jacco Schalkwijk
SQL Server MVP
"Rudi Bruchez" <rudi#no-spam#at.babaluga.com> wrote in message
news:138syoixibdyn$.14zc2fkwrr1j5.dlg@.40tude.net.. .
> Hello all,
> On a SQL Server 2000 sp4, on a 4 Xeon cpus (with hyperthreading) machine
> (Windows Server 2003), most of the query plans I see are showing, on the
> estimated cost of each step of the plan, more than 100% (e.g. 300% for a
> seek).
> Does anyone have an explanation or has already seen that ?
> tia,
> Rudi Bruchez
> MCDBA
|||On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:

> It's a bug in the graphical query plans when dealing with parallel query
> plans. I have seen percentage reaching 4 figures even. I don't think it will
> be fixed, considering it wasn't fixed in SP4.
Hi,
Thanks for the feedback. My problem is that I've set the maximum degree of
parallelism to 1 at the server level, and there's no mention of parallelism
on the graphical plan. Should this still appear ?
Btw, even with a maxdop to 1, I still see some "degree of parallelism"
event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
Is it explanable ?
thanks again,
Rudi Bruchez
|||On Thu, 27 Oct 2005 17:05:35 +0200, Rudi Bruchez
<rudi#no-spam#at.babaluga.com> wrote:
>On a SQL Server 2000 sp4, on a 4 Xeon cpus (with hyperthreading) machine
>(Windows Server 2003), most of the query plans I see are showing, on the
>estimated cost of each step of the plan, more than 100% (e.g. 300% for a
>seek).
>Does anyone have an explanation or has already seen that ?
It happens.

How about the after-query plan?
J.
|||Rudi Bruchez wrote:
> On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:
>
> Hi,
> Thanks for the feedback. My problem is that I've set the maximum
> degree of parallelism to 1 at the server level, and there's no
> mention of parallelism on the graphical plan. Should this still
> appear ?
> Btw, even with a maxdop to 1, I still see some "degree of parallelism"
> event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
> Is it explanable ?
> thanks again,
> Rudi Bruchez
What happens with the actual plan, not the estimated one? Does it
display correctly? What happens if you add a MAXDOP (1) to the query?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||On Thu, 27 Oct 2005 18:33:59 -0400, David Gugick wrote:

> Rudi Bruchez wrote:
> What happens with the actual plan, not the estimated one? Does it
> display correctly? What happens if you add a MAXDOP (1) to the query?
Hello,
I was talking about the actual plan, no difference there with the
estimated, on both I see 300% on several seeks. Same if I put the MAXDOP
(1) option on the query.
I'm interested in this also because it is a server hosted by an ISP, and
there are sometimes performances problems I've difficulties to explain form
the SQL server perspective only. I'm tracing peculiarities which could be
signs for problems.

Estimated cost shows 300% in query plan

Hello all,
On a SQL Server 2000 sp4, on a 4 Xeon cpus (with hyperthreading) machine
(Windows Server 2003), most of the query plans I see are showing, on the
estimated cost of each step of the plan, more than 100% (e.g. 300% for a
seek).
Does anyone have an explanation or has already seen that ?
tia,
Rudi Bruchez
MCDBAIt's a bug in the graphical query plans when dealing with parallel query
plans. I have seen percentage reaching 4 figures even. I don't think it will
be fixed, considering it wasn't fixed in SP4.
Jacco Schalkwijk
SQL Server MVP
"Rudi Bruchez" <rudi#no-spam#at.babaluga.com> wrote in message
news:138syoixibdyn$.14zc2fkwrr1j5.dlg@.40tude.net...
> Hello all,
> On a SQL Server 2000 sp4, on a 4 Xeon cpus (with hyperthreading) machine
> (Windows Server 2003), most of the query plans I see are showing, on the
> estimated cost of each step of the plan, more than 100% (e.g. 300% for a
> seek).
> Does anyone have an explanation or has already seen that ?
> tia,
> Rudi Bruchez
> MCDBA|||On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:

> It's a bug in the graphical query plans when dealing with parallel query
> plans. I have seen percentage reaching 4 figures even. I don't think it wi
ll
> be fixed, considering it wasn't fixed in SP4.
Hi,
Thanks for the feedback. My problem is that I've set the maximum degree of
parallelism to 1 at the server level, and there's no mention of parallelism
on the graphical plan. Should this still appear ?
Btw, even with a maxdop to 1, I still see some "degree of parallelism"
event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
Is it explanable ?
thanks again,
Rudi Bruchez|||On Thu, 27 Oct 2005 17:05:35 +0200, Rudi Bruchez
<rudi#no-spam#at.babaluga.com> wrote:
>On a SQL Server 2000 sp4, on a 4 Xeon cpus (with hyperthreading) machine
>(Windows Server 2003), most of the query plans I see are showing, on the
>estimated cost of each step of the plan, more than 100% (e.g. 300% for a
>seek).
>Does anyone have an explanation or has already seen that ?
It happens.

How about the after-query plan?
J.|||Rudi Bruchez wrote:
> On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:
>
> Hi,
> Thanks for the feedback. My problem is that I've set the maximum
> degree of parallelism to 1 at the server level, and there's no
> mention of parallelism on the graphical plan. Should this still
> appear ?
> Btw, even with a maxdop to 1, I still see some "degree of parallelism"
> event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
> Is it explanable ?
> thanks again,
> Rudi Bruchez
What happens with the actual plan, not the estimated one? Does it
display correctly? What happens if you add a MAXDOP (1) to the query?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||On Thu, 27 Oct 2005 18:33:59 -0400, David Gugick wrote:

> Rudi Bruchez wrote:
> What happens with the actual plan, not the estimated one? Does it
> display correctly? What happens if you add a MAXDOP (1) to the query?
Hello,
I was talking about the actual plan, no difference there with the
estimated, on both I see 300% on several seeks. Same if I put the MAXDOP
(1) option on the query.
I'm interested in this also because it is a server hosted by an ISP, and
there are sometimes performances problems I've difficulties to explain form
the SQL server perspective only. I'm tracing peculiarities which could be
signs for problems.sql

Monday, March 26, 2012

Esport databases

I m having WIN NT 4 and SQl server 6.5 i wanted to shift to WIN 2003 server
and SQL server 2000 on a new machine how can transfer the old SQL 6.5
databaes to new SQL server 2000 on a new nachine
Yousuf Khan
Programmer
You can use several methods.
Backup and restore to newserver or Copy both mdf and ldf file to new server
and attach the database or use DTS.
any more question pls reply
hope this will help
Herbert
"Yousuf" wrote:

> I m having WIN NT 4 and SQl server 6.5 i wanted to shift to WIN 2003 server
> and SQL server 2000 on a new machine how can transfer the old SQL 6.5
> databaes to new SQL server 2000 on a new nachine
> --
> Yousuf Khan
> Programmer
|||Herbert thanks for your reply
can you please write me the exact procedure shifting from old server to new
server with upgraded version I am having Tape Device or can i transfer of
import from 6.5 to 2000
Yousuf Khan
Programmer
"Herbert" wrote:
[vbcol=seagreen]
> You can use several methods.
> Backup and restore to newserver or Copy both mdf and ldf file to new server
> and attach the database or use DTS.
> any more question pls reply
> hope this will help
> Herbert
>
> "Yousuf" wrote:
|||U take full backup to ur local server drive. Copy that backup to new server
and using the All Tasks => restore option restore the DB to new server.
For backup and restore refer BOL
Herbert
"Yousuf" wrote:
[vbcol=seagreen]
> Herbert thanks for your reply
> can you please write me the exact procedure shifting from old server to new
> server with upgraded version I am having Tape Device or can i transfer of
> import from 6.5 to 2000
> --
> Yousuf Khan
> Programmer
>
> "Herbert" wrote:
|||6.5 didn't have mdf files, and the storage format was different from the new architecture. So copy
db files or backup doesn't work.
SQL2K has an upgrade wizard that can upgrade from 6.5. Or, use DTS.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:CC047EAF-C9A5-4E92-B1E8-4C368296F52E@.microsoft.com...[vbcol=seagreen]
> You can use several methods.
> Backup and restore to newserver or Copy both mdf and ldf file to new server
> and attach the database or use DTS.
> any more question pls reply
> hope this will help
> Herbert
>
> "Yousuf" wrote:
|||Hi,
As Tibor pointed out due to architectural changes you can not DETACH and
ATTACH the databases, as well as BACKUP and RESTORE will not
work.
The best method is to use the SQL server 2000 upgrade wizard.This converts
your Schema, logins, users, data and all other objects to sql 2000.
Every thing automatic.
Detailed steps from books online:-
How to perform a SQL Server version 6.5 to SQL Server 2000 upgrade using a
direct pipeline (SQL Server Upgrade Wizard)
Note To run the SQL Server Upgrade Wizard, you must have an instance of
Microsoft SQL ServerT 2000 already installed on your computer.
To perform a SQL Server version 6.5 to SQL Server 2000 upgrade by named pipe
1.. On the Start menu, point to Programs/Microsoft SQL Server-Switch,
click SQL Server Upgrade Wizard, and then click Next.
2.. In the Data and Object Transfer screen, accept the default selections,
including Named pipe, and then click Next. Verification options are
recommended, but not required. Click Help for information.
3.. On the Logon screen, in the Server name box in the Export server (6.5)
group box, enter the name of the local or remote computer on which Microsoft
SQL Server version 6.5 is installed.
In the Administrator password ('sa') box, enter the sa password for SQL
Server 6.5, and then click Next.
Unless you have changed it already, the default system administrator
password for SQL Server 2000 is blank. Although the SQL Server 2000 default
for the sa password is blank, to conform with best security practices, it
should be changed to a strong password at the first opportunity.
For Import Server (2000), the server name is filled in. Enter the optional
startup arguments, if you want. Click Help for information. When you are
finished setting options, click Next.
4.. In the message box asking if you want to continue, click Yes if you
are ready to upgrade. The SQL Server Upgrade Wizard shuts down SQL Server
6.5 and starts SQL Server 2000.
5.. In the Code Page Selection screen, accept or change the default
settings, and then click Next.
6.. In the Database Selection screen, include the databases to upgrade.
Move any databases you do not want upgraded at this time to the Exclude
list, and then click Next.
Converting all databases is recommended.
7.. In the Database Creation dialog box, select Use the default
configuration or edit the default, and then click Next.
Click Edit to examine and make changes to the proposed disk configuration
within the layout utility. In the Proposed Database Layout box, make changes
as needed. Click Advanced to view Object Details and Drive Summary. When you
are finished, click Accept to return to the SQL Server Upgrade Wizard.
8.. In the System Configuration screen, in System objects to transfer,
select the object types to transfer from SQL Server 6.5 to SQL Server 2000:
a.. Server configuration
Login and remote login registrations and server configuration options
relevant to SQL Server 2000 are transferred as part of the version upgrade.
b.. Replication settings
All articles, subscriptions and publications of each selected database,
plus the distribution database, if any, are transferred and upgraded.
c.. SQL Executive settings
All tasks scheduled by SQL Executive are transferred and upgraded so
that SQL Server 2000 can schedule and run those tasks in SQL Server Agent.
9.. In the System Configuration screen, in Advanced settings, for ANSI
Nulls, select:
a.. Off, if ANSI nulls should not be used when stored procedures are
created. This is the default.
b.. On, if ANSI nulls should be used when stored procedures are created.
10.. In Quoted identifiers, select one of these options, and then click
Next:
a.. Mixed (or don't know), if some of your objects were created with
QUOTED_IDENTIFIER set to ON and others with it set to OFF, or if you are not
sure how they were created.
b.. Off, if all objects should be compiled with QUOTED_IDENTIFIER set to
OFF.
c.. On, if all objects should be compiled with QUOTED_IDENTIFIER set to
ON.
11.. In the Completing the SQL Server Wizard screen, view the summary of
choices you have made. Click View warnings and choices in notepad to open a
text version of the upgrade script. If all options are correct, click
Finish.
The SQL Server Upgrade Script Interpreter screen appears, with information
on the progress of the upgrade.
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eSmyRu7dFHA.1684@.TK2MSFTNGP09.phx.gbl...
> 6.5 didn't have mdf files, and the storage format was different from the
> new architecture. So copy
> db files or backup doesn't work.
> SQL2K has an upgrade wizard that can upgrade from 6.5. Or, use DTS.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:CC047EAF-C9A5-4E92-B1E8-4C368296F52E@.microsoft.com...
>
begin 666 note.gif
M1TE&.#EA# `+`(#_`(2&`,# P"'Y! $```$`+ `````,``L`0 (:C(\(H'S[
68%R0&ED;M7,'[%S2YW#1)VJ;4P``.P``
`
end

Esport databases

I m having WIN NT 4 and SQl server 6.5 i wanted to shift to WIN 2003 server
and SQL server 2000 on a new machine how can transfer the old SQL 6.5
databaes to new SQL server 2000 on a new nachine
--
Yousuf Khan
ProgrammerYou can use several methods.
Backup and restore to newserver or Copy both mdf and ldf file to new server
and attach the database or use DTS.
any more question pls reply
hope this will help
Herbert
"Yousuf" wrote:

> I m having WIN NT 4 and SQl server 6.5 i wanted to shift to WIN 2003 serve
r
> and SQL server 2000 on a new machine how can transfer the old SQL 6.5
> databaes to new SQL server 2000 on a new nachine
> --
> Yousuf Khan
> Programmer|||Herbert thanks for your reply
can you please write me the exact procedure shifting from old server to new
server with upgraded version I am having Tape Device or can i transfer of
import from 6.5 to 2000
--
Yousuf Khan
Programmer
"Herbert" wrote:
[vbcol=seagreen]
> You can use several methods.
> Backup and restore to newserver or Copy both mdf and ldf file to new serve
r
> and attach the database or use DTS.
> any more question pls reply
> hope this will help
> Herbert
>
> "Yousuf" wrote:
>|||U take full backup to ur local server drive. Copy that backup to new server
and using the All Tasks => restore option restore the DB to new server.
For backup and restore refer BOL
--
Herbert
"Yousuf" wrote:
[vbcol=seagreen]
> Herbert thanks for your reply
> can you please write me the exact procedure shifting from old server to ne
w
> server with upgraded version I am having Tape Device or can i transfer of
> import from 6.5 to 2000
> --
> Yousuf Khan
> Programmer
>
> "Herbert" wrote:
>|||6.5 didn't have mdf files, and the storage format was different from the new
architecture. So copy
db files or backup doesn't work.
SQL2K has an upgrade wizard that can upgrade from 6.5. Or, use DTS.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:CC047EAF-C9A5-4E92-B1E8-4C368296F52E@.microsoft.com...[vbcol=seagreen]
> You can use several methods.
> Backup and restore to newserver or Copy both mdf and ldf file to new serve
r
> and attach the database or use DTS.
> any more question pls reply
> hope this will help
> Herbert
>
> "Yousuf" wrote:
>

Esport databases

I m having WIN NT 4 and SQl server 6.5 i wanted to shift to WIN 2003 server
and SQL server 2000 on a new machine how can transfer the old SQL 6.5
databaes to new SQL server 2000 on a new nachine
--
Yousuf Khan
ProgrammerYou can use several methods.
Backup and restore to newserver or Copy both mdf and ldf file to new server
and attach the database or use DTS.
any more question pls reply
hope this will help
Herbert
"Yousuf" wrote:
> I m having WIN NT 4 and SQl server 6.5 i wanted to shift to WIN 2003 server
> and SQL server 2000 on a new machine how can transfer the old SQL 6.5
> databaes to new SQL server 2000 on a new nachine
> --
> Yousuf Khan
> Programmer|||Herbert thanks for your reply
can you please write me the exact procedure shifting from old server to new
server with upgraded version I am having Tape Device or can i transfer of
import from 6.5 to 2000
--
Yousuf Khan
Programmer
"Herbert" wrote:
> You can use several methods.
> Backup and restore to newserver or Copy both mdf and ldf file to new server
> and attach the database or use DTS.
> any more question pls reply
> hope this will help
> Herbert
>
> "Yousuf" wrote:
> > I m having WIN NT 4 and SQl server 6.5 i wanted to shift to WIN 2003 server
> > and SQL server 2000 on a new machine how can transfer the old SQL 6.5
> > databaes to new SQL server 2000 on a new nachine
> > --
> > Yousuf Khan
> > Programmer|||U take full backup to ur local server drive. Copy that backup to new server
and using the All Tasks => restore option restore the DB to new server.
For backup and restore refer BOL
--
Herbert
"Yousuf" wrote:
> Herbert thanks for your reply
> can you please write me the exact procedure shifting from old server to new
> server with upgraded version I am having Tape Device or can i transfer of
> import from 6.5 to 2000
> --
> Yousuf Khan
> Programmer
>
> "Herbert" wrote:
> > You can use several methods.
> >
> > Backup and restore to newserver or Copy both mdf and ldf file to new server
> > and attach the database or use DTS.
> >
> > any more question pls reply
> >
> > hope this will help
> >
> > Herbert
> >
> >
> > "Yousuf" wrote:
> >
> > > I m having WIN NT 4 and SQl server 6.5 i wanted to shift to WIN 2003 server
> > > and SQL server 2000 on a new machine how can transfer the old SQL 6.5
> > > databaes to new SQL server 2000 on a new nachine
> > > --
> > > Yousuf Khan
> > > Programmer|||6.5 didn't have mdf files, and the storage format was different from the new architecture. So copy
db files or backup doesn't work.
SQL2K has an upgrade wizard that can upgrade from 6.5. Or, use DTS.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:CC047EAF-C9A5-4E92-B1E8-4C368296F52E@.microsoft.com...
> You can use several methods.
> Backup and restore to newserver or Copy both mdf and ldf file to new server
> and attach the database or use DTS.
> any more question pls reply
> hope this will help
> Herbert
>
> "Yousuf" wrote:
>> I m having WIN NT 4 and SQl server 6.5 i wanted to shift to WIN 2003 server
>> and SQL server 2000 on a new machine how can transfer the old SQL 6.5
>> databaes to new SQL server 2000 on a new nachine
>> --
>> Yousuf Khan
>> Programmer|||Hi,
As Tibor pointed out due to architectural changes you can not DETACH and
ATTACH the databases, as well as BACKUP and RESTORE will not
work.
The best method is to use the SQL server 2000 upgrade wizard.This converts
your Schema, logins, users, data and all other objects to sql 2000.
Every thing automatic.
Detailed steps from books online:-
How to perform a SQL Server version 6.5 to SQL Server 2000 upgrade using a
direct pipeline (SQL Server Upgrade Wizard)
Note To run the SQL Server Upgrade Wizard, you must have an instance of
Microsoft® SQL ServerT 2000 already installed on your computer.
To perform a SQL Server version 6.5 to SQL Server 2000 upgrade by named pipe
1.. On the Start menu, point to Programs/Microsoft SQL Server-Switch,
click SQL Server Upgrade Wizard, and then click Next.
2.. In the Data and Object Transfer screen, accept the default selections,
including Named pipe, and then click Next. Verification options are
recommended, but not required. Click Help for information.
3.. On the Logon screen, in the Server name box in the Export server (6.5)
group box, enter the name of the local or remote computer on which Microsoft
SQL Server version 6.5 is installed.
In the Administrator password ('sa') box, enter the sa password for SQL
Server 6.5, and then click Next.
Unless you have changed it already, the default system administrator
password for SQL Server 2000 is blank. Although the SQL Server 2000 default
for the sa password is blank, to conform with best security practices, it
should be changed to a strong password at the first opportunity.
For Import Server (2000), the server name is filled in. Enter the optional
startup arguments, if you want. Click Help for information. When you are
finished setting options, click Next.
4.. In the message box asking if you want to continue, click Yes if you
are ready to upgrade. The SQL Server Upgrade Wizard shuts down SQL Server
6.5 and starts SQL Server 2000.
5.. In the Code Page Selection screen, accept or change the default
settings, and then click Next.
6.. In the Database Selection screen, include the databases to upgrade.
Move any databases you do not want upgraded at this time to the Exclude
list, and then click Next.
Converting all databases is recommended.
7.. In the Database Creation dialog box, select Use the default
configuration or edit the default, and then click Next.
Click Edit to examine and make changes to the proposed disk configuration
within the layout utility. In the Proposed Database Layout box, make changes
as needed. Click Advanced to view Object Details and Drive Summary. When you
are finished, click Accept to return to the SQL Server Upgrade Wizard.
8.. In the System Configuration screen, in System objects to transfer,
select the object types to transfer from SQL Server 6.5 to SQL Server 2000:
a.. Server configuration
Login and remote login registrations and server configuration options
relevant to SQL Server 2000 are transferred as part of the version upgrade.
b.. Replication settings
All articles, subscriptions and publications of each selected database,
plus the distribution database, if any, are transferred and upgraded.
c.. SQL Executive settings
All tasks scheduled by SQL Executive are transferred and upgraded so
that SQL Server 2000 can schedule and run those tasks in SQL Server Agent.
9.. In the System Configuration screen, in Advanced settings, for ANSI
Nulls, select:
a.. Off, if ANSI nulls should not be used when stored procedures are
created. This is the default.
b.. On, if ANSI nulls should be used when stored procedures are created.
10.. In Quoted identifiers, select one of these options, and then click
Next:
a.. Mixed (or don't know), if some of your objects were created with
QUOTED_IDENTIFIER set to ON and others with it set to OFF, or if you are not
sure how they were created.
b.. Off, if all objects should be compiled with QUOTED_IDENTIFIER set to
OFF.
c.. On, if all objects should be compiled with QUOTED_IDENTIFIER set to
ON.
11.. In the Completing the SQL Server Wizard screen, view the summary of
choices you have made. Click View warnings and choices in notepad to open a
text version of the upgrade script. If all options are correct, click
Finish.
The SQL Server Upgrade Script Interpreter screen appears, with information
on the progress of the upgrade.
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eSmyRu7dFHA.1684@.TK2MSFTNGP09.phx.gbl...
> 6.5 didn't have mdf files, and the storage format was different from the
> new architecture. So copy
> db files or backup doesn't work.
> SQL2K has an upgrade wizard that can upgrade from 6.5. Or, use DTS.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:CC047EAF-C9A5-4E92-B1E8-4C368296F52E@.microsoft.com...
>> You can use several methods.
>> Backup and restore to newserver or Copy both mdf and ldf file to new
>> server
>> and attach the database or use DTS.
>> any more question pls reply
>> hope this will help
>> Herbert
>>
>> "Yousuf" wrote:
>> I m having WIN NT 4 and SQl server 6.5 i wanted to shift to WIN 2003
>> server
>> and SQL server 2000 on a new machine how can transfer the old SQL 6.5
>> databaes to new SQL server 2000 on a new nachine
>> --
>> Yousuf Khan
>> Programmer
>
begin 666 note.gif
M1TE&.#EA# `+`(#_`(2&`,# P"'Y! $```$`+ `````,``L`0 (:C(\(H'S[
68%R0&ED;M7,'[%S2YW#1)VJ;4P``.P``
`
end

Monday, March 19, 2012

ERRORS on Insert/Update DB2 from SQL Enterprise Manager (linked server)

I've set up an AS400/DB2 as a linked server, all standard configs (V4R5) on a SQL server 2000 machine and can select all day long. When I go to update or insert it throws an error referencing my driver. I'm using the HIS/OLEDB2 driver. Any ideas? Someone mentioned turning on Journaling on the 400?

Anything at this point would helpRE:
Q1 I'm using the HIS/OLEDB2 driver and can select all day long. When I go to update or insert it throws an error referencing my driver. Any ideas? Anything at this point would help

A1 Perhaps DB2 Connect provides a better odbc driver? Per IBM, DB2 Connect: "Provides extensive application programming tools for developing client-server and web applications using industry standard APIs such as ODBC, ADO, OLE DB, JDBC, SQLJ, DB2 CLI and Embedded SQL."

See: http://www-3.ibm.com/software/data/db2/db2connect/|||I found an answer that worked from a colleague. Thanks.

Friday, March 9, 2012

Errors accessing VFP data.

I have a test system setup in Reading.
SQL Server is on machine EUK01314
IIS 5 also is on machine EUK01314
Reporting services are installed on machine EUK01314
Visual Studo is on machine EUK01314
I can create reports that access SQL server data, or via ODBC, or via OLEDB
to SQL server on EUK01314.
I can use those reports from our Terminal Server, no problem.
I can create reports that access Oracle data, via ODBC, or via OLEDB to
Oracle on a server in Belgim..
I can use those reports from our Terminal Server, no problem.
I draw a total blank accessing Visual FoxPro data.
I can create reports that access VFP data, via ODBC or OLEDB.
Those reports work on EUK01314 in Visual Studio.
Those reports work on EUK01314 in IE via Report Manager.
So why on earth (or any other planet) don't they work on any other machine
?
Connection string: Driver={Microsoft Visual FoxPro
Driver};SourceType=DBC;SourceDB=\\ecengds001\connections\data\connrdp.dbc;Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO
a.. An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
a.. Cannot create a connection to data source 'DataSource1'.
(rsErrorOpeningConnection) Get Online Help
a.. ERROR [S1000] [Microsoft][ODBC Visual FoxPro Driver]Cannot open
file \\ecengds001\connections\data\connrdp.dbc. ERROR [IM006]
[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR
[01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the
version of ODBC behavior that the application requested (see SQLSetEnvAttr).
Connection string: Driver={Microsoft Visual FoxPro
Driver};SourceType=DBC;SourceDB=\\ecengds001\connections\data\connrdp.dbc
a.. An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
a.. Query execution failed for data set 'DataSet1'.
(rsErrorExecutingCommand) Get Online Help
a.. ERROR [S1000] [Microsoft][ODBC Visual FoxPro Driver]Cannot open
file \\ecengds001\connections\data\service.dbf.
Connection String: Provider=VFPOLEDB.1;Data
Source=\\eukfs01\mis_deve\andersonanalysis\data\;Collating Sequence=MACHINE
a.. An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
a.. Cannot create a connection to data source 'DataSource1'.
(rsErrorOpeningConnection) Get Online Help
a.. Invalid path or file name.
Change to use credentials when connecting to datasource. (my user ID and
password)
a.. An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
a.. Cannot create a connection to data source 'DataSource1'.
(rsErrorOpeningConnection) Get Online Help
a.. Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done.
Change to also impersonate user in Windows after connection.
a.. An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
a.. Cannot impersonate user for data source 'DataSource1'.
(rsErrorImpersonatingUser) Get Online Help
a.. Specified method is not supported.
Remember, these work just fine when Report Manager is accessed from
EUK01314.
They do not work if Report Manager is accessed from another machine.
--
PAulHi PAul :-)
I think checking the Deleted box corresponds to SET DELETED ON which
evaluates to DELETED=YES in the connection string so be sure to verify that
you're getting what you want. Sorry I can't help on the ReportingSvcs stuff.
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"PAul Maskens" <pmaskens@.mvps.org> wrote in message
news:efE5woQwEHA.3872@.TK2MSFTNGP11.phx.gbl...
> Connection string: Driver={Microsoft Visual FoxPro
> Driver};SourceType=DBC;SourceDB=\\ecengds001\connections\data\connrdp.dbc;Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO|||You are using SP1, right? This looks similar to an issue that we had with
Access databases (MDBs) on remote shares. Is the FoxPro database on the same
machine as the Report Server? At one point, we were incorrectly adding an
Integrated Security property to the connection string but this should have
been fixed.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"PAul Maskens" <pmaskens@.mvps.org> wrote in message
news:efE5woQwEHA.3872@.TK2MSFTNGP11.phx.gbl...
>I have a test system setup in Reading.
> SQL Server is on machine EUK01314
> IIS 5 also is on machine EUK01314
> Reporting services are installed on machine EUK01314
> Visual Studo is on machine EUK01314
> I can create reports that access SQL server data, or via ODBC, or via
> OLEDB to SQL server on EUK01314.
> I can use those reports from our Terminal Server, no problem.
> I can create reports that access Oracle data, via ODBC, or via OLEDB to
> Oracle on a server in Belgim..
> I can use those reports from our Terminal Server, no problem.
> I draw a total blank accessing Visual FoxPro data.
> I can create reports that access VFP data, via ODBC or OLEDB.
> Those reports work on EUK01314 in Visual Studio.
> Those reports work on EUK01314 in IE via Report Manager.
> So why on earth (or any other planet) don't they work on any other machine
> ?
> Connection string: Driver={Microsoft Visual FoxPro
> Driver};SourceType=DBC;SourceDB=\\ecengds001\connections\data\connrdp.dbc;Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO
> a.. An error has occurred during report processing. (rsProcessingAborted)
> Get Online Help
> a.. Cannot create a connection to data source 'DataSource1'.
> (rsErrorOpeningConnection) Get Online Help
> a.. ERROR [S1000] [Microsoft][ODBC Visual FoxPro Driver]Cannot open
> file \\ecengds001\connections\data\connrdp.dbc. ERROR [IM006]
> [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR
> [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the
> version of ODBC behavior that the application requested (see
> SQLSetEnvAttr).
> Connection string: Driver={Microsoft Visual FoxPro
> Driver};SourceType=DBC;SourceDB=\\ecengds001\connections\data\connrdp.dbc
> a.. An error has occurred during report processing. (rsProcessingAborted)
> Get Online Help
> a.. Query execution failed for data set 'DataSet1'.
> (rsErrorExecutingCommand) Get Online Help
> a.. ERROR [S1000] [Microsoft][ODBC Visual FoxPro Driver]Cannot open
> file \\ecengds001\connections\data\service.dbf.
> Connection String: Provider=VFPOLEDB.1;Data
> Source=\\eukfs01\mis_deve\andersonanalysis\data\;Collating
> Sequence=MACHINE
> a.. An error has occurred during report processing. (rsProcessingAborted)
> Get Online Help
> a.. Cannot create a connection to data source 'DataSource1'.
> (rsErrorOpeningConnection) Get Online Help
> a.. Invalid path or file name.
> Change to use credentials when connecting to datasource. (my user ID and
> password)
> a.. An error has occurred during report processing. (rsProcessingAborted)
> Get Online Help
> a.. Cannot create a connection to data source 'DataSource1'.
> (rsErrorOpeningConnection) Get Online Help
> a.. Multiple-step OLE DB operation generated errors. Check each OLE
> DB status value, if available. No work was done.
> Change to also impersonate user in Windows after connection.
> a.. An error has occurred during report processing. (rsProcessingAborted)
> Get Online Help
> a.. Cannot impersonate user for data source 'DataSource1'.
> (rsErrorImpersonatingUser) Get Online Help
> a.. Specified method is not supported.
> Remember, these work just fine when Report Manager is accessed from
> EUK01314.
> They do not work if Report Manager is accessed from another machine.
> --
> PAul
>|||Thanks Brian.
Sorry for the delay, I've been away in Utrecht.
I started out with the evaluation edition, then installed the SP1 download
onto that.
I had no errors reported during the installation of SP1, so I assume it
really completed OK.
How can I find out what the connection string being used is?
I know what I typed into the Report Manager, but you're implying that
something else is actually sent.
If there's a way to debug this, I'd like to get to the bottom of it.|||Hmm. I'm not sure there is a trace for VFP. Is it OLE DB or ODBC? For SP1,
the string should be the same (we don't add the Integrated Security flag for
other providers). You can make sure SP1 was successful by looking at the
version under http://server/reportserver.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"PAul Maskens" <pmaskens@.mvps.org> wrote in message
news:OZUD5Xx0EHA.1296@.TK2MSFTNGP10.phx.gbl...
> Thanks Brian.
> Sorry for the delay, I've been away in Utrecht.
> I started out with the evaluation edition, then installed the SP1 download
> onto that.
> I had no errors reported during the installation of SP1, so I assume it
> really completed OK.
> How can I find out what the connection string being used is?
> I know what I typed into the Report Manager, but you're implying that
> something else is actually sent.
> If there's a way to debug this, I'd like to get to the bottom of it.
>|||Sorry for the delay, I've been dragged onto a BizTalk project for a while.
Microsoft SQL Server Reporting Services Version 8.00.878.00

Sunday, February 26, 2012

error:no report server found on the specified machine while setting up reporting services config

Hello,

I have installed sql server 2005 along with reporting services... though i am able to design report using business intelligence studio... i am unable to access the report server.... while trying to start the reporting services configuration manager it says no report server found on the specified machine...Invalid Namespace... the installation is local ...

Due to this problem inspite of designing the entire report i am unable to deploy it on the web..since it is asking for a report server...

Can somebody please help me on this...

Thanks in advance...

Nirupa

Hello Nirupa,

Can you make sure you have IIS installed and running? If so, make sure you have /Reports and /ReportServer showing in IIS management.

Then, check to see if you have the following three folders in the C:\Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services directory (where x is the component number for RS). LogFiles, ReportManager, & ReportServer

Hope this helps.

Jarret

|||

Hi Jarret,

Thanks for your suggestions first,

yes I installed IIS and it is running,

and

I couldn't find any folder C:\Program Files\Microsoft SQL Server\MSSQL.1\ Reporting Services directory

...LogFiles, ReportManager, & ReportServer

I could see only path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ Backup

'' \Bin

'' \Install

'' \LOG

'' \repldata

'' \TemplateData

can you suggest me what might had happen.... or what shall be done

Thanks

Nirupa

|||

Do you have a MSSQL.2, MSSQL.3, or MSSQL.4 folder? If so, look in each for the 3 folders I listed in my last post. If you don't have these folders, you probably should reinstall Reporting Services.

Jarret

|||

Hello Jarret

I do not have the folders that you have mentioned....As you have mentioned will probably need reinstallation

Thank you

Nirupa

|||

That is correct, if you don't have these folders, you don't have RS installed. Try to re-install and see if that fixes your problem.

Jarret

|||

Hi Jarret,

Thanks for your suggestions.As you mentioned, I reinstalled SQL reporting services and got the above mention folders(MSSQL.1,MSSQL.2,MSSQL.3) and also I successfully configured reporting services.While trying to deploy my reporting services application in to localhost , I got the below mentioned error : " The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version. (Microsoft.ReportingServices.Designer) "

can you please sugguest me work around for this issue.

error:26 when connecting to sql server express...

I've got an ASP.net 2.0 application that I published and moved to a publicly available server. From my machine it works fine, but not from the production machine.

I'm attempting to connect to a machine that has sql server express installed.

The message in the browser:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

So, I went by the kb article and made sure tcpip is enabled. This uses port 3288. server browser is enabled and the machine accepts remote connections.My connection string is (minus the ip & user/pwd):

<add name="IRRMAConnectionString" connectionString="Data Source=<ip_address>\SQLEXPRESS,3288;Initial Catalog=IRRMA;User ID=<user>;password=<pwd>" providerName="System.Data.SqlClient"/>

I can ping the db server from my app server. On my dev machine it connects. Windows firewall is turned off on the db server.

This seems to be an issue with a firewall, I think?

Any direction would be appreciated.

Thanks,

Scott Jones

Scott@.fourindianajones.com

You should check this out and go from there.

http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

I would say it has to do with the UDP 1434 or the sqlbrowser.exe in the exceptions. But once you follow all the steps you should get closer to the resolution.

error:26 when connecting to sql server express...

I've got an ASP.net 2.0 application that I published and moved to a publicly available server. From my machine it works fine, but not from the production machine.

I'm attempting to connect to a machine that has sql server express installed.

The message in the browser:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

So, I went by the kb article and made sure tcpip is enabled. This uses port 3288. server browser is enabled and the machine accepts remote connections.My connection string is (minus the ip & user/pwd):

<add name="IRRMAConnectionString" connectionString="Data Source=<ip_address>\SQLEXPRESS,3288;Initial Catalog=IRRMA;User ID=<user>;password=<pwd>" providerName="System.Data.SqlClient"/>

I can ping the db server from my app server. On my dev machine it connects. Windows firewall is turned off on the db server.

This seems to be an issue with a firewall, I think?

Any direction would be appreciated.

Thanks,

Scott Jones

Scott@.fourindianajones.com

You should check this out and go from there.

http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

I would say it has to do with the UDP 1434 or the sqlbrowser.exe in the exceptions. But once you follow all the steps you should get closer to the resolution.

Friday, February 24, 2012

Error:20533.Unable to Open Database

In my project I have used VB 6 and CR 8. In my machine the application is working fine. When it was loaded in the customer machine with windows 98 OS, when the report is clicked, it shows the error 'Error:20533 Unable to Open Database'. The Back end used is MS Access. Anybody pl. guide me to install my project? What to do and how to do?

Pl. post your reply due to urgency.Case 1)If u r running ur exe at client side u must place ur Database at the location u have given to ur DSN
Case 2)If u have installed ur setup at client side and just want to replace exe then check whethere u r replacing ur exe in the folder that has been installed on m/c|||Tried. But getting the same error.|||Open the Report and Do verify Database and map the columns if any|||Verify database. In my machine it works. I haven't installed the CR in the client machine. HOw to set right this problem. Is it necessary to load the CR in the client side? Pl. help
Meenakshi.R|||Did you use DSN to connect to CR?|||I haven't used any DSN for CR inthe client side. How to create that DSN for CR? pl. explain.|||How do you connect CR to database? Did you use OLE DB?
Create a DSN pointing to that DB and design reports. In the Client system create the same DSN and point it to that DB.

Otherwise refer this
http://www.businessobjects.com/support/default.asp