Having trouble creating a login account.

Hello,
I am trying to create a TEST environment by replicating everything on
the PRODUCTION environment.
When creating a login under Sercurity, I am using the same user name
and passowrd. However, I am getting the following error message:
Password validation failed. The password does not meet Windows policy
requirements becuase it is not complex enough. ( Microsoft SQL Server,
Error: 15118 )
Is there some kind of setting I need to change to have lower
requirements?
Thank you!
Hi,
When a SQL Server 2005 db runs on Windows 2003, there is a way to enforce
password policy.
I believe this is on per login basis. So you should be able to switch this
off while creating a login.
Look for the keyword CHECK_POLICY in BOL 2005.
Thank you.
Regards,
Karthik
"bcap" wrote:

> Hello,
> I am trying to create a TEST environment by replicating everything on
> the PRODUCTION environment.
> When creating a login under Sercurity, I am using the same user name
> and passowrd. However, I am getting the following error message:
> Password validation failed. The password does not meet Windows policy
> requirements becuase it is not complex enough. ( Microsoft SQL Server,
> Error: 15118 )
> Is there some kind of setting I need to change to have lower
> requirements?
> Thank you!
>

Having trouble creating a login account.

Hello,
I am trying to create a TEST environment by replicating everything on
the PRODUCTION environment.
When creating a login under Sercurity, I am using the same user name
and passowrd. However, I am getting the following error message:
Password validation failed. The password does not meet Windows policy
requirements becuase it is not complex enough. ( Microsoft SQL Server,
Error: 15118 )
Is there some kind of setting I need to change to have lower
requirements?
Thank you!
Hi
"bcap" wrote:

> Hello,
> I am trying to create a TEST environment by replicating everything on
> the PRODUCTION environment.
> When creating a login under Sercurity, I am using the same user name
> and passowrd. However, I am getting the following error message:
> Password validation failed. The password does not meet Windows policy
> requirements becuase it is not complex enough. ( Microsoft SQL Server,
> Error: 15118 )
> Is there some kind of setting I need to change to have lower
> requirements?
> Thank you!
>
I assume this is SQL Server 2005!!
You can create your login using the CREATE LOGIN statement and the option
CHECK_POLICY = OFF which will ignore password policy checking, but you should
really use policy checking on both systems and strong passwords on all
accounts.
John

Having trouble creating a login account.

Hello,
I am trying to create a TEST environment by replicating everything on
the PRODUCTION environment.
When creating a login under Sercurity, I am using the same user name
and passowrd. However, I am getting the following error message:
Password validation failed. The password does not meet Windows policy
requirements becuase it is not complex enough. ( Microsoft SQL Server,
Error: 15118 )
Is there some kind of setting I need to change to have lower
requirements?
Thank you!Hi
"bcap" wrote:

> Hello,
> I am trying to create a TEST environment by replicating everything on
> the PRODUCTION environment.
> When creating a login under Sercurity, I am using the same user name
> and passowrd. However, I am getting the following error message:
> Password validation failed. The password does not meet Windows policy
> requirements becuase it is not complex enough. ( Microsoft SQL Server,
> Error: 15118 )
> Is there some kind of setting I need to change to have lower
> requirements?
> Thank you!
>
I assume this is SQL Server 2005!!
You can create your login using the CREATE LOGIN statement and the option
CHECK_POLICY = OFF which will ignore password policy checking, but you shoul
d
really use policy checking on both systems and strong passwords on all
accounts.
John

Having trouble creating a login account.

Hello,
I am trying to create a TEST environment by replicating everything on
the PRODUCTION environment.
When creating a login under Sercurity, I am using the same user name
and passowrd. However, I am getting the following error message:
Password validation failed. The password does not meet Windows policy
requirements becuase it is not complex enough. ( Microsoft SQL Server,
Error: 15118 )
Is there some kind of setting I need to change to have lower
requirements?
Thank you!Hi,
When a SQL Server 2005 db runs on Windows 2003, there is a way to enforce
password policy.
I believe this is on per login basis. So you should be able to switch this
off while creating a login.
Look for the keyword CHECK_POLICY in BOL 2005.
Thank you.
Regards,
Karthik
"bcap" wrote:
> Hello,
> I am trying to create a TEST environment by replicating everything on
> the PRODUCTION environment.
> When creating a login under Sercurity, I am using the same user name
> and passowrd. However, I am getting the following error message:
> Password validation failed. The password does not meet Windows policy
> requirements becuase it is not complex enough. ( Microsoft SQL Server,
> Error: 15118 )
> Is there some kind of setting I need to change to have lower
> requirements?
> Thank you!
>sql

Having trouble creating a login account.

Hello,
I am trying to create a TEST environment by replicating everything on
the PRODUCTION environment.
When creating a login under Sercurity, I am using the same user name
and passowrd. However, I am getting the following error message:
Password validation failed. The password does not meet Windows policy
requirements becuase it is not complex enough. ( Microsoft SQL Server,
Error: 15118 )
Is there some kind of setting I need to change to have lower
requirements?
Thank you!Hi
"bcap" wrote:
> Hello,
> I am trying to create a TEST environment by replicating everything on
> the PRODUCTION environment.
> When creating a login under Sercurity, I am using the same user name
> and passowrd. However, I am getting the following error message:
> Password validation failed. The password does not meet Windows policy
> requirements becuase it is not complex enough. ( Microsoft SQL Server,
> Error: 15118 )
> Is there some kind of setting I need to change to have lower
> requirements?
> Thank you!
>
I assume this is SQL Server 2005!!
You can create your login using the CREATE LOGIN statement and the option
CHECK_POLICY = OFF which will ignore password policy checking, but you should
really use policy checking on both systems and strong passwords on all
accounts.
John

having the same definition for 2 indexes

CREATE NONCLUSTERED INDEX [operation_breakdown_machine_pk] ON [dbo].[operation_breakdown] ([machine_pk] ASC)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_operation_breakdown_machine_pk] ON [dbo].[operation_breakdown] ([machine_pk] ASC)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

aren't they exactly the same? what are the possible effects of having the same definition for 2 indexes?

could it be possible that this has been overlooked by the administrator? i would just like to verify if this could have been done with intent or have been accidental.

thanks for any help!

