Do SQL 7 CALs count for a SQL 2000 install?

Hi,
I have a client with SQL 7 and they need to go to SQL 2000 via an upgrade to
run their now updated app. Do their existing SQL 7 CALs allow then to access
the SQL 2000 server or do they have to buy all the CALs again. Or maybe there
is some upgrade price.
Their vendor has priced 60 SQL 200 CALs at £8,100.00 - which isnt too cheap(!)
Thanks.From the server EULA:
"Any CAL must have the same or later version number than the corresponding
version number of the Server Software being used."
http://www.microsoft.com/sql/howtobuy/default.asp
Ask the vendor about upgrade pricing.
--
David Portas
SQL Server MVP
--

Do regular stored procedures still run when Agent XPs are disabled?

Gurus,
I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
following:
"SQL Server Agent (Agent XPs disabled)"
Agent XPs are extended stored procedures.
http://msdn2.microsoft.com/en-us/library/ms178127.aspx
My question is do regular stored procedures still run?
Spin
Spin,
Yes, they do. Agent XPs disable really means just the SQL Agent procedures
that SQL Agent uses to get its job done.
RLF
"Spin" <Spin@.invalid.com> wrote in message
news:647jmvF2ae2t8U1@.mid.individual.net...
> Gurus,
> I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
> following:
> "SQL Server Agent (Agent XPs disabled)"
> Agent XPs are extended stored procedures.
> http://msdn2.microsoft.com/en-us/library/ms178127.aspx
> My question is do regular stored procedures still run?
> --
> Spin
>
sql

Do regular stored procedures still run when Agent XPs are disabled?

Gurus,
I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
following:
"SQL Server Agent (Agent XPs disabled)"
Agent XPs are extended stored procedures.
http://msdn2.microsoft.com/en-us/library/ms178127.aspx
My question is do regular stored procedures still run?
--
SpinSpin,
Yes, they do. Agent XPs disable really means just the SQL Agent procedures
that SQL Agent uses to get its job done.
RLF
"Spin" <Spin@.invalid.com> wrote in message
news:647jmvF2ae2t8U1@.mid.individual.net...
> Gurus,
> I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
> following:
> "SQL Server Agent (Agent XPs disabled)"
> Agent XPs are extended stored procedures.
> http://msdn2.microsoft.com/en-us/library/ms178127.aspx
> My question is do regular stored procedures still run?
> --
> Spin
>

do not have permission error message

I have been trying to run a script that modifies objects in a SQL Server 2005 database. I am trying to drop an index but I am told the index does not exist or I do not have the correct permissions. An example of one of the commands in the script that I am trying to execute are as follows.

command:
DROP INDEX spices.ADAPTER_MESSAGE.ADAPTER_MESSAGE_SET;

response:
Msg 3701, Level 11, State 6, Line 1
Cannot drop the index 'spices.ADAPTER_MESSAGE.ADAPTER_MESSAGE_SET', because it does not exist or you do not have permission.

The index does exist in the table so the problem must be related to permissions. I am the one who created the database so I am the dbo. I should have permission to do anything in the database, right? So why am I getting this error when my script runs?

thanks,
shimo

The dbo should be able to drop the index.

Is it possible that you are not running as the dbo? Maybe an application role has been set or something like that.

One way to check is

select user_name() to determine your user context.

Another possiblility is that the spices table is not in dbo or your default schema.

Try using the two part name schema.spices.ADAPTER_MESSAGE.ADAPTER_MESSAGE_SET

HTH,

-Steven Gott

S/DET

SQL Server

|||

Is this a SQL x64 bit edition?

We have had similar issue and reported to MS Connect site.

sql

Do lots of COUNTs

Hello :)

I seem to have somehow got myself into a situation where I'm having to run the following SELECTs, one after another, on a single ASP page. This is not tidy. How can I join them all together so I get a single recordset returned with all my stats in different columns?

SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years'
SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL
SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy'
SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULLsome ddl and sample data would help...read the hint sticky at the top of the forum...but I'll give it a shot

bit, would you like a result set of many rows or a single row

Also, why don't you use a sproc?|||Apologies:

CREATE TABLE [dbo].[tblQuiz] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[q1] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q2] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q3] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q4] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q5] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q6] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q7] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q8] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[quizdate] [datetime] NULL ,
[ipaddress] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sessionid] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[score] [int] NULL
)

Sample data attached.

I'd like the results as a single row with 6 columns: one for each of the queries.

I'm not using a sproc because I'm lazy and haven't got round to taking it out of my ASP and putting it into one yet. And I don't know how to put all those SQL queries into one proc.|||Something like

select
sum(case when(id like'123%')then 1 else 0 end) Count1
,sum(case when([name] like'sys%')then 1 else 0 end) Count2
from sysobjects|||Do you want something like this....BTW I am not sure...

CREATE PROCEDURE CountTot

AS

