A couple of easy questions

Dear all,
1)
I was wondering myself, how many characters can handle Sql2k when you define
a field? Such limitation is common for any object? I mean, tables, indexes,
jobs.
I've got an ASP application which allow the user create fields and I would
need to know that rule in order to define a maxlenght value.
2)
I had in my EM remote servers added but today they are dissapeared although
now are currently up. Does anyone ever experienced such behaviour? It coul
d
be a shutdown of that server or even if that server is off from the client
vanishes...
Thanks a lot for your suggestions,
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)Hi
1)
Lookup in the BOL for maximum and minimum ovjects limits article
2)
I have not, but did you refresh your EM?
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:514627E4-4715-41C6-B92B-B8913DC31D94@.microsoft.com...
> Dear all,
> 1)
> I was wondering myself, how many characters can handle Sql2k when you
> define
> a field? Such limitation is common for any object? I mean, tables,
> indexes,
> jobs.
> I've got an ASP application which allow the user create fields and I would
> need to know that rule in order to define a maxlenght value.
> 2)
> I had in my EM remote servers added but today they are dissapeared
> although
> now are currently up. Does anyone ever experienced such behaviour? It
> could
> be a shutdown of that server or even if that server is off from the client
> vanishes...
>
> Thanks a lot for your suggestions,
>
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)|||hi again Uri,
Yes, I did. How odd, isn't?
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Uri Dimant" wrote:

> Hi
> 1)
> Lookup in the BOL for maximum and minimum ovjects limits article
> 2)
> I have not, but did you refresh your EM?
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:514627E4-4715-41C6-B92B-B8913DC31D94@.microsoft.com...
>
>|||
> Yes, I did. How odd, isn't?
Hi Enric, do you have 'Open object explorer and new query' in Tools/Options
dialog under Environment/General leaf?
I noticed that if object explorer is not mentioned in selected combo,
servers don't show up.
Peter|||Hi Rogas,
I don't see that option. From Entreprise Manager->Tools->Options I have two
leafs: General and Advanced. In General there is nothing related with you
said.
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Rogas69" wrote:

>
> Hi Enric, do you have 'Open object explorer and new query' in Tools/Option
s
> dialog under Environment/General leaf?
> I noticed that if object explorer is not mentioned in selected combo,
> servers don't show up.
> Peter
>
>

A couple LDF questions.