I guess this would be accidental since having 2 identical indexes will not help you in any way. It will actually be worse for performance.
The effect is probably that SQL Server has to maintain two indexes.

|||thanks! it indeed was an accident.

Having problems creating a function or procedure

When I try to create functions or procedures sqlplus just hangs.
I have tried creating several function and procedures written in the
developing pl/sql manual. The 1st procedure I created worked. All the others will not compile. Any ideas on the problem?
ReneeHello,

could it be, that the procedures is still in creation status by another process - or it is running ?

Just an idea

Regards
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

Having problem with sql create

The following is a create that I was trying to run in SQL/2000 SQL
Analyser:

CREATE TABLE tblxyz as (Select * from tblPlayers);

tblPlayers has 2 rows in it.

I continue to get the error

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.

I have tried it with and without the () and it works on Oracle but not
SQL/2K. Don't know why.

This has all developed because of another problem.

First I have select with a complex join all records with a certain
employee ID.

Then I have to select the top 20 (by date) records from the first
select.

From the second select results I have to select the top 10 by a
numeric field.

The only way I thought of doing it was to create temporary tables (see
original question).SELECT * INTO tblxyz FROM tblPlayers

if you want an empty table with only the schema of tblPlayers use a WHERE
1=0

"Jim R" <JimReid@.comcast.net> wrote in message
news:7eb6df4b.0407150949.15a9468c@.posting.google.c om...
> The following is a create that I was trying to run in SQL/2000 SQL
> Analyser:
> CREATE TABLE tblxyz as (Select * from tblPlayers);
> tblPlayers has 2 rows in it.
> I continue to get the error
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'AS'.
> I have tried it with and without the () and it works on Oracle but not
> SQL/2K. Don't know why.
> This has all developed because of another problem.
> First I have select with a complex join all records with a certain
> employee ID.
> Then I have to select the top 20 (by date) records from the first
> select.
> From the second select results I have to select the top 10 by a
> numeric field.
> The only way I thought of doing it was to create temporary tables (see
> original question).|||> CREATE TABLE tblxyz as (Select * from tblPlayers);

The SQL Server equivalent is

SELECT * INTO tblxyz FROM tblPlayers ;

> The only way I thought of doing it was to create temporary tables (see
> original question).

Without more of a spec (DDL for the tables, some INSERT statements of sample
data) it's impossible for us to know if that's really the best way to
achieve what you want.

--
David Portas
SQL Server MVP
--|||Thank you. That works great. Now I create tbltemp and from tbltemp I
create a tbltempa and then drop tbltemp. All in a stored procedure.
Then after I'm done with my selection of the recordset I'll drop the
tbltempa.

Thanks again.

Jim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||SQL is a declarative language. To get the most out of SQL, start by writing
a query that will produce the end result you require rather than thinking of
procedural solutions like [Query 1] -> [Temp Table] -> [Query 2] -> etc.
From your description I doubt that temp tables are the most concise,
efficient or maintainable solution to your problem.

--
David Portas
SQL Server MVP
--|||David,

You are right. My problem was that I didn't know how to create a select
within a select. What I ended up with was just that and it is all in a
single stored procedure with no temporary tables. Works really well.

My biggest problem was all new things I was trying to do. I have always
done simple select statements before both with Views and Stored
Procedures.

Then I discovered that I needed to feed parameters to the first select
which knocked out Views. Then I needed only the top 20 by date and from
the top 20 the lowest 10(numerically) of those top 20.

It got very confusing real fast when I had a stored procedure that
select via param for the top 20. It would pick the top 20 first and
then the parms. Which wasn't the sequence I wanted.

Since I was only running on SQL/2k I ended up with the following stored
procedure:
CREATE PROCEDURE [SelectUSGA1]]
@.PlayerID integer
AS
SELECT TOP 10 WITH TIES dbo.tblScores.[Date], dbo.tblScores.Player,
dbo.tblScores.HandiDiff, dbo.tblScores.Tees, dbo.tblScores.H1,
dbo.tblScores.H2, dbo.tblScores.H3, dbo.tblScores.Course,
dbo.tblCourse.CourseID, dbo.tblCourse.CourseName, dbo.tblCourse.Hole1,
dbo.tblCourse.Hole2, dbo.tblCourse.Hole3, dbo.tblCourse.Hole4,
dbo.tblCourse.Hole5, dbo.tblCourse.Hole6, dbo.tblCourse.Hole7,
dbo.tblCourse.Hole8, dbo.tblCourse.Hole9, dbo.tblCourse.Hole10,
dbo.tblCourse.Hole11, dbo.tblCourse.Hole12, dbo.tblCourse.Hole13,
dbo.tblCourse.Hole14, dbo.tblCourse.Hole15, dbo.tblCourse.Hole16,
dbo.tblCourse.Hole17, dbo.tblCourse.Hole18, dbo.tblCourse.T,
dbo.tblCourse.Rating, dbo.tblCourse.Slope, dbo.tblPlayers.Player_ID,
dbo.tblPlayers.PlayerLastName, dbo.tblPlayers.PlayerFirstName,
dbo.tblPlayers.PlayerNickName, dbo.tblScores.H4, dbo.tblScores.H5,
dbo.tblScores.H6, dbo.tblScores.H7, dbo.tblScores.H8, dbo.tblScores.H9,
dbo.tblScores.H10, dbo.tblScores.H11, dbo.tblScores.H12,
dbo.tblScores.H13, dbo.tblScores.H14, dbo.tblScores.H15,
dbo.tblScores.H16, dbo.tblScores.H17, dbo.tblScores.H18,
dbo.tblScores.B9, dbo.tblScores.T18, dbo.tblScores.P1, dbo.tblScores.F9,
dbo.tblScores.P2, dbo.tblScores.P3, dbo.tblScores.P4, dbo.tblScores.P5,
dbo.tblScores.P6,
dbo.tblScores.P7, dbo.tblScores.P8, dbo.tblScores.P9, dbo.tblScores.P10,
dbo.tblScores.P11, dbo.tblScores.P12, dbo.tblScores.P13,
dbo.tblScores.P15, dbo.tblScores.P14,
dbo.tblScores.P16, dbo.tblScores.P17, dbo.tblScores.P18,
dbo.tblScores.FW1, dbo.tblScores.FW2, dbo.tblScores.FW3,
dbo.tblScores.FW4, dbo.tblScores.FW5, dbo.tblScores.FW6,
dbo.tblScores.FW7, dbo.tblScores.FW8, dbo.tblScores.FW9,
dbo.tblScores.FW10, dbo.tblScores.FW11, dbo.tblScores.FW12,
dbo.tblScores.FW13, dbo.tblScores.FW14, dbo.tblScores.FW15,
dbo.tblScores.FW16, dbo.tblScores.FW17,
dbo.tblScores.FW18, dbo.tblScores.FWT, dbo.tblScores.TotalPutts,
dbo.tblScores.ActualDiff, dbo.tblScores.TotPars, dbo.tblScores.TotBirds,
dbo.tblScores.TotEagles, dbo.tblScores.TotBogys,
dbo.tblScores.Tot2Bogys, dbo.tblScores.AvgPutts
FROM dbo.tblScores
LEFT OUTER JOIN
dbo.tblCourse ON dbo.tblScores.Course = dbo.tblCourse.CourseID
LEFT OUTER JOIN
dbo.tblPlayers ON dbo.tblScores.Player =
dbo.tblPlayers.Player_ID
WHERE tblPlayers.Player_ID=@.PlayerID
order by date, HandiDiff;
GO