SELECT
(SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years') as Totq3,
(SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL) as Totq4,
(SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy')as Totq5,
(SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes') as Totq6,
(SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes') as ToTq7,
(SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULL) as Totq8
FROM tblQuiz|||This will scan the table only once
SELECT
sum(case when q3='5 years +' OR q3='2 - 4 years' then 1 else 0 end) as Totq3
,sum(case when q4<>'' AND q4 IS NOT NULL then 1 else 0 end) as Totq4
,sum(case when q5='Unhappy' then 1 else 0 end) as Totq5
,sum(case when q6='Yes' then 1 else 0 end) as Totq6
,sum(case when q7='Yes' then 1 else 0 end) as ToTq7
,sum(case when q8<>'' AND q8 IS NOT NULL then 1 else 0 end) as Totq8
FROM tblQuiz|||This will scan the table only once
SELECT
sum(case when q3='5 years +' OR q3='2 - 4 years' then 1 else 0 end) as Totq3
,sum(case when q4<>'' AND q4 IS NOT NULL then 1 else 0 end) as Totq4
,sum(case when q5='Unhappy' then 1 else 0 end) as Totq5
,sum(case when q6='Yes' then 1 else 0 end) as Totq6
,sum(case when q7='Yes' then 1 else 0 end) as ToTq7
,sum(case when q8<>'' AND q8 IS NOT NULL then 1 else 0 end) as Totq8
FROM tblQuiz

That's exactly what I want, thankyou :) Now to try and figure out how it works :confused: :D|||Was that not equivalent to what I had posted ? :(|||Was that not equivalent to what I had posted ? :(nope, quite different :)

Do locks slow other processes?

Hi,
Lets say I have a process running on the server (e.g a stored proc) -
Process A
Then another scheduled process starts to run but is locked up by process A.
Will process A run any slower as it's holding up the second process, or,
does it not affect the performance at all?
Basically, I'm quite happy for the second process to have to wait - but - I
don't want the performance to be radically slowed.
ThanksLondon
http://www.sql-server-performance.com/reducing_locks.asp
"London Developer" <dev@.nowhere.com> wrote in message
news:OSO5ge8lDHA.744@.tk2msftngp13.phx.gbl...
> Hi,
> Lets say I have a process running on the server (e.g a stored proc) -
> Process A
> Then another scheduled process starts to run but is locked up by process
A.
> Will process A run any slower as it's holding up the second process, or,
> does it not affect the performance at all?
> Basically, I'm quite happy for the second process to have to wait - but -
I
> don't want the performance to be radically slowed.
> Thanks
>|||Process A should not be slowed by processes which are waiting on A.
Offcourse if there are more processes which are running or claiming memory,
process A can get slowed. A waiting process should consume very little (or
no) cpu. It is offcourse in a list of processes so the OS uses a very very
VERY small amount of CPU to check or pass over this process when
rescheduling, SQL-server has to set a wait on a lock this consumes very VERY
VERY small amount of cpu.
Offcourse if the process which is blocked by A holds locks and process A
needs those resources you wil have a deadlock. This holds both processes
till a deadlock is detected, one process is 'aborted' the other can
continue.
ben brugman
"London Developer" <dev@.nowhere.com> wrote in message
news:OSO5ge8lDHA.744@.tk2msftngp13.phx.gbl...
> Hi,
> Lets say I have a process running on the server (e.g a stored proc) -
> Process A
> Then another scheduled process starts to run but is locked up by process
A.
> Will process A run any slower as it's holding up the second process, or,
> does it not affect the performance at all?
> Basically, I'm quite happy for the second process to have to wait - but -
I
> don't want the performance to be radically slowed.
> Thanks
>sql

do I need a domain in order to run a cluster?

Hi, I am a complete newbie with windows clustering -
still reading the white papers etc. From what I have read
it seems that I need a Windows Domain in order to run
clustering. Is this true? Is there any way I can cluster
SQL server without having to setup a domain?
thanks
christos
Yes. You must have a domain to run a cluster. No, you can't get around
having a domain.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Christos Kritikos" <anonymous@.discussions.microsoft.com> wrote in message
news:030001c49b56$400d9060$a301280a@.phx.gbl...
> Hi, I am a complete newbie with windows clustering -
> still reading the white papers etc. From what I have read
> it seems that I need a Windows Domain in order to run
> clustering. Is this true? Is there any way I can cluster
> SQL server without having to setup a domain?
> thanks
> christos
>
|||Hi Geoff,
I too have the same doubt. What are some Technical
reasons forcing cluster to run in a domain?
Thanks
Chip.
>--Original Message--
>Yes. You must have a domain to run a cluster. No, you
can't get around
>having a domain.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Christos Kritikos"
<anonymous@.discussions.microsoft.com> wrote in message[vbcol=seagreen]
>news:030001c49b56$400d9060$a301280a@.phx.gbl...
read[vbcol=seagreen]
cluster
>
>.
>
|||You have to have a common security context for the cluster service to manage
resources on multiple machines. Parallel usernames and paswords will not
work. You have to have a domain (NT4 or AD) to provide cluster-wide access
to resources.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:150e01c49c9d$f0cd9560$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi Geoff,
> I too have the same doubt. What are some Technical
> reasons forcing cluster to run in a domain?
> Thanks
> Chip.
> can't get around
> <anonymous@.discussions.microsoft.com> wrote in message
> read
> cluster
|||There is some good information in
281662 Windows 2000 and Windows Server 2003 Cluster Nodes As Domain Controllers
http://support.microsoft.com/?id=281662
Here is a section from the same.
To have Windows Clustering function properly (where the Cluster service starts on each node) the node that forms the cluster must be able to validate the Cluster service domain account, which is the account that you
configure during the Windows Clustering installation. To accomplish this, each node must be able to establish a secure channel with a domain controller to validate this account. If the node cannot validate the
account, the Cluster service does not start. This is also true for other clustered programs that must have account validation for services to start, such as Microsoft SQL Server and Microsoft Exchange.
If you have a cluster deployment in which there is no link with either a Windows NT 4.0 domain, a Windows 2000 domain, or a Windows Server 2003 domain, you must configure the cluster nodes as domain
controllers so that the Cluster service account can always be validated to allow for proper cluster functionality.
If the connectivity between cluster nodes and domain controllers is such that the link is either slow or unreliable, consider having a domain controller co-located with the cluster, or configuring the cluster nodes as
domain controllers.
Consider the following important points when you are deploying Windows Clustering nodes as domain controllers:
--> See the above KB (http://support.microsoft.com/?id=281662)
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Thanks Geoff & Uttam.
Chip
>--Original Message--
>There is some good information in
>281662 Windows 2000 and Windows Server 2003 Cluster Nodes
As Domain Controllers
>http://support.microsoft.com/?id=281662
>Here is a section from the same.
>To have Windows Clustering function properly (where the
Cluster service starts on each node) the node that forms
the cluster must be able to validate the Cluster service
domain account, which is the account that you
>configure during the Windows Clustering installation. To
accomplish this, each node must be able to establish a
secure channel with a domain controller to validate this
account. If the node cannot validate the
>account, the Cluster service does not start. This is also
true for other clustered programs that must have account
validation for services to start, such as Microsoft SQL
Server and Microsoft Exchange.
>If you have a cluster deployment in which there is no
link with either a Windows NT 4.0 domain, a Windows 2000
domain, or a Windows Server 2003 domain, you must
configure the cluster nodes as domain
>controllers so that the Cluster service account can
always be validated to allow for proper cluster
functionality.
>If the connectivity between cluster nodes and domain
controllers is such that the link is either slow or
unreliable, consider having a domain controller co-located
with the cluster, or configuring the cluster nodes as
>domain controllers.
>Consider the following important points when you are
deploying Windows Clustering nodes as domain controllers:
> --> See the above KB
(http://support.microsoft.com/?id=281662)
>Best Regards,
>Uttam Parui
>Microsoft Corporation
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Are you secure? For information about the Strategic
Technology Protection Program and to order your FREE
Security Tool Kit, please visit
http://www.microsoft.com/security.
>Microsoft highly recommends that users with Internet
access update their Microsoft software to better protect
against viruses and security vulnerabilities. The easiest
way to do this is to visit the following websites:
>http://www.microsoft.com/protect
>http://www.microsoft.com/security/guidance/default.mspx
>
>.
>

do I need a DBA for SQL Server?

We currently run an Oracle 8i db for Oracle Applications 10.7. We are
considering moving away from this whole infrastructure to Windows 2003
Server and SQL Server 2005 with a new ERP system.

Currently, we have an Oracle DBA under contract who performs all of the
maintenance required, plus does any and all fixes to the db when there
are problems.

What I have been asked, and couldn't comfortably answer, is do we need
the same sort of arrangement for SQL Server? We have plenty of Windows
server experience in house, but only minor SQL Server experience (I can
set up maintenance jobs, backups, user admin, etc.). If something bad
happens to the ERP system, and the ERP vendor determines that the
problem is not with their app but is with the db itself, how do I get
this resolved quickly? Should I have a contract with a 3rd party to
cover us should this scenario occur?

Thanks for any and all feedback.
Regards,
BrianIf you are in the UK then give me a call and we can sort out a support
contract.

Personally, I'd give a couple of your internal guys some training to bring
them up to a support level for SQL Server and then rely on experts on call
if things go wrong.

A full time DBA is probably going to be expensive if they aren't doing
anything else.

The role of a pure DBA within the SQL Server has reduced somewhat, they tend
to do development but a lot do Business Intelligence stuff now as well.

Hope that helps.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"Brian" <barmand@.amphenolpcd.com> wrote in message
news:1138807906.362534.91700@.g49g2000cwa.googlegro ups.com...
> We currently run an Oracle 8i db for Oracle Applications 10.7. We are
> considering moving away from this whole infrastructure to Windows 2003
> Server and SQL Server 2005 with a new ERP system.
> Currently, we have an Oracle DBA under contract who performs all of the
> maintenance required, plus does any and all fixes to the db when there
> are problems.
> What I have been asked, and couldn't comfortably answer, is do we need
> the same sort of arrangement for SQL Server? We have plenty of Windows
> server experience in house, but only minor SQL Server experience (I can
> set up maintenance jobs, backups, user admin, etc.). If something bad
> happens to the ERP system, and the ERP vendor determines that the
> problem is not with their app but is with the db itself, how do I get
> this resolved quickly? Should I have a contract with a 3rd party to
> cover us should this scenario occur?
> Thanks for any and all feedback.
> Regards,
> Brian

Do I HAVE to specify configuration files in dtexec

Hi,
I have a package that uses configurations and when I run from SSIS Designer it picks up the config files without any problem.

When I run it from dtexec...it doesn't.

Do I have to specify the config files that the package should use even though I have already defined them in the package?

If the answer is "Yes", how do I tell it that the location of the configuration file is stored in an environment variable cos dtexecui doesn't provide an interface to let you do that?

-JamieJamie,

You should not have to specify it as the package should be set up to use the config file. I know this is a stupid question but did you verify that the environment variable was available in the command prompt from which you ran DTExec from? If you did then please file a bug.

As a workaround you can specify a config file from the command line using the config option with the config filename (you can not use an environment variable from the command line).

Thanks,
Matt|||I think I've bottomed this one out.

I came in this morning and the package in question is now working. Strange!!

However, I think I know why. I knocked up a quick repro and saw that the env variable was not available to the command line. As soon as I logged out and back in again it was there.

So it sounds like a Windows "funny". I'm on Win2k3 by the way.

Anyway, it works. Thanks for the tips Matt.

-Jamie

Do cursors use statistics?

I have the following RPC which is followed by 2 fetches (of 100 rows)
and then a close. Query returns 150 rows.
When I run it through the query analyzer, it runs fast. Each time
through the VB app, the first set of 100 takes 20 seconds with over 10
M reads. The second set takes 7 seconds with 2 M reads.
The only thing I can think of is that it is not using the stats. Any
debugging ideas or advice?
the sgrp_ext is a view that joins a 9M row table to a 170M row table on
a single column
declare @.P1 int
set @.P1=180150009
declare @.P2 int
set @.P2=4
declare @.P3 int
set @.P3=1
declare @.P4 int
set @.P4=-1
exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
(SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
(PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
select @.P1, @.P2, @.P3, @.P4Advice: Get rid of the cursor.
"Mark" <mark.kale@.guidant.com> wrote in message
news:1156449002.782650.266430@.74g2000cwt.googlegroups.com...
>I have the following RPC which is followed by 2 fetches (of 100 rows)
> and then a close. Query returns 150 rows.
> When I run it through the query analyzer, it runs fast. Each time
> through the VB app, the first set of 100 takes 20 seconds with over 10
> M reads. The second set takes 7 seconds with 2 M reads.
> The only thing I can think of is that it is not using the stats. Any
> debugging ideas or advice?
> the sgrp_ext is a view that joins a 9M row table to a 170M row table on
> a single column
>
> declare @.P1 int
> set @.P1=180150009
> declare @.P2 int
> set @.P2=4
> declare @.P3 int
> set @.P3=1
> declare @.P4 int
> set @.P4=-1
> exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
> SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
> SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
> SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
> SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
> SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
> (SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
> (PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
> AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
> DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
> SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
> select @.P1, @.P2, @.P3, @.P4
>|||I've never used sp_cursoropen, and can't even find it in my local copy
of BOL. How about using TSQL cursor syntax, or finding a way to
specify "forward_only" or "fast_forward" attributes via the SP?
Default cursor types of dynamic can run pathologically slow, as you
are seeing.
J.
On 24 Aug 2006 12:50:02 -0700, "Mark" <mark.kale@.guidant.com> wrote:
>I have the following RPC which is followed by 2 fetches (of 100 rows)
>and then a close. Query returns 150 rows.
>When I run it through the query analyzer, it runs fast. Each time
>through the VB app, the first set of 100 takes 20 seconds with over 10
>M reads. The second set takes 7 seconds with 2 M reads.
>The only thing I can think of is that it is not using the stats. Any
>debugging ideas or advice?
>the sgrp_ext is a view that joins a 9M row table to a 170M row table on
>a single column
>
>declare @.P1 int
>set @.P1=180150009
>declare @.P2 int
>set @.P2=4
>declare @.P3 int
>set @.P3=1
>declare @.P4 int
>set @.P4=-1
>exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
>SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
>SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
>SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
>SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
>SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
>(SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
>(PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
>AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
>DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
>SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
>select @.P1, @.P2, @.P3, @.P4|||Yes they will statistics. sp_cursoropen is just what the API
uses to access the data - it's just doing what the driver
tells it to do through the application (via ADO, ODBC,
etc). You would probably want to take a look at the VB end
of things as there are different settings on the application
end that could be affecting this.
-Sue
On 24 Aug 2006 12:50:02 -0700, "Mark"
<mark.kale@.guidant.com> wrote:
>I have the following RPC which is followed by 2 fetches (of 100 rows)
>and then a close. Query returns 150 rows.
>When I run it through the query analyzer, it runs fast. Each time
>through the VB app, the first set of 100 takes 20 seconds with over 10
>M reads. The second set takes 7 seconds with 2 M reads.
>The only thing I can think of is that it is not using the stats. Any
>debugging ideas or advice?
>the sgrp_ext is a view that joins a 9M row table to a 170M row table on
>a single column
>
>declare @.P1 int
>set @.P1=180150009
>declare @.P2 int
>set @.P2=4
>declare @.P3 int
>set @.P3=1
>declare @.P4 int
>set @.P4=-1
>exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
>SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
>SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
>SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
>SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
>SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
>(SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
>(PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
>AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
>DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
>SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
>select @.P1, @.P2, @.P3, @.P4

Do cursors use statistics?

I have the following RPC which is followed by 2 fetches (of 100 rows)
and then a close. Query returns 150 rows.
When I run it through the query analyzer, it runs fast. Each time
through the VB app, the first set of 100 takes 20 seconds with over 10
M reads. The second set takes 7 seconds with 2 M reads.
The only thing I can think of is that it is not using the stats. Any
debugging ideas or advice?
the sgrp_ext is a view that joins a 9M row table to a 170M row table on
a single column
declare @.P1 int
set @.P1=180150009
declare @.P2 int
set @.P2=4
declare @.P3 int
set @.P3=1
declare @.P4 int
set @.P4=-1
exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
(SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
(PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
select @.P1, @.P2, @.P3, @.P4Advice: Get rid of the cursor.
"Mark" <mark.kale@.guidant.com> wrote in message
news:1156449002.782650.266430@.74g2000cwt.googlegroups.com...
>I have the following RPC which is followed by 2 fetches (of 100 rows)
> and then a close. Query returns 150 rows.
> When I run it through the query analyzer, it runs fast. Each time
> through the VB app, the first set of 100 takes 20 seconds with over 10
> M reads. The second set takes 7 seconds with 2 M reads.
> The only thing I can think of is that it is not using the stats. Any
> debugging ideas or advice?
> the sgrp_ext is a view that joins a 9M row table to a 170M row table on
> a single column
>
> declare @.P1 int
> set @.P1=180150009
> declare @.P2 int
> set @.P2=4
> declare @.P3 int
> set @.P3=1
> declare @.P4 int
> set @.P4=-1
> exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
> SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
> SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
> SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
> SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
> SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
> (SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
> (PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
> AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
> DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
> SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
> select @.P1, @.P2, @.P3, @.P4
>|||I've never used sp_cursoropen, and can't even find it in my local copy
of BOL. How about using TSQL cursor syntax, or finding a way to
specify "forward_only" or "fast_forward" attributes via the SP?
Default cursor types of dynamic can run pathologically slow, as you
are seeing.
J.
On 24 Aug 2006 12:50:02 -0700, "Mark" <mark.kale@.guidant.com> wrote:

>I have the following RPC which is followed by 2 fetches (of 100 rows)
>and then a close. Query returns 150 rows.
>When I run it through the query analyzer, it runs fast. Each time
>through the VB app, the first set of 100 takes 20 seconds with over 10
>M reads. The second set takes 7 seconds with 2 M reads.
>The only thing I can think of is that it is not using the stats. Any
>debugging ideas or advice?
>the sgrp_ext is a view that joins a 9M row table to a 170M row table on
>a single column
>
>declare @.P1 int
>set @.P1=180150009
>declare @.P2 int
>set @.P2=4
>declare @.P3 int
>set @.P3=1
>declare @.P4 int
>set @.P4=-1
>exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
>SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
>SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
>SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
>SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
>SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
>(SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
>(PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
>AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
>DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
>SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
>select @.P1, @.P2, @.P3, @.P4|||Yes they will statistics. sp_cursoropen is just what the API
uses to access the data - it's just doing what the driver
tells it to do through the application (via ADO, ODBC,
etc). You would probably want to take a look at the VB end
of things as there are different settings on the application
end that could be affecting this.
-Sue
On 24 Aug 2006 12:50:02 -0700, "Mark"
<mark.kale@.guidant.com> wrote:

>I have the following RPC which is followed by 2 fetches (of 100 rows)
>and then a close. Query returns 150 rows.
>When I run it through the query analyzer, it runs fast. Each time
>through the VB app, the first set of 100 takes 20 seconds with over 10
>M reads. The second set takes 7 seconds with 2 M reads.
>The only thing I can think of is that it is not using the stats. Any
>debugging ideas or advice?
>the sgrp_ext is a view that joins a 9M row table to a 170M row table on
>a single column
>
>declare @.P1 int
>set @.P1=180150009
>declare @.P2 int
>set @.P2=4
>declare @.P3 int
>set @.P3=1
>declare @.P4 int
>set @.P4=-1
>exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
>SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
>SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
>SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
>SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
>SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
>(SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
>(PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
>AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
>DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
>SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
>select @.P1, @.P2, @.P3, @.P4

Do child and grandchild packages require passwords?

My packages are now in the filesystem. I have given the parent package a password in the properties window, saved the package and moved it to the run location.

I am using the SQL Job Agent to schedule the execution. My step command line reads:

dtexec /FILE "C:\Path\parentpackage.dtsx" /De mypass /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

Do the the 20 or so child and grandchild packages require passwords to run this way?

Thanks,

IanO

I would say yes, they will require passwords if you are Encrypting with a password. On the children, you might want to consider using DontSaveSensitive instead, and pass in the passwords for connection manager objects via a configuration file or similar.

More importantly, have you tried it for yourself?

DMO doesn't work with SQL Server 2005 (Express)?

I have an applications that uses DMO to access SQL Server 2000.
When I tried to run that app against SQL Server 2005 Express CTP,
I got the below error:
Error
[Microsoft][ODBC SQL Server Driver][SQL Server]
To connect to this server you must use SQL Server Management Studio or SQL Server Management Objects (SMO)
OK
Any idea what I'm missing here?
Thanks,
Sarah
DMO is not supported on SQL2005. You need to use SMO.
Chris Wood
"Sarah" <SarahBram@.HotMail.com> wrote in message
news:%23xZE7FzrFHA.904@.tk2msftngp13.phx.gbl...
>I have an applications that uses DMO to access SQL Server 2000.
> When I tried to run that app against SQL Server 2005 Express CTP,
> I got the below error:
> --
> Error
> --
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> To connect to this server you must use SQL Server Management Studio or SQL
> Server Management Objects (SMO)
> --
> OK
> --
> Any idea what I'm missing here?
> Thanks,
> Sarah
|||Hello Chris,
If I complie in SMO will it be backward compatible with SQL 2000 or do I
need to include both in my applications?
Regards,
John
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:OZiB6lzrFHA.248@.TK2MSFTNGP14.phx.gbl...
> DMO is not supported on SQL2005. You need to use SMO.
> Chris Wood
> "Sarah" <SarahBram@.HotMail.com> wrote in message
> news:%23xZE7FzrFHA.904@.tk2msftngp13.phx.gbl...
>
|||Chris Wood wrote:
> DMO is not supported on SQL2005. You need to use SMO.
> Chris Wood
Chris,
There is a SQL Server DMO in Beta now for SQL Server 2005. I'm guessing
Microsoft decided they didn't want to make upgrading DMO apps any more
difficult than they needed to be. The new SQL-DMO is supposed to work
with SQL 2000 and 7. I'm not sure when it will be available for
download if you're not a beta member.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||David,
Hopefully the new version of DMO comes with the September CTP and before the
RTM (Gold) build.
Thanks
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:Om36c$zrFHA.3596@.TK2MSFTNGP15.phx.gbl...
> Chris Wood wrote:
> Chris,
> There is a SQL Server DMO in Beta now for SQL Server 2005. I'm guessing
> Microsoft decided they didn't want to make upgrading DMO apps any more
> difficult than they needed to be. The new SQL-DMO is supposed to work with
> SQL 2000 and 7. I'm not sure when it will be available for download if
> you're not a beta member.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||David,
Did this make it with the September CTP?
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:Om36c$zrFHA.3596@.TK2MSFTNGP15.phx.gbl...
> Chris Wood wrote:
> Chris,
> There is a SQL Server DMO in Beta now for SQL Server 2005. I'm guessing
> Microsoft decided they didn't want to make upgrading DMO apps any more
> difficult than they needed to be. The new SQL-DMO is supposed to work with
> SQL 2000 and 7. I'm not sure when it will be available for download if
> you're not a beta member.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

DMO doesn't work with SQL Server 2005 (Express)?

I have an applications that uses DMO to access SQL Server 2000.
When I tried to run that app against SQL Server 2005 Express CTP,
I got the below error:
--
Error
--
[Microsoft][ODBC SQL Server Driver][SQL Server]
To connect to this server you must use SQL Server Management Studio or SQL Server Management Objects (SMO)
--
OK
--
Any idea what I'm missing here?
Thanks,
SarahDMO is not supported on SQL2005. You need to use SMO.
Chris Wood
"Sarah" <SarahBram@.HotMail.com> wrote in message
news:%23xZE7FzrFHA.904@.tk2msftngp13.phx.gbl...
>I have an applications that uses DMO to access SQL Server 2000.
> When I tried to run that app against SQL Server 2005 Express CTP,
> I got the below error:
> --
> Error
> --
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> To connect to this server you must use SQL Server Management Studio or SQL
> Server Management Objects (SMO)
> --
> OK
> --
> Any idea what I'm missing here?
> Thanks,
> Sarah|||Hello Chris,
If I complie in SMO will it be backward compatible with SQL 2000 or do I
need to include both in my applications?
Regards,
John
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:OZiB6lzrFHA.248@.TK2MSFTNGP14.phx.gbl...
> DMO is not supported on SQL2005. You need to use SMO.
> Chris Wood
> "Sarah" <SarahBram@.HotMail.com> wrote in message
> news:%23xZE7FzrFHA.904@.tk2msftngp13.phx.gbl...
>>I have an applications that uses DMO to access SQL Server 2000.
>> When I tried to run that app against SQL Server 2005 Express CTP,
>> I got the below error:
>> --
>> Error
>> --
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> To connect to this server you must use SQL Server Management Studio or
>> SQL Server Management Objects (SMO)
>> --
>> OK
>> --
>> Any idea what I'm missing here?
>> Thanks,
>> Sarah
>|||Chris Wood wrote:
> DMO is not supported on SQL2005. You need to use SMO.
> Chris Wood
Chris,
There is a SQL Server DMO in Beta now for SQL Server 2005. I'm guessing
Microsoft decided they didn't want to make upgrading DMO apps any more
difficult than they needed to be. The new SQL-DMO is supposed to work
with SQL 2000 and 7. I'm not sure when it will be available for
download if you're not a beta member.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David,
Hopefully the new version of DMO comes with the September CTP and before the
RTM (Gold) build.
Thanks
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:Om36c$zrFHA.3596@.TK2MSFTNGP15.phx.gbl...
> Chris Wood wrote:
>> DMO is not supported on SQL2005. You need to use SMO.
>> Chris Wood
> Chris,
> There is a SQL Server DMO in Beta now for SQL Server 2005. I'm guessing
> Microsoft decided they didn't want to make upgrading DMO apps any more
> difficult than they needed to be. The new SQL-DMO is supposed to work with
> SQL 2000 and 7. I'm not sure when it will be available for download if
> you're not a beta member.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||David,
Did this make it with the September CTP?
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:Om36c$zrFHA.3596@.TK2MSFTNGP15.phx.gbl...
> Chris Wood wrote:
>> DMO is not supported on SQL2005. You need to use SMO.
>> Chris Wood
> Chris,
> There is a SQL Server DMO in Beta now for SQL Server 2005. I'm guessing
> Microsoft decided they didn't want to make upgrading DMO apps any more
> difficult than they needed to be. The new SQL-DMO is supposed to work with
> SQL 2000 and 7. I'm not sure when it will be available for download if
> you're not a beta member.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

DMO doesn't work with SQL Server 2005 (Express)?

I have an applications that uses DMO to access SQL Server 2000.
When I tried to run that app against SQL Server 2005 Express CTP,
I got the below error:
Error
--
[Microsoft][ODBC SQL Server Driver][SQL Server]
To connect to this server you must use SQL Server Management Studio or SQL S
erver Management Objects (SMO)
--
OK
--
Any idea what I'm missing here?
Thanks,
SarahDMO is not supported on SQL2005. You need to use SMO.
Chris Wood
"Sarah" <SarahBram@.HotMail.com> wrote in message
news:%23xZE7FzrFHA.904@.tk2msftngp13.phx.gbl...
>I have an applications that uses DMO to access SQL Server 2000.
> When I tried to run that app against SQL Server 2005 Express CTP,
> I got the below error:
> --
> Error
> --
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> To connect to this server you must use SQL Server Management Studio or SQL
> Server Management Objects (SMO)
> --
> OK
> --
> Any idea what I'm missing here?
> Thanks,
> Sarah|||Hello Chris,
If I complie in SMO will it be backward compatible with SQL 2000 or do I
need to include both in my applications?
Regards,
John
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:OZiB6lzrFHA.248@.TK2MSFTNGP14.phx.gbl...
> DMO is not supported on SQL2005. You need to use SMO.
> Chris Wood
> "Sarah" <SarahBram@.HotMail.com> wrote in message
> news:%23xZE7FzrFHA.904@.tk2msftngp13.phx.gbl...
>|||Chris Wood wrote:
> DMO is not supported on SQL2005. You need to use SMO.
> Chris Wood
Chris,
There is a SQL Server DMO in Beta now for SQL Server 2005. I'm guessing
Microsoft decided they didn't want to make upgrading DMO apps any more
difficult than they needed to be. The new SQL-DMO is supposed to work
with SQL 2000 and 7. I'm not sure when it will be available for
download if you're not a beta member.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David,
Hopefully the new version of DMO comes with the September CTP and before the
RTM (Gold) build.
Thanks
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:Om36c$zrFHA.3596@.TK2MSFTNGP15.phx.gbl...
> Chris Wood wrote:
> Chris,
> There is a SQL Server DMO in Beta now for SQL Server 2005. I'm guessing
> Microsoft decided they didn't want to make upgrading DMO apps any more
> difficult than they needed to be. The new SQL-DMO is supposed to work with
> SQL 2000 and 7. I'm not sure when it will be available for download if
> you're not a beta member.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||David,
Did this make it with the September CTP?
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:Om36c$zrFHA.3596@.TK2MSFTNGP15.phx.gbl...
> Chris Wood wrote:
> Chris,
> There is a SQL Server DMO in Beta now for SQL Server 2005. I'm guessing
> Microsoft decided they didn't want to make upgrading DMO apps any more
> difficult than they needed to be. The new SQL-DMO is supposed to work with
> SQL 2000 and 7. I'm not sure when it will be available for download if
> you're not a beta member.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

DMO Anonymous Merge Pull

I adapted Hilary's DMO Example, and have tried ReplCtrl\VB\ReplSamp.vbp ...
WHAT CODE DO I NEED to .Initialize .Run .Terminate the SQLMerge?
Subscription was created using Windows Synchronization Manager,
Administrator Account on WinXPPro Laptop. VB is running in MS Access XP
Runtime .ade
CODE looks good, but is still missing 'something'
DMO Example -- last line is
objMergePullSubscriptions.Add objMergPullSubscription
Obviously FAILS because 'The subscription already exists.'
AIRCODE; Run via VPN to namedserver
Option Compare Database
Option Explicit
Private Sub cmdMergePublication_Click()
'On Error GoTo Err_cmdMergePublication_Click
Const SQLDMOSubscription_Anonymous = 2
Const SQLDMOReplSecurity_Normal = 0
Const SQLDMOReplSecurity_Integrated = 1
Const SQLDMOSubscription_All = 3
Const SQLDMOMergeSubscriber_Default = 2
Dim objServer, objReplication, objReplicationDatabases, objReplicationDatabase
Dim objMergePullSubscription, objMergePullSubscriptions,
objReplicationSecurity
Set objServer = CreateObject("SQLDMO.SQLServer")
objServer.Connect ".", "sa", "nyrv%fa"
Set objReplication = objServer.Replication
Set objReplicationDatabases = objReplication.ReplicationDatabases
Set objReplicationDatabase = objReplicationDatabases("CareSQL50207")
Set objMergePullSubscription = CreateObject("SQLDMO.MergePullSubscription2")
Set objReplicationSecurity = objMergePullSubscription.DistributorSecurity
objReplicationSecurity.SecurityMode = SQLDMOReplSecurity_Normal
objReplicationSecurity.StandardLogin = "sa"
objReplicationSecurity.StandardPassword = "d23&tmv"
With objMergePullSubscription
.AltSnapshotFolder = "C:\temp\"
.Distributor = "GSHSBS2000"
.DistributorSecurity.SecurityMode = SQLDMOReplSecurity_Normal
.DistributorSecurity.StandardLogin = "sa"
.DistributorSecurity.StandardPassword = "nyrv%fa"
.Publisher = "GSHSBS2000"
.PublicationDB = "CareSQL50207"
.Publication = "CareSQL50207"
.PublisherSecurity.SecurityMode = SQLDMOReplSecurity_Normal
.PublisherSecurity.StandardLogin = "sa"
.PublisherSecurity.StandardPassword = "nyrv%fa"
.SubscriberType = SQLDMOMergeSubscriber_Default
.SubscriptionType = SQLDMOSubscription_Anonymous
.SubscriberSecurityMode = SQLDMOReplSecurity_Normal
.SubscriberLogin = "sa"
.SubscriberPassword = "d23&tmv"
.UseFTP = False
End With
Set objMergePullSubscriptions = objReplicationDatabase.MergePullSubscriptions
'$$$$$ STOPS here: [MS][ODBC][SQLServer] The subscription already exists.
objMergePullSubscriptions.Add objMergePullSubscription
Set objMergePullSubscription = Nothing
Set objMergePullSubscriptions = Nothing
Set objReplicationDatabases = Nothing
Set objReplication = Nothing
Set objServer = Nothing
Exit_cmdMergePublication_Click:
Exit Sub
Err_cmdMergePublication_Click:
MsgBox Err.Description
Resume Exit_cmdMergePublication_Click
End Sub
TIA
Aubrey Kelley
this is to create the job, not to start it. I've been puzzling for a good
long while on trying to figure out how to start it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Aubrey" <Aubrey@.discussions.microsoft.com> wrote in message
news:9D3A8AF9-7A89-4BCA-B3A4-0D564D00C217@.microsoft.com...
> I adapted Hilary's DMO Example, and have tried ReplCtrl\VB\ReplSamp.vbp
...
> WHAT CODE DO I NEED to .Initialize .Run .Terminate the SQLMerge?
>
> Subscription was created using Windows Synchronization Manager,
> Administrator Account on WinXPPro Laptop. VB is running in MS Access XP
> Runtime .ade
> CODE looks good, but is still missing 'something'
> DMO Example -- last line is
> objMergePullSubscriptions.Add objMergPullSubscription
> Obviously FAILS because 'The subscription already exists.'
>
> AIRCODE; Run via VPN to namedserver
> Option Compare Database
> Option Explicit
> Private Sub cmdMergePublication_Click()
> 'On Error GoTo Err_cmdMergePublication_Click
> Const SQLDMOSubscription_Anonymous = 2
> Const SQLDMOReplSecurity_Normal = 0
> Const SQLDMOReplSecurity_Integrated = 1
> Const SQLDMOSubscription_All = 3
> Const SQLDMOMergeSubscriber_Default = 2
> Dim objServer, objReplication, objReplicationDatabases,
objReplicationDatabase
> Dim objMergePullSubscription, objMergePullSubscriptions,
> objReplicationSecurity
> Set objServer = CreateObject("SQLDMO.SQLServer")
> objServer.Connect ".", "sa", "nyrv%fa"
> Set objReplication = objServer.Replication
> Set objReplicationDatabases = objReplication.ReplicationDatabases
> Set objReplicationDatabase = objReplicationDatabases("CareSQL50207")
> Set objMergePullSubscription =
CreateObject("SQLDMO.MergePullSubscription2")
> Set objReplicationSecurity = objMergePullSubscription.DistributorSecurity
> objReplicationSecurity.SecurityMode = SQLDMOReplSecurity_Normal
> objReplicationSecurity.StandardLogin = "sa"
> objReplicationSecurity.StandardPassword = "d23&tmv"
> With objMergePullSubscription
> .AltSnapshotFolder = "C:\temp\"
> .Distributor = "GSHSBS2000"
> .DistributorSecurity.SecurityMode = SQLDMOReplSecurity_Normal
> .DistributorSecurity.StandardLogin = "sa"
> .DistributorSecurity.StandardPassword = "nyrv%fa"
> .Publisher = "GSHSBS2000"
> .PublicationDB = "CareSQL50207"
> .Publication = "CareSQL50207"
> .PublisherSecurity.SecurityMode = SQLDMOReplSecurity_Normal
> .PublisherSecurity.StandardLogin = "sa"
> .PublisherSecurity.StandardPassword = "nyrv%fa"
> .SubscriberType = SQLDMOMergeSubscriber_Default
> .SubscriptionType = SQLDMOSubscription_Anonymous
> .SubscriberSecurityMode = SQLDMOReplSecurity_Normal
> .SubscriberLogin = "sa"
> .SubscriberPassword = "d23&tmv"
> .UseFTP = False
> End With
> Set objMergePullSubscriptions =
objReplicationDatabase.MergePullSubscriptions
> '$$$$$ STOPS here: [MS][ODBC][SQLServer] The subscription already exists.
> objMergePullSubscriptions.Add objMergePullSubscription
> Set objMergePullSubscription = Nothing
> Set objMergePullSubscriptions = Nothing
> Set objReplicationDatabases = Nothing
> Set objReplication = Nothing
> Set objServer = Nothing
> Exit_cmdMergePublication_Click:
> Exit Sub
> Err_cmdMergePublication_Click:
> MsgBox Err.Description
> Resume Exit_cmdMergePublication_Click
> End Sub
> --
> TIA
> Aubrey Kelley
|||Ouch! At least now I do not feel 'so dumb'. Maybe PSS will be helpful now
that we are so far. Nothing but dead ends in the past ...
Aubrey
"Hilary Cotter" wrote:

> this is to create the job, not to start it. I've been puzzling for a good
> long while on trying to figure out how to start it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Aubrey" <Aubrey@.discussions.microsoft.com> wrote in message
> news:9D3A8AF9-7A89-4BCA-B3A4-0D564D00C217@.microsoft.com...
> ...
> objReplicationDatabase
> CreateObject("SQLDMO.MergePullSubscription2")
> objReplicationDatabase.MergePullSubscriptions
>
>
|||here is what I have so far - this will spill the anmes of the anonymous pull
subscriptions in the database Northwindsub.
Can't seem to get it to resolve to the job_id's for the merge pull though.
set objSQLServer=CreateObject("SQLDMO.SQLServer")
objSQLServer.loginSecure=True
objSQLServer.Connect "Publisher"
set objReplication=objSQLServer.Replication
wscript.echo objReplication.Distributor.DistributionServer
set objReplicationDatabase =
objReplication.ReplicationDatabases("Northwindsub" )
for each objMergepullSubscription in
objReplicationDatabase.MergepullSubscriptions
wscript.echo "objMergepullSubscription.Name"
wscript.echo objMergepullSubscription.Name
wscript.echo "objMergepullSubscription.MergeJObID"
wscript.echo objMergepullSubscription.MergeJObID
set QueryResults=objMergepullSubscription.EnumJobInfo
for b=1 to QueryResults.Rows
for a=1 to QueryResults.Columns
wscript.echo QueryResults.ColumnName(A)
wscript.echo QueryResults.GetColumnString(b,A)
next
next
wscript.echo objReplication.Distributor.DistributionServer
set QueryResults=objReplication.Distributor.EnumMergeA gentViews()
for b=1 to QueryResults.Rows
for a=1 to QueryResults.Columns
wscript.echo QueryResults.ColumnName(A)
wscript.echo QueryResults.GetColumnString(b,A)
next
next
set objDistributor=Nothing
next
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Aubrey" <Aubrey@.discussions.microsoft.com> wrote in message
news:2F4F23EF-3F58-44CD-8620-4BBA107F649F@.microsoft.com...[vbcol=seagreen]
> Ouch! At least now I do not feel 'so dumb'. Maybe PSS will be helpful now
> that we are so far. Nothing but dead ends in the past ...
> Aubrey
> "Hilary Cotter" wrote:
good[vbcol=seagreen]
ReplCtrl\VB\ReplSamp.vbp[vbcol=seagreen]
XP[vbcol=seagreen]
objMergePullSubscription.DistributorSecurity[vbcol=seagreen]
exists.[vbcol=seagreen]

DLL

I have a report that need to use a decrypting dll. I can save the DLL on my
locat drive and run the report locally in the report designer. Ho do I get
it to work with a published report. I have put the dll on the local drive of
the server but it does not work when I run the report in report manager.There should be detailed instructions in RS books under "custom assemblies"
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"johnE" <johnE@.discussions.microsoft.com> wrote in message
news:535467D0-6489-4944-BB1C-7C445B36FAE5@.microsoft.com...
> I have a report that need to use a decrypting dll. I can save the DLL on
my
> locat drive and run the report locally in the report designer. Ho do I
get
> it to work with a published report. I have put the dll on the local drive
of
> the server but it does not work when I run the report in report manager.

division in a view

In trying to simplify an overly complex query [at one time, 1000+ views and
14 queries run in query analyzer!], I am trying to incorporate many of the
different views/queries together but am having troubles with one thing;
trying to divide one of the columns in a view.
SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
Doesn't workPerhaps you can incorporate a CASE statement
select
case MLD1ST when 0 then 0
else
100 * (RCD1ST / MLD1ST)
end
from <your table>
"notme" <not@.me.com> wrote in message
news:uOoxI89LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> In trying to simplify an overly complex query [at one time, 1000+ views
and
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work|||Are you encountering a divide by zero error? Make sure that you code the
T-SQL in such a way as to not have any problems with a divide by zero error:
create table #foo (RCD1ST int, MLD1ST decimal(5,2))
insert into #foo values (5, 2)
insert into #foo values (10, 2)
insert into #foo values (15, 0)
insert into #foo values (20, null)
PRINT ''
PRINT '********************* this fails *********************'
SELECT *, 100 * (RCD1ST / MLD1ST) AS POR FROM #foo
go
PRINT ''
PRINT '********************* this works *********************'
SELECT *, CASE WHEN MLD1ST = 0 THEN NULL ELSE 100 * (RCD1ST / MLD1ST) END AS
POR FROM #foo
Keith
"notme" <not@.me.com> wrote in message
news:uOoxI89LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> In trying to simplify an overly complex query [at one time, 1000+ views
and
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work|||You can not reference a column alias in the same column list. Try:
SELECT
FIPS,
[MTG TYPE],
COUNT(RCVD_1ST) AS RCD1ST,
COUNT(MAILED_1ST) AS MLD1ST,
AVG(DATEDIFF(day, MAILED_1ST, RCVD_1ST)) AS mldgap,
AVG(DATEDIFF(day, CLOSING, MAILED_1ST)) AS closegap,
100 * (COUNT(RCVD_1ST) / nullif(COUNT(MAILED_1ST), 0)) AS POR
from ...
AMB
"notme" wrote:

> In trying to simplify an overly complex query [at one time, 1000+ views an
d
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work
>|||Not really, depend if sql server decides to escalate the lock .
Example:
-- connection 1
use northwind
go
begin transaction
update orders
set orderdate = orderdate
where orderid = 10250
-- connection 2
use northwind
go
select * from orders
where orderid < 10250 or orderid > 10250
-- connection 1
rollback transaction
AMB
"notme" wrote:

> In trying to simplify an overly complex query [at one time, 1000+ views an
d
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> Not really, depend if sql server decides to escalate the lock .
> Example:
> -- connection 1
> use northwind
> go
> begin transaction
> update orders
> set orderdate = orderdate
> where orderid = 10250
> -- connection 2
> use northwind
> go
> select * from orders
> where orderid < 10250 or orderid > 10250
> -- connection 1
> rollback transaction
>
> AMB
>
> "notme" wrote:
>|||On 3/23/2005 1:49:09 PM, "examnotes" wrote:
>You can not reference a column alias in the same column list. Try:
>SELECT
> FIPS,
> [MTG TYPE],
> COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST,
> AVG(DATEDIFF(day, MAILED_1ST, RCVD_1ST)) AS mldgap,
> AVG(DATEDIFF(day, CLOSING, MAILED_1ST)) AS closegap,
> 100 * (COUNT(RCVD_1ST) / nullif(COUNT(MAILED_1ST), 0)) AS POR
>from ...
>
>AMB
>
>
That was it - thanks
jeff

Distrubuter has sick hardware. Need to change box.

We run our distribution on another server ie not on the publisher.
We have had some hardware errors and to be honest it's an old machine and
spares are getting sparse.
Is there a step by step for moving distribution to another server?
Thanks
Paul
The short answer is no. The longer answer is no there is not step by step
guide for moving a distributor to another server.
An even longer answer is, no, but it is possible to do a tape restore to the
new distributor with the same name bounce it and hope for the best. I don't
like remote distributors. DBAs IMHO are too quick to migrate to them, and
you need to cluster them otherwise you have a single point of failure which
can be problematic for you as you have discovered.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Cahill" <noname@.anon.com> wrote in message
news:e6FVdryzFHA.2792@.tk2msftngp13.phx.gbl...
> We run our distribution on another server ie not on the publisher.
> We have had some hardware errors and to be honest it's an old machine and
> spares are getting sparse.
> Is there a step by step for moving distribution to another server?
> Thanks
> Paul
>

Distributor SQLSERVERAgent Account

What account should the SqlServerAgent run under at the distributor
(dist. and publisher are same machine)?
Mine is running under .\sqladmin but I keep getting
Error: The schema script
'\\WSCLAN08\ReplData\unc\WSCLAN08_TMS_TMS\20050209 054316\Ltab_Years_1.sch'
could not be propagated to the subscriber.
At subscriber, SqlServerAgent is running under LocalSystem.
\\WSCLAN08\ReplData\ is public share and user has permissions (incl.
sqladmin).
Also, user is in PAL of publication (incl. sqladmin).
Thanks.
Is this pull? If so, your subscriber's sql server agent should be running
under the same account as the publisher's SQL Server agent, or an account
that is part of the local admin group on the publisher.
If this is not possible the SQL Server agent account on the subscriber
should have rigths to read the snapshot share and underlying files and
folders. You will probably have to craft another snapshot share on the
publisher for this.
For push subscriptions you should not have to worry about this unless you
have a remote distributor.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<richerwin@.ubs-europe.org> wrote in message
news:1107929613.327118.195320@.l41g2000cwc.googlegr oups.com...
> What account should the SqlServerAgent run under at the distributor
> (dist. and publisher are same machine)?
> Mine is running under .\sqladmin but I keep getting
> Error: The schema script
> '\\WSCLAN08\ReplData\unc\WSCLAN08_TMS_TMS\20050209 054316\Ltab_Years_1.sch'
> could not be propagated to the subscriber.
> At subscriber, SqlServerAgent is running under LocalSystem.
> \\WSCLAN08\ReplData\ is public share and user has permissions (incl.
> sqladmin).
> Also, user is in PAL of publication (incl. sqladmin).
> Thanks.
>
|||Hilary Cotter wrote:
> Is this pull? If so, your subscriber's sql server agent should be
running
> under the same account as the publisher's SQL Server agent, or an
account
> that is part of the local admin group on the publisher.
Yes, this is annonymous pull over VPN.
BTW, if default installations are used, what is the difference between
running SQL Server Agent as LocalSystem and sa? What is LocalSystem? If
SQL Server Agent is to run under sqladmin, for example, does sqladmin
have to be created as a domain administrator on the subscribers'
computer?

> If this is not possible the SQL Server agent account on the
subscriber
> should have rigths to read the snapshot share and underlying files
and
> folders. You will probably have to craft another snapshot share on
the
> publisher for this.
I've got everbody and his uncle given full rights to the share, but
since my subscriber SQL Server Agent is running under LocalSystem, I
guess all bets are off as to why he can't access the share.
Sorry for all these basic questions, but my accts are all screwed up
and my brain is mush.
Thanks
|||There is some confusion here between service startup
accounts and SQL Server logins.
Service startup accounts can be found in control panel,
services and we are really interested in the sql server
agent one, which runs the replication jobs. This must be
a domain user account if you are to replicate from one
machine to another. LocalSystem can be used if you're
doing it all on one box.
If you're on a trusted environment, then the domain user
account on the subscriber for pull subscriptions must
have rights to the snapshot share. If it's non-trusted,
you'll need FTP.
'sa' is a SQL Server login. If your agents are set to use
impersonation, then the whole thing will be using windows
security and you can forget sql logins. If you're using a
non-trusted environment, you'll be obliged to use sql
logins.
This is explained in replication, security in BOL but
admittedly is not at all a straightforward topic
Rgds,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
>--Original Message--
>Hilary Cotter wrote:
agent should be[vbcol=seagreen]
>running
agent, or an
>account
>Yes, this is annonymous pull over VPN.
>BTW, if default installations are used, what is the
difference between
>running SQL Server Agent as LocalSystem and sa? What is
LocalSystem? If
>SQL Server Agent is to run under sqladmin, for example,
does sqladmin
>have to be created as a domain administrator on the
subscribers'[vbcol=seagreen]
>computer?
on the[vbcol=seagreen]
>subscriber
underlying files[vbcol=seagreen]
>and
snapshot share on
>the
>I've got everbody and his uncle given full rights to the
share, but
>since my subscriber SQL Server Agent is running under
LocalSystem, I
>guess all bets are off as to why he can't access the
share.
>Sorry for all these basic questions, but my accts are
all screwed up
>and my brain is mush.
>Thanks
>.
>