I do a bit a sql programming but do not handle the admin side. Our
server backups are becoming huge so I looked around to see why. A
couple of our databases (small... ~60Megs) have ldf files that are
almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
shows that there are almost 200 virtual logs in each ldf file, all
with active transactions. One of these databases is still attached but
hasn't been used for over a year. How can I determine more information
about these transactions, or force old ones to close? Our datacenter
handles the actual scheduled backups so I don't really want to mess
with too much but i'm hoping there's a way for me to shrink the log
size to something more 'appropriate' without losing any necessary
information. Is this something that they (datacenter) should handle
(they don't manage our databases, just the backups) or is this likely
to be a software issue not closing the transactions so they persist?
Any thoughts or comments greatly appreciated.
Thanks.
Mark<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
>
My guess is that they are NOT handling backups correctly. Sounds like they
may not be doing a transaction log backup.
Try DBCC opentran on each database. This should return the SPIDs of any
open transactions.
dbcc inputbuffer(spid)
and
sp_who2 spid
to find out what they are doing.
You might be able to kill the spids.

> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||As Greg mentioned, you have probably missed doing log backups for the databa
se. If the db is in full
recovery mode, and you don't do log backups, then the log file is never "emp
tied". Or, in other
words, the virtual log file will have a status of 2. If you don't want to do
log backups, you should
set the database to simple recovery mode.
I also want to point out that status 2 is not the same as open transactions.
It just mean that the
virtual log file cannot be re-used, because you haven't done a log backup ye
t.
Some more info at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>|||many thanks to both. that helps. i'll try the code to learn more about
the spid's and will call the datacenter to see what the backup
settings are. thanks again.

A couple LDF questions.

I do a bit a sql programming but do not handle the admin side. Our
server backups are becoming huge so I looked around to see why. A
couple of our databases (small... ~60Megs) have ldf files that are
almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
shows that there are almost 200 virtual logs in each ldf file, all
with active transactions. One of these databases is still attached but
hasn't been used for over a year. How can I determine more information
about these transactions, or force old ones to close? Our datacenter
handles the actual scheduled backups so I don't really want to mess
with too much but i'm hoping there's a way for me to shrink the log
size to something more 'appropriate' without losing any necessary
information. Is this something that they (datacenter) should handle
(they don't manage our databases, just the backups) or is this likely
to be a software issue not closing the transactions so they persist?
Any thoughts or comments greatly appreciated.
Thanks.
Mark
<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegr oups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
>
My guess is that they are NOT handling backups correctly. Sounds like they
may not be doing a transaction log backup.
Try DBCC opentran on each database. This should return the SPIDs of any
open transactions.
dbcc inputbuffer(spid)
and
sp_who2 spid
to find out what they are doing.
You might be able to kill the spids.

> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||As Greg mentioned, you have probably missed doing log backups for the database. If the db is in full
recovery mode, and you don't do log backups, then the log file is never "emptied". Or, in other
words, the virtual log file will have a status of 2. If you don't want to do log backups, you should
set the database to simple recovery mode.
I also want to point out that status 2 is not the same as open transactions. It just mean that the
virtual log file cannot be re-used, because you haven't done a log backup yet.
Some more info at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegr oups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>
|||many thanks to both. that helps. i'll try the code to learn more about
the spid's and will call the datacenter to see what the backup
settings are. thanks again.

A couple LDF questions.

I do a bit a sql programming but do not handle the admin side. Our
server backups are becoming huge so I looked around to see why. A
couple of our databases (small... ~60Megs) have ldf files that are
almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
shows that there are almost 200 virtual logs in each ldf file, all
with active transactions. One of these databases is still attached but
hasn't been used for over a year. How can I determine more information
about these transactions, or force old ones to close? Our datacenter
handles the actual scheduled backups so I don't really want to mess
with too much but i'm hoping there's a way for me to shrink the log
size to something more 'appropriate' without losing any necessary
information. Is this something that they (datacenter) should handle
(they don't manage our databases, just the backups) or is this likely
to be a software issue not closing the transactions so they persist?
Any thoughts or comments greatly appreciated.
Thanks.
Mark<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
>
My guess is that they are NOT handling backups correctly. Sounds like they
may not be doing a transaction log backup.
Try DBCC opentran on each database. This should return the SPIDs of any
open transactions.
dbcc inputbuffer(spid)
and
sp_who2 spid
to find out what they are doing.
You might be able to kill the spids.
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||As Greg mentioned, you have probably missed doing log backups for the database. If the db is in full
recovery mode, and you don't do log backups, then the log file is never "emptied". Or, in other
words, the virtual log file will have a status of 2. If you don't want to do log backups, you should
set the database to simple recovery mode.
I also want to point out that status 2 is not the same as open transactions. It just mean that the
virtual log file cannot be re-used, because you haven't done a log backup yet.
Some more info at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>|||many thanks to both. that helps. i'll try the code to learn more about
the spid's and will call the datacenter to see what the backup
settings are. thanks again.

A better way to handle repeating dates

I am a developer who works with MS SQL Server a lot, but I'm far from
an expert. I am revamping an appointment scheduling system that
allows for appointments to reoccur daily, weekly, monthly and yearly.

Currently, I am saving the appointment date as the initial appointment
date. Then when I want to check for appointments, my stored proc does
does a select on the appropriate records fitting certain critieria
(like only appointments for this doctor, at this location, etc). Once
I have these records I cycle through them calling the DateAdd() and
DateDiff() functions to see if the appointment is reoccuring during
the dates I'm looking for.

Here's is a mock up of what I'm doing. I know cursors are a huge hit
performance-wise (especially how they are used in this scenario) and
want to get a way from this, but I can't figure out how to get
reoccuring appointments to work. Any help is appreciated. Thanks.

sp_GetAppointments(@.StartDate, @.EndDate)

set @.DateToCheck = @.StartApptDate
while @.DateToCheck <= @.EndApptDate
begin
--Start a cursor
DECLARE RepeatCursor CURSOR
FORWARD_ONLY STATIC FOR

select ApptDate from ApptTable where DoctorID = 1 and

--Check if it repeats daily
((repeat = 1 and
DateAdd(d,DateDiff(d,ApptDate,@.DateToCheck),ApptDa te) =
@.DateToCheck
and DateDiff(d,ApptDate,@.DateToCheck) >0)

--Check if it repeats weekly
or (repeat = 2 and
DateAdd(wk,DateDiff(wk,ApptDate,@.DateToCheck),Appt Date) =
@.DateToCheck
and DateDiff(d,ApptDate,@.DateToCheck) >0)

CLOSE RepeatCursor
DEALLOCATE RepeatCursor

set @.DateToCheck = DateAdd(d,1,@.DateToCheck)
end"Dean" <daudirsch@.hotmail.com> wrote in message
news:1f9c615a.0407140934.33853b8d@.posting.google.c om...
> I am a developer who works with MS SQL Server a lot, but I'm far from
> an expert. I am revamping an appointment scheduling system that
> allows for appointments to reoccur daily, weekly, monthly and yearly.
> Currently, I am saving the appointment date as the initial appointment
> date. Then when I want to check for appointments, my stored proc does
> does a select on the appropriate records fitting certain critieria
> (like only appointments for this doctor, at this location, etc). Once
> I have these records I cycle through them calling the DateAdd() and
> DateDiff() functions to see if the appointment is reoccuring during
> the dates I'm looking for.
> Here's is a mock up of what I'm doing. I know cursors are a huge hit
> performance-wise (especially how they are used in this scenario) and
> want to get a way from this, but I can't figure out how to get
> reoccuring appointments to work. Any help is appreciated. Thanks.
> sp_GetAppointments(@.StartDate, @.EndDate)
> set @.DateToCheck = @.StartApptDate
> while @.DateToCheck <= @.EndApptDate
> begin
> --Start a cursor
> DECLARE RepeatCursor CURSOR
> FORWARD_ONLY STATIC FOR
> select ApptDate from ApptTable where DoctorID = 1 and
> --Check if it repeats daily
> ((repeat = 1 and
> DateAdd(d,DateDiff(d,ApptDate,@.DateToCheck),ApptDa te) =
> @.DateToCheck
> and DateDiff(d,ApptDate,@.DateToCheck) >0)
> --Check if it repeats weekly
> or (repeat = 2 and
> DateAdd(wk,DateDiff(wk,ApptDate,@.DateToCheck),Appt Date) =
> @.DateToCheck
> and DateDiff(d,ApptDate,@.DateToCheck) >0)
> CLOSE RepeatCursor
> DEALLOCATE RepeatCursor
> set @.DateToCheck = DateAdd(d,1,@.DateToCheck)
> end

I'm not sure that I see how you identify an appointment from the information
above, since it seems that you're only looking at dates. If the doctor has
an appointment today, and one in a week, how does he know if they're related
or unrelated? And what about the time of day?

In any case, some standard advice would be to remove the sp_ prefix, which
is reserved for system stored procedures, and to investigate using a
calendar table to help you with date-related queries. For more specific
advice, you will have to give more details, and someone may be able to
suggest something - CREATE TABLE statements for the tables you're looking at
(perhaps simplified), INSERT statements for sample data, and then the output
you would like to have. But if your business requirements are complex, it
may be tricky to resolve in a newsgroup.

Simon|||As Simon has suggested, some more info would help us understand your
requirements better.

Here's a simplified example of how you could generate repeating appointments
without a cursor.

CREATE TABLE Appointments (doctorid INTEGER NOT NULL, start_dt DATETIME,
end_dt DATETIME NOT NULL, CHECK (start_dt<end_dt), repeat INTEGER NOT NULL
DEFAULT 1 CHECK (repeat>0), repeat_days INTEGER NOT NULL DEFAULT 0,
repeat_months INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (doctorid, start_dt))

The Repeat column defines how many times an appointment occurs and the
Repeat_Days / Repeat_Months columns define the interval either in months or
days.

Here are some sample appointments:

INSERT INTO Appointments VALUES /* Single appointment */
(1,'2004-01-15T10:00:00.000','2004-01-15T10:30:00.000',1,0,0)

INSERT INTO Appointments VALUES /* Weekly for 5 weeks */
(2,'2004-01-16T12:30:00.000','2004-01-16T13:30:00.000',5,7,0)

INSERT INTO Appointments VALUES /* Monthly for 6 months */
(3,'2004-02-01T14:30:00.000','2004-02-01T15:30:00.000',6,0,1)

Here's the query to generate the repeating appointments (you need to create
an auxiliary Numbers table first:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp)

SELECT doctorid,
DATEADD(MONTH,(N.number-1)*repeat_months,
DATEADD(DAY,(N.number-1)*repeat_days, A.start_dt)),
DATEADD(MONTH,(N.number-1)*repeat_months,
DATEADD(DAY,(N.number-1)*repeat_days, A.end_dt))
FROM Appointments AS A
JOIN Numbers AS N
ON N.number BETWEEN 1 AND A.repeat

Whether it then makes sense to insert this result into another table or just
extrapolate the appointments with this query as needed really depends on
your business requirements.

Hope this helps.

--
David Portas
SQL Server MVP
--|||>> I am revamping an appointment scheduling system that allows for
appointments to reoccur daily, weekly, monthly and yearly. <<

The first problem you have is your mental model. Look at the words in
your specs!

>> Currently, I am saving the appointment date as the initial [sic]
appointment date. Then when I want to check for appointments, my
stored proc does does a select on the appropriate records [sic]
fitting certain critieria ... Once I have these records [sic] I cycle
[sic] through them calling the DateAdd() and DateDiff() functions
[sic] to see if the appointment is reoccuring during the dates I'm
looking for. <<

Rows are not records. Cycles (loops) are procedural. We prefer data
that holds all the facts over functions and computations that build
them on the fly.

When you make the appointment, it is not one appointment; you are
making a set of appointments ("Well, Mr. Celko, we'll see you here
every other week until you die, or your insurance gives out for the
next five years!").

Use a calendar table for the schedules so that nobody gets a check up
on Christmas and New Years. You can also predict when a doctor is
going to be overloaded in advance and prevent it. Pull out a base
schedule from the calendar table, add the client and doctor, and then
modify it as you need to later in time ("I'm too sick to come to
chemotherapy today!"). This ad hoc change is the way this is really
done.

Worse case? A daily visit for 10 years in advance costs you (365.2422
*10 rows) = 3653 rows of (datetime, patient, doctor) data in the
appointment table. It lets me replace one doctor for another in
advance, too.|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<X5adnZu_EoBOEGjdRVn-gg@.giganews.com>...
> As Simon has suggested, some more info would help us understand your
> requirements better.
> Here's a simplified example of how you could generate repeating appointments
> without a cursor.
> CREATE TABLE Appointments (doctorid INTEGER NOT NULL, start_dt DATETIME,
> end_dt DATETIME NOT NULL, CHECK (start_dt<end_dt), repeat INTEGER NOT NULL
> DEFAULT 1 CHECK (repeat>0), repeat_days INTEGER NOT NULL DEFAULT 0,
> repeat_months INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (doctorid, start_dt))
> The Repeat column defines how many times an appointment occurs and the
> Repeat_Days / Repeat_Months columns define the interval either in months or
> days.
> Here are some sample appointments:
> INSERT INTO Appointments VALUES /* Single appointment */
> (1,'2004-01-15T10:00:00.000','2004-01-15T10:30:00.000',1,0,0)
> INSERT INTO Appointments VALUES /* Weekly for 5 weeks */
> (2,'2004-01-16T12:30:00.000','2004-01-16T13:30:00.000',5,7,0)
> INSERT INTO Appointments VALUES /* Monthly for 6 months */
> (3,'2004-02-01T14:30:00.000','2004-02-01T15:30:00.000',6,0,1)
> Here's the query to generate the repeating appointments (you need to create
> an auxiliary Numbers table first:
> http://www.bizdatasolutions.com/tsql/tblnumbers.asp)
> SELECT doctorid,
> DATEADD(MONTH,(N.number-1)*repeat_months,
> DATEADD(DAY,(N.number-1)*repeat_days, A.start_dt)),
> DATEADD(MONTH,(N.number-1)*repeat_months,
> DATEADD(DAY,(N.number-1)*repeat_days, A.end_dt))
> FROM Appointments AS A
> JOIN Numbers AS N
> ON N.number BETWEEN 1 AND A.repeat
> Whether it then makes sense to insert this result into another table or just
> extrapolate the appointments with this query as needed really depends on
> your business requirements.
> Hope this helps.

Thanks to all for the assistance, it really helped.

A bad error!

hi friends!
i have a web service in .NET 2003 that contain below error and I cant handle it...
...

Anunhandled exception of type 'System.Web.Services.Protocols.SoapException'occurred in system.web.services.dll

Additionalinformation: System.Web.Services.Protocols.SoapException: Server was unable toprocess request. --> System.Data.SqlClient.SqlException: Login failed foruser 'MICROGOD3\ASPNET'.

atSystem.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,Boolean breakConnection)

atSystem.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObjectstateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehaviorrunBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

atSystem.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)

atSystem.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnectionowningObject, SqlConnectionString connectionOptions, String newPassword,Boolean redirectedUserInstance)

atSystem.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentityidentity, SqlConnectionString connectionOptions, Object providerInfo, StringnewPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

atSystem.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptionsoptions, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnectionowning Connection)

atSystem.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionowning Connection, DbConnectionPool pool, DbConnectionOptions options)

atSystem.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnectionowningObject)