Works really well now.

Jim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Looks like you just need date in DESCending order (the default is
ascending).

SELECT TOP 10 WITH TIES
...
ORDER BY date DESC, handidiff

Note that you may or may not want the WITH TIES option. Without it, TOP may
give non-deterministic results - if there are values where the sort columns
are equal (tied) then only some subset of the rows may be shown if the
result would otherwise give more than 10 rows.

--
David Portas
SQL Server MVP
--

> I posted another message because with your solution it gave me 10 rows by
> date which isn't exactly what I was looking for.

> I have to selection for a row of golf scores, the most current 20 rows by
> date for a particular player.

> Then I have to select the lowest 10 scores from that subset.

> Then I use those 10 to calculate the USGA Handicap Index.

> What I'm getting is the newest 10 by date which may not be the best scores|||Still have a problem with SQL. I have tried the following:

Select top 10 with ties, date, score
From tblScores
Where playerID = 3
Order by date desc, score;

The problem is that it selects the top 10 scores by date when in fact I
want the most current top 20 scores and from the most current top 20 I
want to pick the top 10 scores by lowest score.

So if I had 100 rows I would get the most current 20. From that group I
would select by score the lowest 10.

I'm not sure how TIES works but it's not doing what I wanted.

Any help would be appreciated.

Jim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Try this:

SELECT TOP 10 WITH TIES *
FROM
(SELECT TOP 20 WITH TIES
S.[date], S.player, S.handidiff, S.tees, S.h1, S.h2, S.h3, S.course,
C.courseid, C.coursename, C.hole1, C.hole2, C.hole3, C.hole4, C.hole5,
C.hole6, C.hole7, C.hole8, C.hole9, C.hole10, C.hole11, C.hole12,
C.hole13, C.hole14, C.hole15, C.hole16, C.hole17, C.hole18, C.t,
C.rating, C.slope, P.player_id, P.playerlastname, P.playerfirstname,
P.playernickname, S.h4, S.h5, S.h6, S.h7, S.h8, S.h9, S.h10, S.h11,
S.h12, S.h13, S.h14, S.h15, S.h16, S.h17, S.h18, S.b9, S.t18,
S.p1, S.f9, S.p2, S.p3, S.p4, S.p5, S.p6, S.p7, S.p8, S.p9, S.p10,
S.p11, S.p12, S.p13, S.p15, S.p14, S.p16, S.p17, S.p18, S.fw1, S.fw2,
S.fw3, S.fw4, S.fw5, S.fw6, S.fw7, S.fw8, S.fw9, S.fw10, S.fw11,
S.fw12, S.fw13, S.fw14, S.fw15, S.fw16, S.fw17, S.fw18, S.fwt,
S.totalputts, S.actualdiff, S.totpars, S.totbirds, S.toteagles,
S.totbogys, S.tot2bogys, S.avgputts
FROM dbo.tblScores AS S
LEFT OUTER JOIN
dbo.tblCourse AS C
ON S.course = C.courseid
LEFT OUTER JOIN
dbo.tblPlayers AS P
ON S.player = P.player_id
WHERE P.player_id = @.PlayerID
ORDER BY S.[date] DESC, S.handidiff) AS T
ORDER BY handidiff

--
David Portas
SQL Server MVP
--sql

HAVING prob !

Bonjour,

CREATE TABLE [dbo].[MAND](
[Mat] [varchar](5)
[Dur] [varchar](1)
) ON [PRIMARY]

Mat Dur
16030d
16030i
31217i
10000d
12000i
10000d
31217d
35000d
36000i
35000d

Je voudrais avoir le resulat suivant (i need this result) :

10000 d
35000 d

Car ils ont tous les deux "d". J'ai beau faire un regroupement (group
by) par Mat avec un having ne marche pas.

Comment faire ?
Merci d'avanceIs this what you want?

SELECT Mat,
Dur
FROM MAND
GROUP BY Mat,Dur
HAVING COUNT(*)=2|||Try:

select
Mat
, Dur
from
MAND
group by
Mat
, Dur
having
count (*) > 1

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<daoud97@.yahoo.com> wrote in message
news:1149245350.517939.8790@.c74g2000cwc.googlegrou ps.com...
Bonjour,

CREATE TABLE [dbo].[MAND](
[Mat] [varchar](5)
[Dur] [varchar](1)
) ON [PRIMARY]

Mat Dur
16030 d
16030 i
31217 i
10000 d
12000 i
10000 d
31217 d
35000 d
36000 i
35000 d

Je voudrais avoir le resulat suivant (i need this result) :

10000 d
35000 d

Car ils ont tous les deux "d". J'ai beau faire un regroupement (group
by) par Mat avec un having ne marche pas.

Comment faire ?
Merci d'avance|||markc...@.hotmail.com schreef:

> Is this what you want?
>
> SELECT Mat,
> Dur
> FROM MAND
> GROUP BY Mat,Dur
> HAVING COUNT(*)=2

No.

When i group by 'Mat' i need to filter by 'Mat' having only 'd' in
'Dur'. I d'ont want Mat with 'i' in Dur.|||Maybe this

SELECT Mat,
Dur
FROM MAND
WHERE Dur='d'
GROUP BY Mat,Dur
HAVING COUNT(*)=2|||other example to help you :

Mat Dur
16030 d
16030 i
31217 i
10000 d
12000 i
10000 d
31217 d
35000 d
36000 i
35000 d
35000 i

i need this result :

10000 d

Now, 35000 have one 'i' in 'Dur', so i don't need it.

daoud97@.yahoo.com schreef:

> markc...@.hotmail.com schreef:
> > Is this what you want?
> > SELECT Mat,
> > Dur
> > FROM MAND
> > GROUP BY Mat,Dur
> > HAVING COUNT(*)=2
> No.
> When i group by 'Mat' i need to filter by 'Mat' having only 'd' in
> 'Dur'. I d'ont want Mat with 'i' in Dur.|||SELECT Mat,
'd' AS Dur
FROM MAND
GROUP BY Mat
HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1|||markc600@.hotmail.com schreef:

> SELECT Mat,
> 'd' AS Dur
> FROM MAND
> GROUP BY Mat
> HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1

Sorry, it's not correct :

if i insert

36000 i
36000 i

and apply your SQL, i have some result like :

10000d
16030d
31217d
36000d (??)

perhaps, i d'ont expline vers well !|||daoud97@.yahoo.com schreef:

> markc600@.hotmail.com schreef:
> > SELECT Mat,
> > 'd' AS Dur
> > FROM MAND
> > GROUP BY Mat
> > HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1
> Sorry, it's not correct :
> if i insert
> 36000 i
> 36000 i
> and apply your SQL, i have some result like :
> 10000d
> 16030d
> 31217d
> 36000d (??)
> perhaps, i d'ont expline vers well !

I will try to expline more :

this is the table with groups:

Mat Dur
16030 d
16030 i

31217 i
31217 d
31217 d
31217 d

10000 d
10000 d
10000 d

35000 d

I'am interested only by the 2 groups : 1000 and 35000 because the
haven't any 'i' in the colonn Dur.|||daoud97@.yahoo.com schreef:

> daoud97@.yahoo.com schreef:
> > markc600@.hotmail.com schreef:
> > > SELECT Mat,
> > > 'd' AS Dur
> > > FROM MAND
> > > GROUP BY Mat
> > > HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1
> > Sorry, it's not correct :
> > if i insert
> > 36000 i
> > 36000 i
> > and apply your SQL, i have some result like :
> > 10000d
> > 16030d
> > 31217d
> > 36000d (??)
> > perhaps, i d'ont expline vers well !
> I will try to expline more :
>
> this is the table with groups:
> Mat Dur
> 16030 d
> 16030 i
> 31217 i
> 31217 d
> 31217 d
> 31217 d
> 10000 d
> 10000 d
> 10000 d
> 35000 d
>
> I'am interested only by the 2 groups : 10000 and 35000 because the
> haven't any 'i' in the colonn Dur.|||
SELECT Mat,
'd' AS Dur
FROM MAND
GROUP BY Mat
HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1
AND MAX(Dur)='d'|||markc600@.hotmail.com schreef:

> SELECT Mat,
> 'd' AS Dur
> FROM MAND
> GROUP BY Mat
> HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1
> AND MAX(Dur)='d'

sorry, it's not correct

Example:
16030d
16030d
31217d
10000d
12000i
10000d
31217d
35000d
36000i
35000d
35000d
36000i
45d
45d
45i
250d
250i
10i
10i

result with your query :
10000d
16030d
31217d

reult that i need:
10000 d
16030 d
31217 d
35000 d

why COUNT(*)=2 ?|||The problem is I'm trying to guess your business rules

SELECT Mat,
MAX(Dur) AS Dur
FROM MAND
GROUP BY Mat
HAVING COUNT(*)>1 AND COUNT(DISTINCT Dur)=1
AND MAX(Dur)='d'|||Try:

SELECT Mat,
Dur
FROM MAND
GROUP BY Mat, Dur
HAVING SUM (CASE WHEN Dur = 'i' THEN 1 ELSE 0 END) = 0

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<daoud97@.yahoo.com> wrote in message
news:1149251877.853856.250370@.y43g2000cwc.googlegr oups.com...

daoud97@.yahoo.com schreef:

> markc600@.hotmail.com schreef:
> > SELECT Mat,
> > 'd' AS Dur
> > FROM MAND
> > GROUP BY Mat
> > HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1
> Sorry, it's not correct :
> if i insert
> 36000 i
> 36000 i
> and apply your SQL, i have some result like :
> 10000 d
> 16030 d
> 31217 d
> 36000 d (??)
> perhaps, i d'ont expline vers well !

I will try to expline more :

this is the table with groups:

Mat Dur
16030 d
16030 i

31217 i
31217 d
31217 d
31217 d

10000 d
10000 d
10000 d

35000 d

I'am interested only by the 2 groups : 1000 and 35000 because the
haven't any 'i' in the colonn Dur.|||markc600@.hotmail.com schreef:

> The problem is I'm trying to guess your business rules
>
> SELECT Mat,
> MAX(Dur) AS Dur
> FROM MAND
> GROUP BY Mat
> HAVING COUNT(*)>1 AND COUNT(DISTINCT Dur)=1
> AND MAX(Dur)='d'

mmm very interresting. Thanks

Having Indexed tbl will speed up DTS job ?

Hi ,
I have a DTS job that populates the data from server A to server B.
howver, i did not create the tbl with indexes.
from what i know , creating an indexed tbl will enable a faster retrieval
of data but in the case where my DTS job needs to populates the data across,
will it actually speed my my DTS job performance taking into acct that now
SQL will need to create the indexes for each indexed field ?
appreciate any advise
thksHi
From:
http://msdn.microsoft.com/library/d...asp?frame=true
"The performance benefits of indexes, however, do come with a cost. Tables
with indexes require more storage space in the database. Also, commands that
insert, update, or delete data can take longer and require more processing
time to maintain the indexes. When you design and create indexes, you should
ensure that the performance benefits outweigh the extra cost in storage spac
e
and processing resources."
If you are indexing the source table and are selectivly retrieving the
records, then there may be benefits, if you have indexed the destination
table it may slow the insertion of the records down, but subsequent retrieva
l
of information from that table can be improved.
A technique that is sometimes used, it to drop the indexes on your
destination table before inserting your records and then re-applying them
afterwards. This can sometime be quicker than doing the inserts with the
index present, and it will also mean that the indexes are less fragmented
(because they have been re-built).
John
"maxzsim" wrote:

> Hi ,
> I have a DTS job that populates the data from server A to server B.
> howver, i did not create the tbl with indexes.
> from what i know , creating an indexed tbl will enable a faster retrieva
l
> of data but in the case where my DTS job needs to populates the data acros
s,
> will it actually speed my my DTS job performance taking into acct that now
> SQL will need to create the indexes for each indexed field ?
> appreciate any advise
> thkssql

Having Indexed tbl will speed up DTS job ?

Hi ,
I have a DTS job that populates the data from server A to server B.
howver, i did not create the tbl with indexes.
from what i know , creating an indexed tbl will enable a faster retrieval
of data but in the case where my DTS job needs to populates the data across,
will it actually speed my my DTS job performance taking into acct that now
SQL will need to create the indexes for each indexed field ?
appreciate any advise
thks
Hi
From:
http://msdn.microsoft.com/library/de...asp?frame=true
"The performance benefits of indexes, however, do come with a cost. Tables
with indexes require more storage space in the database. Also, commands that
insert, update, or delete data can take longer and require more processing
time to maintain the indexes. When you design and create indexes, you should
ensure that the performance benefits outweigh the extra cost in storage space
and processing resources."
If you are indexing the source table and are selectivly retrieving the
records, then there may be benefits, if you have indexed the destination
table it may slow the insertion of the records down, but subsequent retrieval
of information from that table can be improved.
A technique that is sometimes used, it to drop the indexes on your
destination table before inserting your records and then re-applying them
afterwards. This can sometime be quicker than doing the inserts with the
index present, and it will also mean that the indexes are less fragmented
(because they have been re-built).
John
"maxzsim" wrote:

> Hi ,
> I have a DTS job that populates the data from server A to server B.
> howver, i did not create the tbl with indexes.
> from what i know , creating an indexed tbl will enable a faster retrieval
> of data but in the case where my DTS job needs to populates the data across,
> will it actually speed my my DTS job performance taking into acct that now
> SQL will need to create the indexes for each indexed field ?
> appreciate any advise
> thks

Having Indexed tbl will speed up DTS job ?

Hi ,
I have a DTS job that populates the data from server A to server B.
howver, i did not create the tbl with indexes.
from what i know , creating an indexed tbl will enable a faster retrieval
of data but in the case where my DTS job needs to populates the data across,
will it actually speed my my DTS job performance taking into acct that now
SQL will need to create the indexes for each indexed field ?
appreciate any advise
thksHi
From:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_30s5.asp?frame=true
"The performance benefits of indexes, however, do come with a cost. Tables
with indexes require more storage space in the database. Also, commands that
insert, update, or delete data can take longer and require more processing
time to maintain the indexes. When you design and create indexes, you should
ensure that the performance benefits outweigh the extra cost in storage space
and processing resources."
If you are indexing the source table and are selectivly retrieving the
records, then there may be benefits, if you have indexed the destination
table it may slow the insertion of the records down, but subsequent retrieval
of information from that table can be improved.
A technique that is sometimes used, it to drop the indexes on your
destination table before inserting your records and then re-applying them
afterwards. This can sometime be quicker than doing the inserts with the
index present, and it will also mean that the indexes are less fragmented
(because they have been re-built).
John
"maxzsim" wrote:
> Hi ,
> I have a DTS job that populates the data from server A to server B.
> howver, i did not create the tbl with indexes.
> from what i know , creating an indexed tbl will enable a faster retrieval
> of data but in the case where my DTS job needs to populates the data across,
> will it actually speed my my DTS job performance taking into acct that now
> SQL will need to create the indexes for each indexed field ?
> appreciate any advise
> thks

Having difficulty setting Back Up to back up file wihout datetime stamp SQL 2K

Hello,

I'm trying to create a simple back up in the SQL Maintenance Plan that will
make a single back up copy of all database every night at 10 pm. I'd like
the previous nights file to be overwritten, so there will be only a single
back up file for each database (tape back up runs every night, so each days
back up will be saved on tape).

Every night the maintenance plan makes a back up of all the databases to a
new file with a datetime stamp, meaning the previous nights file still
exists. Even when I check "Remove files older than 22 hours" the previous
nights file still exists. Is there any way to create a back up file without
the date time stamp so it overwrites the previous nights file?

Thanks!
RickOn 24.04.2007 15:29, Rico wrote:

Quote:

Originally Posted by

I'm trying to create a simple back up in the SQL Maintenance Plan that will
make a single back up copy of all database every night at 10 pm. I'd like
the previous nights file to be overwritten, so there will be only a single
back up file for each database (tape back up runs every night, so each days
back up will be saved on tape).
>
Every night the maintenance plan makes a back up of all the databases to a
new file with a datetime stamp, meaning the previous nights file still
exists. Even when I check "Remove files older than 22 hours" the previous
nights file still exists. Is there any way to create a back up file without
the date time stamp so it overwrites the previous nights file?

Yes, I believe option is "WITH INIT". Please see BOL for details.

Regards

robert|||Hi Robert,

Thanks, but I should have mentioned that I'm using SQL 2000 and I'm not
using T-SQL, just trying to create the Maintenance plan from the Enterprise
Manager.

Rick

"Robert Klemme" <shortcutter@.googlemail.comwrote in message
news:596ghdF2jtij6U1@.mid.individual.net...

Quote:

Originally Posted by

On 24.04.2007 15:29, Rico wrote:

Quote:

Originally Posted by

>I'm trying to create a simple back up in the SQL Maintenance Plan that
>will
>make a single back up copy of all database every night at 10 pm. I'd
>like
>the previous nights file to be overwritten, so there will be only a
>single
>back up file for each database (tape back up runs every night, so each
>days
>back up will be saved on tape).
>>
>Every night the maintenance plan makes a back up of all the databases to
>a
>new file with a datetime stamp, meaning the previous nights file still
>exists. Even when I check "Remove files older than 22 hours" the
>previous
>nights file still exists. Is there any way to create a back up file
>without
>the date time stamp so it overwrites the previous nights file?