atSystem.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnectionowningObject)

atSystem.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnectionowningObject)

atSystem.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

atSystem.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnectionouterConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

atSystem.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[]datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommandcommand, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataSetdataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommandcommand, CommandBehavior behavior)

atSystem.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

at LeitnerWebService.Service1.GetData() inc:\inetpub\wwwroot\leitnerwebservice\service1.asmx.cs:line 68

-- End of inner exception stack trace --

PLEASE HELP ME!The Windows user, the web service runs under on the server, user being MICROGOD3\ASPNET based on the error message, does not have permissions to log into the SQL Server you use. It should have a login in SQL Server and the login should be granted permissions to access the database in question.

5 clients takes 5 times more

Hi there!

This is the scenario: I've got a dataset to handle data in aplication tier. I've sent transactional operations to data layer. In data layer I've got stored procedures that handle xml data to "keep" and "retrieve" data in/from database. This is the idea.

After some tests I get this code in DB:

DECLARE @.Documento as xml

DECLARE @.QtdReg as int

DECLARE @.Contador as int

SET @.Contador = 1

Set @.Documento = '

<ROOT>

<Mesas>

<CMesa>1</CMesa>

<CodMesa>1</CodMesa>

<Designacao>TESTE</Designacao>

<NPessoas>5</NPessoas>