>
Yes, I believe option is "WITH INIT". Please see BOL for details.
>
Regards
>
robert

|||Rico (me@.you.com) writes:

Quote:

Originally Posted by

Thanks, but I should have mentioned that I'm using SQL 2000 and I'm not
using T-SQL, just trying to create the Maintenance plan from the
Enterprise Manager.

From what you described, you should not use a maintenance plan. Just set up
a scheduled job to run the BACKUP statement. You can do this from the
GUI where you backup databases, and select Schedule somewhere on a button.
In the end you get a one-step job that has a BACKUP job.

Then again, if you have any interest in acquiring basic DBA skills, you
should certainly learn to write basic BACKUP commands in T-SQL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland,

I don't have a problem creating the T-SQL commands, it's using the
Enterprise Manager to do more than create dbs is where I get lost.

I will give that a try (creating a back up job and scheduling)

THanks!
Rick

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns991D5A7E496DYazorman@.127.0.0.1...

Quote:

Originally Posted by

Rico (me@.you.com) writes:

Quote:

Originally Posted by

>Thanks, but I should have mentioned that I'm using SQL 2000 and I'm not
>using T-SQL, just trying to create the Maintenance plan from the
>Enterprise Manager.

>
From what you described, you should not use a maintenance plan. Just set
up
a scheduled job to run the BACKUP statement. You can do this from the
GUI where you backup databases, and select Schedule somewhere on a button.
In the end you get a one-step job that has a BACKUP job.
>
Then again, if you have any interest in acquiring basic DBA skills, you
should certainly learn to write basic BACKUP commands in T-SQL.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||
"Rico" <me@.you.comwrote in message news:8LvXh.9$_G.8@.edtnps89...

Quote:

Originally Posted by

Thanks Erland,
>
I don't have a problem creating the T-SQL commands, it's using the
Enterprise Manager to do more than create dbs is where I get lost.

That's the problem with using EM.

BTW... the reason you're seeing the behavior you're seeing is that the
maintenance job takes the conservative approach and assumes that the most
recent backup HAS to succeed before it'll delete the older one.

So setting it to 22 hours or anything won't force it to delete the older
file until the new one is successfully created.

Quote:

Originally Posted by

>
I will give that a try (creating a back up job and scheduling)
>
THanks!
Rick
>
>
"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns991D5A7E496DYazorman@.127.0.0.1...

Quote:

Originally Posted by

>Rico (me@.you.com) writes:

Quote:

Originally Posted by

>>Thanks, but I should have mentioned that I'm using SQL 2000 and I'm not
>>using T-SQL, just trying to create the Maintenance plan from the
>>Enterprise Manager.

>>
>From what you described, you should not use a maintenance plan. Just set
>up
>a scheduled job to run the BACKUP statement. You can do this from the
>GUI where you backup databases, and select Schedule somewhere on a
>button.
>In the end you get a one-step job that has a BACKUP job.
>>
>Then again, if you have any interest in acquiring basic DBA skills, you
>should certainly learn to write basic BACKUP commands in T-SQL.
>>
>--
>Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>Books Online for SQL Server 2005 at
>http://www.microsoft.com/technet/pr...oads/books.mspx
>Books Online for SQL Server 2000 at
>http://www.microsoft.com/sql/prodin...ions/books.mspx

>
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Having difficulty creating a stored procedure

I am trying to create stored procedure i Query analyzer in visual studio 2005. I am having
difficulty though. Whenever I press the execute button, here is the error message I get:

Msg 102, Level 15, State 1, Procedure MarketCreate, Line 21
Incorrect syntax near 'MarketName'.

Here is the stored procedure. Note that the very first column in named "MarketId" but I did not
include it in the stored procedure since it should be auto generated.

USE [StockWatch]
GO
/****** Object: StoredProcedure [dbo].[MarketCreate] Script Date: 08/28/2007 15:49:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MarketCreate]

(
@.MarketCode nvarchar(20),
@.MarketName nvarchar(100),
@.LastUpdateDate nvarchar(2),
@.MarketDescription nvarchar(100)
)

AS
INSERT INTO Market
(
MarketCode
MarketName
LastUpdateDate
MarketDescription
)
VALUES
(
@.MarketCode
@.MarketName
@.LastUpdateUser
@.MarketDescription
)

You need to use comma's to separate the column names.

USE[StockWatch]

GO

/****** Object: StoredProcedure [dbo].[MarketCreate] Script Date: 08/28/2007 15:49:26 ******/

SET ANSI_NULLSON

GO

SET QUOTED_IDENTIFIERON

GO

CREATEPROCEDURE [dbo].[MarketCreate]

(

@.MarketCodenvarchar(20),

@.MarketNamenvarchar(100),

@.LastUpdateDatenvarchar(2),

@.MarketDescriptionnvarchar(100)

)

AS

INSERTINTO Market

(

MarketCode,

MarketName,

LastUpdateDate,

MarketDescription

)

VALUES

(

@.MarketCode,

@.MarketName,

@.LastUpdateUser,

@.MarketDescription

)

|||

Thanks !

Have Stored Procedure with input parameters and want to Use Spreadsheet

I have a stored procedure that is able to ultimately do an create a temp table and insert into another table. I'm trying to figure out how I could execute the stored procedure that would get it's input parameters from a spreadsheet rather than execute it line by line. Can anyone suggest anything?

CREATE PROCEDURE osp_xmlSvc_UpdateItemAttribute

@.PartNumber varchar(50) = '',

@.Rev varchar(50) = '',

@.AttributeName varchar(256) = '',

@.AttributeValue varchar(1024) = '',

@.EditSource varchar(255) = '',

@.UserName varchar(50) = 'admin',

@.ReturnState int = 0 Output

WITH ENCRYPTION

AS

DECLARE @.ItemID int

DECLARE @.RevID int

DECLARE @.AssignAllRevs int

DECLARE @.ParamIndexID int

DECLARE @.CurrentValue varchar(1024)

DECLARE @.UserID int

SET @.ReturnState = -1

SET @.ItemID = -1

SET @.RevID = -1

SET @.ParamIndexID = -1

SET @.CurrentValue = ''

SET @.AssignAllRevs = 0

SELECT @.UserID = ID FROM UserProfile WHERE UserName = @.UserName

SELECT @.ItemID = ID FROM Entry WHERE PartNumber = @.PartNumber

if(NOT(@.ItemID = -1) AND @.ItemID IS NOT NULL) begin

SELECT @.RevID = ID FROM Rev WHERE ItemID = @.ItemID AND Rev=@.Rev

if(@.RevID = -1 OR @.RevID IS NULL) begin

SET @.AssignAllRevs = 1

SELECT @.RevID = ID FROM Rev WHERE ItemID = @.ItemID AND Expired=0

end

SELECT @.ParamIndexID = ID FROM ParamIndex WHERE [Name]=@.AttributeName

if(NOT(@.ParamIndexID = -1) AND @.ParamIndexID IS NOT NULL) begin

SET @.ReturnState = 0

CREATE TABLE tmp_xml_AV (AttVal varchar(1024))

DECLARE @.tmpSQL nvarchar(2024)

SET @.tmpSQL = 'INSERT INTO tmp_xml_AV (AttVal) SELECT [' + @.AttributeName + '] FROM ParamValue WHERE Expired=0 AND ItemID=' + CAST(@.ItemID AS VARCHAR)+ ' AND RevID=' + CAST(@.RevID AS VARCHAR)

EXECUTE sp_executesql @.tmpSQL

SELECT @.CurrentValue = IsNull(AttVal, '') FROM tmp_xml_AV

DROP TABLE tmp_xml_AV

if(NOT(@.CurrentValue = @.AttributeValue) OR @.CurrentValue IS NULL) begin

SET @.ReturnState = 1