<Ocupacao>0</Ocupacao>

<Designa>1 - Mesa 1 </Designa>

</Mesas>

<Mesas>

<CMesa>10</CMesa>

<CodMesa>10</CodMesa>

<Designacao>TESTE 10</Designacao>

<NPessoas>5</NPessoas>

<Ocupacao>0</Ocupacao>

<Designa>1 - Mesa 10 </Designa>

</Mesas>

</ROOT>'

SET @.QtdReg = @.Documento.value('count(//ROOT/Mesas)', 'int' )

print @.qtdreg

WHILE @.Contador<=@.QtdReg

BEGIN

SELECT

@.Documento.value('(//ROOT/Mesas[sql:variable("@.Contador")]/CodMesa)[1]','integer') Chave,

@.Documento.value('(//ROOT/Mesas[sql:variable("@.Contador")]/Designacao)[1]','varchar(255)') Designacao,

@.Documento.query('//ROOT/Mesas[sql:variable("@.Contador")]') RegistoXML

SET @.Contador = @.Contador + 1

END

I'm just doing selects (but, if I put an INSERT command before the SELECT I can insert data in DB).

The problem is: If I increment the xml data (I've tested with 500 rows - not inserted it here for space reasons - you can copy/paste one row and create 500 rows) it takes about 1 minute to handle all registers.

Worst, if I put this code in SQL SERVER MANAGEMENT STUDIO in 5 different queries (simulating 5 different clients) the time to execute the select takes 5 times more.

Am I doing this in the wrong way? Can you help me doing the right way?

Thank you very much for your time,

Rui Dias

Rui Dias VD wrote:

Hi there!

This is the scenario: I've got a dataset to handle data in aplication tier. I've sent transactional operations to data layer. In data layer I've got stored procedures that handle xml data to "keep" and "retrieve" data in/from database. This is the idea.

After some tests I get this code in DB:

DECLARE @.Documento as xml

DECLARE @.QtdReg as int

DECLARE @.Contador as int

SET @.Contador = 1

Set @.Documento = '

<ROOT>

<Mesas>

<CMesa>1</CMesa>

<CodMesa>1</CodMesa>

<Designacao>TESTE</Designacao>

<NPessoas>5</NPessoas>

<Ocupacao>0</Ocupacao>

<Designa>1 - Mesa 1 </Designa>

</Mesas>

<Mesas>

<CMesa>10</CMesa>

<CodMesa>10</CodMesa>

<Designacao>TESTE 10</Designacao>

<NPessoas>5</NPessoas>

<Ocupacao>0</Ocupacao>

<Designa>1 - Mesa 10 </Designa>

</Mesas>

</ROOT>'

SET @.QtdReg = @.Documento.value('count(//ROOT/Mesas)', 'int' )

print @.qtdreg

WHILE @.Contador<=@.QtdReg

BEGIN

SELECT

@.Documento.value('(//ROOT/Mesas[sql:variable("@.Contador")]/CodMesa)[1]','integer') Chave,

@.Documento.value('(//ROOT/Mesas[sql:variable("@.Contador")]/Designacao)[1]','varchar(255)') Designacao,

@.Documento.query('//ROOT/Mesas[sql:variable("@.Contador")]') RegistoXML

SET @.Contador = @.Contador + 1

END

I'm just doing selects (but, if I put an INSERT command before the SELECT I can insert data in DB).

The problem is: If I increment the xml data (I've tested with 500 rows - not inserted it here for space reasons - you can copy/paste one row and create 500 rows) it takes about 1 minute to handle all registers.

Worst, if I put this code in SQL SERVER MANAGEMENT STUDIO in 5 different queries (simulating 5 different clients) the time to execute the select takes 5 times more.

Am I doing this in the wrong way? Can you help me doing the right way?

Thank you very much for your time,

Rui Dias

Sorry, forgot to tell:

I'm using SQL Server 2005 and Visual Studio 2005

|||

You should never write this type of loop. If you want to map one Mesas element to one row, please use the nodes() method as in:

SELECT

d.value('(CodMesa)[1]','integer') Chave,

d.value('(Designacao)[1]','varchar(255)') Designacao,

d.query('.') RegistoXML

FROM @.Documento.nodes('/ROOT/Mesas') as N(d)

(also please try to avoid // if you know the path).

Best regards

Michael