SET @.tmpSQL = 'UPDATE ParamValue SET [' + @.AttributeName + ']=''' + @.AttributeValue + ''' WHERE ItemID=' + CAST(@.ItemID AS VARCHAR)

if(@.AssignAllRevs = 0) begin

SET @.tmpSQL = @.tmpSQL + ' AND RevID=' + CAST(@.RevID AS VARCHAR)

end

EXECUTE sp_executesql @.tmpSQL

-- Record history

DECLARE @.tmpInt int

SELECT @.tmpInt = Max(ID)+1 FROM EntryChangeAction

INSERT INTO EntryChangeAction (ID,EntryAffected,RevID,ActionType,Details,Tool,UserID)

VALUES (@.tmpInt,@.ItemID,@.RevID,6,@.AttributeName + ': ' + @.CurrentValue + ' to: ' + @.AttributeValue,@.EditSource,@.UserID)

end

end

end

GRANT EXECUTE ON [dbo].[osp_xmlSvc_UpdateItemAttribute] TO [public]

GO

Thanks.

Amy

In the stored procedure, you could open and read the xls file and put the values into variables. (An xml file would be simpler -Excel could save the file as xml.)

Check in Books Online about [OpenXML].

Have lost the facility to select a Report Model DataSource Type?

I am trying to create a report from a Report Model, but when I try to select
the Datasource Type I find the Report Model DataSource Type is no longer
available to select. It definitely used to be there.
I have recently been experimenting with the Custom Report Items and modified
the configs etc as per instructions. The Polygons example appeared to work
eventually but at around the same time as experimenting with that, the Report
Model DataSource Type dissapeared. I have restored the config files back to
what they were, but still lost my DataSource Types. I suspect the only real
answer is to reinstall, however, I need to understand how to fix, and how I
have broken it. Where are the datasource types drop downs picked from? and
how best to fix this without reinstall. Any experts out there please help!Unsure how on earth lost the Report Model DataSource Type and the only way I
could find of restoring it was to uninstall all of SQL and reinstall it
again. Lost my Report Server Database, since cannot be restored.
Custom Report Items Health warning - tread carefully... suspect the
RSReportDesigner.config was the one that was clobbered in the process -
losing the RS Data extension. Unclear if it could be recreated somehow
without all this pain. Maybe a repair facility on Business Intelligence
Studio WOULD HAVE HELPED!
"Steve Giergiel" wrote:
> I am trying to create a report from a Report Model, but when I try to select
> the Datasource Type I find the Report Model DataSource Type is no longer
> available to select. It definitely used to be there.
> I have recently been experimenting with the Custom Report Items and modified
> the configs etc as per instructions. The Polygons example appeared to work
> eventually but at around the same time as experimenting with that, the Report
> Model DataSource Type dissapeared. I have restored the config files back to
> what they were, but still lost my DataSource Types. I suspect the only real
> answer is to reinstall, however, I need to understand how to fix, and how I
> have broken it. Where are the datasource types drop downs picked from? and
> how best to fix this without reinstall. Any experts out there please help!

Have an Idea but not the answer.... :( needed help

Hi,

I have a table that has the ff:

LastName varchar(50)

FirstName varchar(50)

PhotoPath varchar(50)

Now I want to create a form that can accept the LastName,Firstname and also can upload a picture which in turn the filename of the image will be the value for the PhotoPath field, and eventually displays it using the repeater control.

Your Help/Info. is highly appreciated...

Jeff

This answers your question

http://forums.asp.net/t/1113089.aspx

|||

Check this tutorialhttp://www.asp.net/Learn/DataAccess/#binary

It explains you both options to save the photos (as a file as well as an image object in the database)

Another link:http://aspalliance.com/articleViewer.aspx?aId=138&pId

Thanks

|||

Hi Jeff,

Saving first and last names are simple enough, you can search for any good ADO.NET tutorial for this.

For uploading an image file, check this article:

http://www.4guysfromrolla.com/webtech/091201-1.shtml

After uploading, save the filepath in the DB table and then show it as the image url in the repeater control.

Hope this helps,

Vivek

sql

Harish Mohanbabu

Hi,
How to create jobs in MS SQL Server 2000 for the following purposes -
1. For re-organise/re-index to 10% per page &
2. For checking DB integrity.
Can someone let me know please.
TIA,
Harish
*********************************
Long way to go before I sleep ..
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hello Harish,
If you want the quick and dirty way use the maintenance wizard for this. If
you want scripts for this I can email some that I use.
Let me know if need to know how to run the maintenance wizard. Or just look
it up in the books online for SQL server, this is an excellent resource with
walk throughs and examples.
John...
"Harish Mohanbabu" <Harish@.nospam.com> wrote in message
news:eZ6VCrU$DHA.1732@.TK2MSFTNGP12.phx.gbl...
> Hi,
> How to create jobs in MS SQL Server 2000 for the following purposes -
> 1. For re-organise/re-index to 10% per page &
> 2. For checking DB integrity.
> Can someone let me know please.
> TIA,
> Harish
>
> *********************************
> Long way to go before I sleep ..
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

Harish Mohanbabu

Hi,
How to create jobs in MS SQL Server 2000 for the following purposes -
1. For re-organise/re-index to 10% per page &
2. For checking DB integrity.
Can someone let me know please.
TIA,
Harish
*********************************
Long way to go before I sleep ..
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Go to Enterprise Manager/management/database mainenance
plans. You can right click and select "new maintenance
plan", you just select the databases you want to setup and
it will walk you through the rest. You can also setup
full/tran log backups. You will then see the additions of
new jobs.
Hope that helps
thanks
Susan
>--Original Message--
>Hi,
>How to create jobs in MS SQL Server 2000 for the
following purposes -
>1. For re-organise/re-index to 10% per page &
>2. For checking DB integrity.
>Can someone let me know please.
>TIA,
>Harish
>
>*********************************
>Long way to go before I sleep ..
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>|||Hello Harish,
If you want the quick and dirty way use the maintenance wizard for this. If
you want scripts for this I can email some that I use.
Let me know if need to know how to run the maintenance wizard. Or just look
it up in the books online for SQL server, this is an excellent resource with
walk throughs and examples.
John...
"Harish Mohanbabu" <Harish@.nospam.com> wrote in message
news:eZ6VCrU$DHA.1732@.TK2MSFTNGP12.phx.gbl...
> Hi,
> How to create jobs in MS SQL Server 2000 for the following purposes -
> 1. For re-organise/re-index to 10% per page &
> 2. For checking DB integrity.
> Can someone let me know please.
> TIA,
> Harish
>
> *********************************
> Long way to go before I sleep ..
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Handling very large XML result sets

I am writing a .NET based application to create large XML data files using
SQLXML classes and FOR XML EXPLICIT queries. What are some strategies I can
use to break up and process these large result sets? The overhead of issuing
multiple queries by breaking them up via WHERE clause filters isn’t the way I
want to go since my queries are very large and take significant time to
process within SQL server.
I am currently experiencing out of memory exceptions on some larger result
sets (~50-60 Mbytes total XML file size). My first attempt was using
SqlXmlCommand.ExecuteXmlReader and an XmlDocument via this snippet of code:
XmlReader xr = forXMLCommand.ExecuteXmlReader();
XmlDocument xd = new XmlDocument();
xd.Load(xr);
This throws a System.OutOfMemoryException on the call to ExecuteXmlReader
when the result set gets very large.
I also tried using SqlXmlCommand.ExecuteStream thinking I could read a
buffer of chars at a time to process these large result sets but this also
resulted in a System.OutOfMemoryException on the call to ExecuteStream:
Stream s = forXMLCommand.ExecuteStream();
StreamWriter sw3 = new StreamWriter(mResultsFileName);
using (StreamReader sr = new StreamReader(s))
{
char[] c = null;
while (sr.Peek() >= 0)
{
c = new char[10000];
intnumRead = sr.Read(c, 0, c.Length);
sw3.Write(c, 0, numRead);
}
}
I have tried running my application on two different systems one with 1G of
main memory and the other a Win2K3 server with 8G of main memory. Both
systems seem to run out of memory at the same 50-60 Mb limit) Are there any
..NET memory settings I can tweak to give my .NET application more memory?
Thanks for your suggestions and ideas,
Scott
The XmlReader is a streaming interface which should not run out of memory
via the SqlXmlCommand.ExecuteStream method.
Loading into an XmlDocument however will cache the entire document into
memory.
Can you remove the following two lines from your repro and see if you are
still having the problem:
XmlDocument xd = new XmlDocument();
xd.Load(xr);
Thanks -
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad
|||Andrew,
That was exactly my thought as well, but ExecuteStream is throwing an
OutOfMemoryException. I am NOTcalling XmlDocument .Load in the code that
uses ExecuteStream.
Here is my full method I am using:
private void ExecuteSQLXMLCommandExecuteStream()
{
try
{
SqlXmlCommandforXMLCommand = new SqlXmlCommand("Provider=SQLOLEDB;DATA
SOURCE=Gibraltar;Initial Catalog=RDCModel;User ID=sa;Password=XXXX");
forXMLCommand.CommandType = SqlXmlCommandType.Sql;
StreamReadersr1 = new StreamReader(mQueryFileName);
stringquery = sr1.ReadToEnd();
sr1.Close();
query = query.Replace("\r\n", " ");
query = query.Replace("\t", " ");
forXMLCommand.CommandText = query;
Stream s = forXMLCommand.ExecuteStream();
StreamWriter sw3 = new StreamWriter(mResultsFileName);
using (StreamReader sr = new StreamReader(s))
{
char[] c = null;
while (sr.Peek() >= 0)
{
c = new char[10000];
intnumRead = sr.Read(c, 0, c.Length);
sw3.Write(c, 0, numRead);
}
}
sw3.Close();
}
catch (SqlXmlException ex)
{
ex.ErrorStream.Position = 0;
string sqlErrorString;
sqlErrorString = new StreamReader(ex.ErrorStream).ReadToEnd();
Console.WriteLine(sqlErrorString);
RDCUtilities.WriteToLog(sqlErrorString);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine(ex.StackTrace);
RDCUtilities.WriteToLog(ex.Message);
}
""Andrew Conrad"" wrote:

> The XmlReader is a streaming interface which should not run out of memory
> via the SqlXmlCommand.ExecuteStream method.
> Loading into an XmlDocument however will cache the entire document into
> memory.
> Can you remove the following two lines from your repro and see if you are
> still having the problem:
> XmlDocument xd = new XmlDocument();
> xd.Load(xr);
> Thanks -
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad
>
|||Try using SqlXmlCommand.ExecuteToStream() instead of ExecuteStream.
Because of some technical limitations with COM interop, ExecuteStream
caches results.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad