is too long. Maximum length is 128. Error

I try to Update a field of a table using this statement

UPDATE Table SET field="Forget......(long text)" WHERE id=1

and I get this error

The identifier that starts with 'Forget your busexcursions. Marta Patiño takes a trip out of this world at LaLaguna's Science Museum.In April 2001, De' is too long. Maximum length is 128.

What is wrong?

Looks like the "field" column in your table is defined to hold a maximum of 128 characters of data. Update your table definition to make the column bigger or reduce the size of your data.

Bill

|||Literal text is enclosed in single quotes. Identifiers (Like column names) may be enclosed in double quotes. Because you've put the text in double quotes, it is saying that your column name (That whole block of text) is too long.|||

If you use a t-sql statement like

Update Models Set LocalDescription = "General Description" Where ModelId = 2

And if you have LocalDescription and "General Description" as the name of the fields you will update one field with orders value.

"" points to an identifier (a field name) is up to 128 characters.

If you are trying to set a field with a value more than it is expecting you will get "string or binary data would be truncated" error message.

Eralper

http://www.kodyaz.com

is this wrong ?

declare @.name1 varchar(100)
select @.name = 'table1'
Truncate table @.name
I get an error at the truncate table statement
Whats the correct way of writing this ?
ThanksWhy do it with a variable? You just need to say:
truncate table table1;
(See TRUNCATE TABLE
<http://msdn.microsoft.com/library/e..._ta-tz_2hk5.asp> in BOL.)
Is this part of something larger that's causing you issues? If you need
to do this in a repeating loop for many tables then you'll have to use
dynamic sql (see sp_executesql
<http://msdn.microsoft.com/library/e..._ea-ez_2h7w.asp>
in BOL). Something like:
exec sp_executesql
N'TRUNCATE TABLE @.tablename',
N'@.tablename sysname',
@.tablename = N'table1';
with a looping wrapper (ie. cursor) around it.
*mike hodgson*
http://sqlnerd.blogspot.com
Hassan wrote:

>declare @.name1 varchar(100)
>select @.name = 'table1'
>Truncate table @.name
>I get an error at the truncate table statement
>Whats the correct way of writing this ?
>Thanks
>
>|||Mike Hodgson (e1minst3r@.gmail.com) writes:
> exec sp_executesql
> N'TRUNCATE TABLE @.tablename',
> N'@.tablename sysname',
> @.tablename = N'table1';
This has the same problem as the original post. You cannot use a variable
to hold the name of a table.
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|||Hassan wrote:
> declare @.name1 varchar(100)
> select @.name = 'table1'
> Truncate table @.name
> I get an error at the truncate table statement
> Whats the correct way of writing this ?
> Thanks
Use dynamic SQL:
Declare @.sql nvarchar(255)
Set @.sql = N'Truncate Table [' + @.name + N']'
EXEC (@.sql)
David Gugick - SQL Server MVP
Quest Software|||Yeah (oops). I discovered that just after I'd posted this reply (when I
was building up a reply to the next post regarding dropping all foreign
keys in a database) - same with an ALTER TABLE.
*mike hodgson*
http://sqlnerd.blogspot.com
Erland Sommarskog wrote:

>Mike Hodgson (e1minst3r@.gmail.com) writes:
>
>This has the same problem as the original post. You cannot use a variable
>to hold the name of a table.
>
>|||SQL is a compiled programming language. Do you have any idea what a
compiler is? Please, please do not try to write SQL; you have no idea
what you are doing and need at least a year of intense education and
not just in SQL. .|||How is the book coming along?
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1140477991.507316.78270@.f14g2000cwb.googlegroups.com...
> SQL is a compiled programming language. Do you have any idea what a
> compiler is? Please, please do not try to write SQL; you have no idea
> what you are doing and need at least a year of intense education and
> not just in SQL. .
>

Is this SQL related problem?

Any idea the cause of this error. I got it while trying to execute a stored proc (in sql 2005). I had just restored database and below error is stucking me to move ahead. Any help please

Error invoking method 'ExecuteQuery' for transaction (Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding)

Timeout means that the server does not replies or completes the job within 30 sec (default)
Are you sure the the database is online ?
Can you execute a simple query such as select @.@.spid without having error ?
Can you post the query ?

Is this Query valid?

Hello Experts,
Is this query is valid or not? it si giving me error invalid colum name
"exceptCount"
Update lib_RoundPerformance Set Successful = exceptCount select
COUNT(DISTINCT dbo.MSC_ArchivedResult.ResultID) AS exceptCount
FROM dbo.MSC_ArchivedResult INNER JOIN
dbo.lib_RoundPerformance ON
dbo.MSC_ArchivedResult.ArchivedSessionId_fk =
dbo.lib_RoundPerformance.ArchivedSessionID AND
dbo.MSC_ArchivedResult.RoundID =
dbo.lib_RoundPerformance.RoundID
WHERE (dbo.MSC_ArchivedResult.ReadType = 'E')
GROUP BY dbo.MSC_ArchivedResult.ArchivedSessionId_fk,
dbo.MSC_ArchivedResult.RoundID
Any help is appriciated, Thanks in advance.
RikRik,
You have two queries here.
-- First query
Update lib_RoundPerformance Set Successful = exceptCount
-- Second query
select
COUNT(DISTINCT dbo.MSC_ArchivedResult.ResultID) AS exceptCount
FROM dbo.MSC_ArchivedResult INNER JOIN
dbo.lib_RoundPerformance ON
dbo.MSC_ArchivedResult.ArchivedSessionId_fk =
dbo.lib_RoundPerformance.ArchivedSessionID AND
dbo.MSC_ArchivedResult.RoundID =
dbo.lib_RoundPerformance.RoundID
WHERE (dbo.MSC_ArchivedResult.ReadType = 'E')
GROUP BY dbo.MSC_ArchivedResult.ArchivedSessionId_fk,
dbo.MSC_ArchivedResult.RoundID
The first query appears to be invalid, since exceptCount is not
a column of the table lib_RoundPerformance.
Perhaps you mean to do this:
Update dbo.lib_RoundPerformance Set
Successful = (
select COUNT(DISTINCT dbo.MSC_ArchivedResult.ResultID)
FROM dbo.MSC_ArchivedResult
WHERE dbo.MSC_ArchivedResult.ArchivedSessionId_fk =
dbo.lib_RoundPerformance.ArchivedSessionID
AND dbo.MSC_ArchivedResult.RoundID = dbo.lib_RoundPerformance.RoundID
AND dbo.MSC_ArchivedResult.ReadType = 'E'
)
But this is just a guess.
Steve Kass
Drew University
Rik wrote:

>Hello Experts,
>Is this query is valid or not? it si giving me error invalid colum name
>"exceptCount"
>
>Update lib_RoundPerformance Set Successful = exceptCount select
>COUNT(DISTINCT dbo.MSC_ArchivedResult.ResultID) AS exceptCount
>FROM dbo.MSC_ArchivedResult INNER JOIN
> dbo.lib_RoundPerformance ON
>dbo.MSC_ArchivedResult.ArchivedSessionId_fk =
>dbo.lib_RoundPerformance.ArchivedSessionID AND
> dbo.MSC_ArchivedResult.RoundID =
>dbo.lib_RoundPerformance.RoundID
>WHERE (dbo.MSC_ArchivedResult.ReadType = 'E')
>GROUP BY dbo.MSC_ArchivedResult.ArchivedSessionId_fk,
>dbo.MSC_ArchivedResult.RoundID
>
>Any help is appriciated, Thanks in advance.
>Rik
>
>|||A correction. You may want to update only those rows for which
there are matching rows in MSC_ArchivedResult:
Update dbo.lib_RoundPerformance Set
Successful = (
select COUNT(DISTINCT dbo.MSC_ArchivedResult.ResultID)
FROM dbo.MSC_ArchivedResult
WHERE dbo.MSC_ArchivedResult.ArchivedSessionId_fk =
dbo.lib_RoundPerformance.ArchivedSessionID
AND dbo.MSC_ArchivedResult.RoundID = dbo.lib_RoundPerformance.RoundID
AND dbo.MSC_ArchivedResult.ReadType = 'E'
)
where exists (
select * from dbo.MSC_ArchivedResult
WHERE dbo.MSC_ArchivedResult.ArchivedSessionId_fk =
dbo.lib_RoundPerformance.ArchivedSessionID
AND dbo.MSC_ArchivedResult.RoundID = dbo.lib_RoundPerformance.RoundID
AND dbo.MSC_ArchivedResult.ReadType = 'E'
)
SK
Steve Kass wrote:
> Rik,
> You have two queries here.
> -- First query
> Update lib_RoundPerformance Set Successful = exceptCount
>
> -- Second query
> select COUNT(DISTINCT dbo.MSC_ArchivedResult.ResultID) AS exceptCount
> FROM dbo.MSC_ArchivedResult INNER JOIN
> dbo.lib_RoundPerformance ON
> dbo.MSC_ArchivedResult.ArchivedSessionId_fk =
> dbo.lib_RoundPerformance.ArchivedSessionID AND
> dbo.MSC_ArchivedResult.RoundID =
> dbo.lib_RoundPerformance.RoundID
> WHERE (dbo.MSC_ArchivedResult.ReadType = 'E')
> GROUP BY dbo.MSC_ArchivedResult.ArchivedSessionId_fk,
> dbo.MSC_ArchivedResult.RoundID
>
> The first query appears to be invalid, since exceptCount is not
> a column of the table lib_RoundPerformance.
> Perhaps you mean to do this:
> Update dbo.lib_RoundPerformance Set
> Successful = (
> select COUNT(DISTINCT dbo.MSC_ArchivedResult.ResultID)
> FROM dbo.MSC_ArchivedResult
> WHERE dbo.MSC_ArchivedResult.ArchivedSessionId_fk =
> dbo.lib_RoundPerformance.ArchivedSessionID
> AND dbo.MSC_ArchivedResult.RoundID = dbo.lib_RoundPerformance.RoundID
> AND dbo.MSC_ArchivedResult.ReadType = 'E'
> )
> But this is just a guess.
>
> Steve Kass
> Drew University
>
> Rik wrote:
>|||Thanks you steve, You are genious mate.
Your Second Option Works.
Have a good wend.
Ta
Rik
"Steve Kass" <skass@.drew.edu> wrote in message
news:ejZSM41KFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Rik,
> You have two queries here.
> -- First query
> Update lib_RoundPerformance Set Successful = exceptCount
>
> -- Second query
> select COUNT(DISTINCT dbo.MSC_ArchivedResult.ResultID) AS exceptCount
> FROM dbo.MSC_ArchivedResult INNER JOIN
> dbo.lib_RoundPerformance ON
> dbo.MSC_ArchivedResult.ArchivedSessionId_fk =
> dbo.lib_RoundPerformance.ArchivedSessionID AND
> dbo.MSC_ArchivedResult.RoundID =
> dbo.lib_RoundPerformance.RoundID
> WHERE (dbo.MSC_ArchivedResult.ReadType = 'E')
> GROUP BY dbo.MSC_ArchivedResult.ArchivedSessionId_fk,
> dbo.MSC_ArchivedResult.RoundID
>
> The first query appears to be invalid, since exceptCount is not
> a column of the table lib_RoundPerformance.
> Perhaps you mean to do this:
> Update dbo.lib_RoundPerformance Set
> Successful = (
> select COUNT(DISTINCT dbo.MSC_ArchivedResult.ResultID)
> FROM dbo.MSC_ArchivedResult
> WHERE dbo.MSC_ArchivedResult.ArchivedSessionId_fk =
> dbo.lib_RoundPerformance.ArchivedSessionID
> AND dbo.MSC_ArchivedResult.RoundID = dbo.lib_RoundPerformance.RoundID
> AND dbo.MSC_ArchivedResult.ReadType = 'E'
> )
> But this is just a guess.
>
> Steve Kass
> Drew University
>
> Rik wrote:
>

Is this possible?

hey guys,

I have a column called Error Count, which display the the error count values (Fields!error_count.value) from the dataset. And assume the report has some parameters.I want to change the values of this column by comparing the parameters value that the user specified with the values in the database. For instance, If sessions.timestamp == parameters!date.value then do something, where timestamp is a field in the sessions table and parameters!date.value is the the parameter value.

Please let me know if anybody came across this kind of situation and how you solve it.

Any idea is appreciated

Sincerely

Amde

Two approaches come to mind for accomplishing this.

The first way is to use a stored procedure, where you pass the report parameters to the stored procedure. In the stored procedure you have complete control over what values get put in the dataset.

The second approach would be to use a custom data processing extension. You could change the contents of the .Net data set returned by your sql query before returning it to reporting services. Or, another way would be to change the values on the fly when reporting services asks for a particular field from the data set.

I suggest using the first way, as it will require less code and infrastructure complexity.

Is this possible?

Hey guys,

I wrote a T-SQL query to retrieve all errors name and count the error for each error name based on some conditions. Assume that errors_name is a column name in Error table and the error_count is a calculated field. Here is my question; when I run the query, obviously, it displays the list of errors name and the corresponding errors count which only meets the where condition . However, what I need is to list all the errors name (even if they don't meet the condition) and assign a zero value to the error count column for those errors name that do not fulfill the where condition.

Can anybody assist me?

Appreciate your help.

Sincerely,

Amde

given:

create table ErrorSpec(Id, Message)|||

Yeah, just use a left outer join, and then sum on non-null values (hopefully this matches your need. If not, post table structures and data :):

SET NOCOUNT ON
GO
CREATE TABLE errorType
(
errorTypeId varchar(10) PRIMARY KEY
)
CREATE TABLE errorOccurence
(
errorOccurence int IDENTITY PRIMARY KEY,
occurred datetime DEFAULT (getdate()),
errorTypeId varchar(10) REFERENCES errorType(errorTypeId)
)
GO

INSERT INTO errorType
SELECT 'Type1'
UNION ALL
SELECT 'Type2'
UNION ALL
SELECT 'Type3'

GO

INSERT INTO errorOccurence (errorTypeId)
SELECT 'Type1'
UNION ALL
SELECT 'Type1'
UNION ALL
SELECT 'Type1'
UNION ALL
SELECT 'Type1'
UNION ALL
SELECT 'Type3'
go

SELECT errorType.errorTypeId,
sum(case WHEN errorOccurence.errorTypeId IS NOT NULL THEN 1 ELSE 0 end)
FROM errorType
LEFT OUTER JOIN errorOccurence
ON errorType.errorTypeId = errorOccurence.errorTypeId
GROUP BY errorType.errorTypeId

|||

oops. . . yeah I meant left outer join

should have read:

given:

create table ErrorSpec(Id, Message) create table ErrorOccurance(ErrorSpec_Id, DateOfError)|||

Hey guys,

Thank you for your assistance. I already did what you suggested, however adding the left outer join clause doesn't solve my problem.

Here is the code that I wrote:

--

SELECT

e.eventtypename,m.eventtypeid,Count(m.eventtypeid) as [error count],Count(I.instanceid) as [session count]

FROM

eventtypes e

LEFT OUTER JOIN

messages m

ON

e.eventtypeid = m.eventtypeid

LEFT OUTER JOIN

instances i

ON

m.instanceid = i.instanceid

LEFT OUTER JOIN

sessions s

ON

i.instanceid = s.instanceid

LEFT OUTER JOIN

applications a

ON

s.applicationid = a.applicationid

WHERE

m.instanceid IN(SELECT s.instanceid FROM sessions

WHERE(s.[timestamp] between '2006-02-25 01:23:52.883' and '2006-02-29 01:29:15.513')

AND(a.applicationid = 1002))

AND(I.rootinstanceid = 0)

GROUP BY e.eventtypename,m.eventtypeid

GO

--

The output of this query is as follow:

eventtypename eventtypeid error count session count

Trace 4 492 492

--

So obviously only the data which fulfill the where condition will be displayed. That is true! however, I need also to display those eventtypename which doesn's fulfill the where condition and assign a zero value. For instance, the following table shows the output that I need.

eventtypename eventtypeid error count session count

Trace 4 492 492

Debug failur 2 0 0

Trace warning 3 0 0

Warning event 1 0 0

and so on......

--

I think I am now clear.

Please let me know if you need more explanation?

Sincerely,

Amde

---

|||

remove the timstamp criteria from the where clause and immediately after the "group by" put:

having s.[timestamp] between '2006-02-25 01:23:52.883' and '2006-02-29 01:29:15.513'

the where is applied at the join point.

the Having statement applies a filter on the grouping

|||

Dear Blair,

I tried it, but it didn't solve my problem.

Let me know if you have another idea.

Sincerely,

Amde

|||post a simple script that makes and populates a similar set of tables.|||

I believe when you create your Left Joins, you should add your filter criteria to the ON clause. Putting your filter criteria in the WHERE will filter out all rows that do not meet the criteria, so when there would be no rows whose COUNT == 0, because the WHERE would filter those rows out. Putting the criteria in the ON clause will at least keep the left portion of the row.

You can do this either by:
1. putting the filter criteria one of the LEFT JOIN clause's. (This looks like it might be more work.)
2. LEFT JOINing the eventtypes table with a subquery that filters the messages table.

It may be easier for you to diagnose if you remove the GROUP BY and COUNTS until you get your rowset looking like:

eventtypename eventtypeid m.eventtypeid I.instanceid
Trace 4 <value> <value>
Trace 4 <value> <value>
Trace 4 <value> <value>
Trace 4 <value> <value>
Trace 4 <value> <value>
Trace 4 <value> <value>
<....492 times>
Debug failur 2 NULL NULL
Trace warning 3 NULL NULL
Warning event 1 NULL NULL

then you can add your COUNTs and GROUP BY back and you should have the calculation you are looking for.

Also with representative data we could give more specific assistance.

|||

First. . . you need all your instances where rootinstanceID = 0 with its associated sessions.

then that is used in the left join with messages/events -

try this:

SELECT e.eventtypename, m.eventtypeid,
Count(m.eventtypeid) AS [error count],
Count(temp.instanceid) AS [session count]
FROM eventtypes e LEFT OUTER JOIN messages m ON e.eventtypeid = m.eventtypeid
LEFT OUTER JOIN
(
SELECT i.instanceid
FROM instances INNER JOIN sessions s ON i.instanceid = s.instanceid
INNER JOIN applications a ON a.applicationid = s.applicationid
WHERE rootinstanceid = 0
AND s.[timestamp] BETWEEN '2006-02-25 01:23:52.883' AND '2006-02-29 01:29:15.513'
) temp
ON m.instanceid = temp.instanceid
GROUP BY e.eventtypename,m.eventtypeid

|||

Thank you guys, both Blair and Todd.

I got what I want. Now it's working

Appreciate it!

Sincerely,

Amde

|||

Dear Blair and Todd,

As I told you earlier, everything works fine, however, I want to bind the error count column with the s.timestamp condition. In other words, I want to count those errors which occured under the given date.

Do you have any idea to implement this functionality with out affecting the previous logic?

Sincerely,

Amde

|||

The other technique to try would be to embed a nested sub-query within one of your columns. This will work better if the relationship between your applications and sessions is not 1-to-n. Without seeing the data, I don't know if this is what you are looking for, but it will allow you to count "error count" and "session count" independently if this is what you need to do. Here is an example syntax with Customers counting Orders:

select
c.CustomerId,
(SELECT count(*) from Orders o where o.CustomerId = c.CustomerId) as o_count
FROM
Customers c

This will work as long as the SELECT statement returns at most 1 row and 1 column.

The sub-select is nested in the column selector, which would allow you to count "applications" and "messages" seperately.

|||

the inline select count is going to be a little slow.

I dont know why my suggestion didnt work. . .

try:

SELECT e.eventtypename, m.eventtypeid,
Count(m.eventtypeid) AS [error count],
Count(i.instanceid) AS [session count]
FROM eventtypes e LEFT OUTER JOIN messages m ON e.eventtypeid = m.eventtypeid
LEFT OUTER JOIN instances i on m.instanceid = temp.instanceid
LEFT OUTER JOIN session s ON i.instanceid = s.instanceid
LEFT OUTER JOIN applications a ON a.applicationid = s.applicationid
GROUP BY e.eventtypename,m.eventtypeid
HAVING isNull(rootinstanceid, 0) = 0
AND IsNull(s.[timestamp], '2006-02-25 01:23:52.883')
BETWEEN '2006-02-25 01:23:52.883' AND '2006-02-29 01:29:15.513'

Note that 'Having' will not take into account indexes.

Again, I think my nested select should work.

Post some create table/insert statements so I can build a dummy to work with if that doesnt work.

cheers

|||

Dear Blair,

I am not saying your suggestion doesn't work. It is perfect. And your nested select statement works fine. That is really what I wanted. So here is the thing; in the previous code(nested query), the [session count] is filtered based on the s.timestamp value. That is great. Similarly, I need also to filter the [error count] based on the s.timestamp value, so that it will count those errors in the specified timestamp and return a zero value if the given date is not with in the timestamp. In other words, we have to assign a zero value to [error count] if s.timestamp is not between '2006-02-25 01:23:52.883' AND '2006-02-29 01:29:15.513' . Make sure that this modification should not affect the previous functionality.

I appreciate your willingness to assist me.

Amde

Is this indication of Deadlock Occurs?

Hi All,
I see the following in the SQL (error) log and am curious as to
why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
SQL Server 2000 SP4. This server uses the Intel with 4 processors.
2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
ECID:33
Ec0xA9CA60C0) Value:0x802d1c0c
Cost0/270F)
2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
2005-11-13 12:52:06.71 spid4 ... (similar as above)
2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
2005-11-13 12:52:11.71 spid4 ... (similar as above)
2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
2005-11-13 12:52:16.71 spid4 ... (similar as above)
As you can see, it happens every 5 seconds, and somehow
stop by itself. I also realize that before the shows that,
I do enable DBCC TRACEON (3605,1204,-1).
The questions is:
1. does it normal situation?
2. does it means that locking occurs, but no deadlock occurs?
3. or does it means that locking occurs, and deadlock happens,
and Lock Manager does terminate one/more SPID?
4. what is 'ResType:ExchangeId'?
Really need your help.
Regards,
Johan
Looks like deadlock did you look through the profile who the culprit
SQL is
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/
|||If you run sp_who2 while this is going on you will see the spid that is
blocking your transaction.
You can then run dbcc inputbuffer (spid #) to get more insight.
burt_king@.yahoo.com
"Johan" wrote:

> Hi All,
> I see the following in the SQL (error) log and am curious as to
> why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
> SQL Server 2000 SP4. This server uses the Intel with 4 processors.
> 2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
> ECID:33
> Ec0xA9CA60C0) Value:0x802d1c0c
> Cost0/270F)
> 2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:06.71 spid4 ... (similar as above)
> 2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:11.71 spid4 ... (similar as above)
> 2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:16.71 spid4 ... (similar as above)
> As you can see, it happens every 5 seconds, and somehow
> stop by itself. I also realize that before the shows that,
> I do enable DBCC TRACEON (3605,1204,-1).
> The questions is:
> 1. does it normal situation?
> 2. does it means that locking occurs, but no deadlock occurs?
> 3. or does it means that locking occurs, and deadlock happens,
> and Lock Manager does terminate one/more SPID?
> 4. what is 'ResType:ExchangeId'?
> Really need your help.
> Regards,
> Johan
>
>
|||Since it happened sporadically and quite fast so I don't have the chance to
run SQL Profiler.
BTW, if I have the chance to run SQL Profiler, what 'Event Classes' to
capture the trace?
Thanks
"shiv_koirala@.yahoo.com" wrote:

> Looks like deadlock did you look through the profile who the culprit
> SQL is
> --
> Regards ,
> C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
> http://www.geocities.com/dotnetinterviews/
> My Interview Blog
> http://spaces.msn.com/members/dotnetinterviews/
>
|||Since it happened sporadically and quite fast so I don't have the chance to
run sp_who2.
I also do some searching, that if deadlock really occured, then this message
will show up in ERRORLOG
Your transaction (process ID #52) was deadlocked on {lock | communication
buffer | thread} resources with another process and has been chosen as the
deadlock victim. Rerun your transaction.
Basically I need some confirmation, LOG entry below:
ResType:ExchangeId Stype:'AND' SPID:93 ECID:33 Ec0xA9CA60C0)
Value:0x802d1c0c
1. does it means that locking occurs, but no deadlock occurs?
2. if it happened quite frequently, will it degrade the overall DB
performance?
Thanks,
Johan
"burt_king" wrote:

> If you run sp_who2 while this is going on you will see the spid that is
> blocking your transaction.
> You can then run dbcc inputbuffer (spid #) to get more insight.
>
> --
> burt_king@.yahoo.com
>
sql

Is this indication of Deadlock Occurs?

Hi All,
I see the following in the SQL (error) log and am curious as to
why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
SQL Server 2000 SP4. This server uses the Intel with 4 processors.
2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
ECID:33
Ec:(0xA9CA60C0) Value:0x802d1c0c
Cost:(0/270F)
2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
2005-11-13 12:52:06.71 spid4 ... (similar as above)
2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
2005-11-13 12:52:11.71 spid4 ... (similar as above)
2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
2005-11-13 12:52:16.71 spid4 ... (similar as above)
As you can see, it happens every 5 seconds, and somehow
stop by itself. I also realize that before the shows that,
I do enable DBCC TRACEON (3605,1204,-1).
The questions is:
1. does it normal situation?
2. does it means that locking occurs, but no deadlock occurs?
3. or does it means that locking occurs, and deadlock happens,
and Lock Manager does terminate one/more SPID?
4. what is 'ResType:ExchangeId'?
Really need your help.
Regards,
JohanLooks like deadlock did you look through the profile who the culprit
SQL is
--
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/|||If you run sp_who2 while this is going on you will see the spid that is
blocking your transaction.
You can then run dbcc inputbuffer (spid #) to get more insight.
burt_king@.yahoo.com
"Johan" wrote:
> Hi All,
> I see the following in the SQL (error) log and am curious as to
> why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
> SQL Server 2000 SP4. This server uses the Intel with 4 processors.
> 2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
> ECID:33
> Ec:(0xA9CA60C0) Value:0x802d1c0c
> Cost:(0/270F)
> 2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:06.71 spid4 ... (similar as above)
> 2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:11.71 spid4 ... (similar as above)
> 2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:16.71 spid4 ... (similar as above)
> As you can see, it happens every 5 seconds, and somehow
> stop by itself. I also realize that before the shows that,
> I do enable DBCC TRACEON (3605,1204,-1).
> The questions is:
> 1. does it normal situation?
> 2. does it means that locking occurs, but no deadlock occurs?
> 3. or does it means that locking occurs, and deadlock happens,
> and Lock Manager does terminate one/more SPID?
> 4. what is 'ResType:ExchangeId'?
> Really need your help.
> Regards,
> Johan
>
>|||Since it happened sporadically and quite fast so I don't have the chance to
run SQL Profiler.
BTW, if I have the chance to run SQL Profiler, what 'Event Classes' to
capture the trace?
Thanks
"shiv_koirala@.yahoo.com" wrote:
> Looks like deadlock did you look through the profile who the culprit
> SQL is
> --
> Regards ,
> C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
> http://www.geocities.com/dotnetinterviews/
> My Interview Blog
> http://spaces.msn.com/members/dotnetinterviews/
>|||Since it happened sporadically and quite fast so I don't have the chance to
run sp_who2.
I also do some searching, that if deadlock really occured, then this message
will show up in ERRORLOG
--
Your transaction (process ID #52) was deadlocked on {lock | communication
buffer | thread} resources with another process and has been chosen as the
deadlock victim. Rerun your transaction.
--
Basically I need some confirmation, LOG entry below:
--
ResType:ExchangeId Stype:'AND' SPID:93 ECID:33 Ec:(0xA9CA60C0)
Value:0x802d1c0c
--
1. does it means that locking occurs, but no deadlock occurs?
2. if it happened quite frequently, will it degrade the overall DB
performance?
Thanks,
Johan
"burt_king" wrote:
> If you run sp_who2 while this is going on you will see the spid that is
> blocking your transaction.
> You can then run dbcc inputbuffer (spid #) to get more insight.
>
> --
> burt_king@.yahoo.com
>

Is this indication of Deadlock Occurs?

Hi All,
I see the following in the SQL (error) log and am curious as to
why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
SQL Server 2000 SP4. This server uses the Intel with 4 processors.
2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
ECID:33
Ec0xA9CA60C0) Value:0x802d1c0c
Cost0/270F)
2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
2005-11-13 12:52:06.71 spid4 ... (similar as above)
2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
2005-11-13 12:52:11.71 spid4 ... (similar as above)
2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
2005-11-13 12:52:16.71 spid4 ... (similar as above)
As you can see, it happens every 5 seconds, and somehow
stop by itself. I also realize that before the shows that,
I do enable DBCC TRACEON (3605,1204,-1).
The questions is:
1. does it normal situation?
2. does it means that locking occurs, but no deadlock occurs?
3. or does it means that locking occurs, and deadlock happens,
and Lock Manager does terminate one/more SPID?
4. what is 'ResType:ExchangeId'?
Really need your help.
Regards,
JohanLooks like deadlock did you look through the profile who the culprit
SQL is
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/|||If you run sp_who2 while this is going on you will see the spid that is
blocking your transaction.
You can then run dbcc inputbuffer (spid #) to get more insight.
burt_king@.yahoo.com
"Johan" wrote:

> Hi All,
> I see the following in the SQL (error) log and am curious as to
> why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
> SQL Server 2000 SP4. This server uses the Intel with 4 processors.
> 2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
> ECID:33
> Ec0xA9CA60C0) Value:0x802d1c0c
> Cost0/270F)
> 2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:06.71 spid4 ... (similar as above)
> 2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:11.71 spid4 ... (similar as above)
> 2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:16.71 spid4 ... (similar as above)
> As you can see, it happens every 5 seconds, and somehow
> stop by itself. I also realize that before the shows that,
> I do enable DBCC TRACEON (3605,1204,-1).
> The questions is:
> 1. does it normal situation?
> 2. does it means that locking occurs, but no deadlock occurs?
> 3. or does it means that locking occurs, and deadlock happens,
> and Lock Manager does terminate one/more SPID?
> 4. what is 'ResType:ExchangeId'?
> Really need your help.
> Regards,
> Johan
>
>|||Since it happened sporadically and quite fast so I don't have the chance to
run SQL Profiler.
BTW, if I have the chance to run SQL Profiler, what 'Event Classes' to
capture the trace?
Thanks
"shiv_koirala@.yahoo.com" wrote:

> Looks like deadlock did you look through the profile who the culprit
> SQL is
> --
> Regards ,
> C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
> http://www.geocities.com/dotnetinterviews/
> My Interview Blog
> http://spaces.msn.com/members/dotnetinterviews/
>|||Since it happened sporadically and quite fast so I don't have the chance to
run sp_who2.
I also do some searching, that if deadlock really occured, then this message
will show up in ERRORLOG
--
Your transaction (process ID #52) was deadlocked on {lock | communicati
on
buffer | thread} resources with another process and has been chosen as the
deadlock victim. Rerun your transaction.
--
Basically I need some confirmation, LOG entry below:
--
ResType:ExchangeId Stype:'AND' SPID:93 ECID:33 Ec0xA9CA60C0)
Value:0x802d1c0c
--
1. does it means that locking occurs, but no deadlock occurs?
2. if it happened quite frequently, will it degrade the overall DB
performance?
Thanks,
Johan
"burt_king" wrote:

> If you run sp_who2 while this is going on you will see the spid that is
> blocking your transaction.
> You can then run dbcc inputbuffer (spid #) to get more insight.
>
> --
> burt_king@.yahoo.com
>

Is this error something to worry about?

Hi Everyone,
One of my clients started getting error messages in their "Database
Maintenance Plan" that has me concerned.
Databases: master & msdb (two error messages but same codes)
Activity: Check Data and Index Linkage
Error number: 7919
Message: Repair statement not processed. Database needs to be in single
user mode.
I checked Microsoft knowledgebase and it appears that this is a known issue
http://support.microsoft.com/default...;en-us;Q290622
What concerns me is this never happened before to any of my clients and
it seems serious as it affects key system databases Master and msdb.
Should I be worried?
Thanks
Richard
hi Richard,
Richard Fagen wrote:
> ...
> What concerns me is this never happened before to any of my clients
> and
> it seems serious as it affects key system databases Master and msdb.
> Should I be worried?
hopefully not, but you actually and unfortunately have no workaround for
that...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
I suspected as much, but I wanted confirmation from the experts
Maybe I'll convince them to upgrade to SQL 2005 later in the year.
I know ISA 2004 will be included in SBS SP1 for free! (expected to be
almost 400M!) Do you think Microsoft would be generous with SQL 2005 or
maybe offer it at a reduced price for SBS 2003 users?
Thanks for your help
Richard
Andrea Montanari wrote:
> hi Richard,
> hopefully not, but you actually and unfortunately have no workaround for
> that...
|||hi Richard,
Richard Fagen wrote:
> Hi Andrea,
> I know ISA 2004 will be included in SBS SP1 for free! (expected to be
> almost 400M!) Do you think Microsoft would be generous with SQL 2005
> or maybe offer it at a reduced price for SBS 2003 users?
I suspect this is out of my concerns :D:D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Is this DELETE possible?

I am getting error messages when I try to delete from a table using
the values in the table itself. The intent is to delete all rows from
TableA where col_2 matches any of the col_1 values.

DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 =
y.col_2)

Error msg: The table 'TableA' is ambiguous.

Can this be done with SQL or should I use T-SQL with cursors here?Try EXISTS or IN

DELETE TableA
WHERE EXISTS (SELECT * FROM TableA y
WHERE TableA.col_2 = y.col_1)

Or did you want:

DELETE TableA
WHERE EXISTS (SELECT * FROM TableA y
WHERE TableA.col_1 = y.col_2)

As always, I recommend you test with SELECT queries first. (No warrantees
implied, etc...)

"php newbie" <newtophp2000@.yahoo.com> wrote in message
news:124f428e.0407131933.72eea682@.posting.google.c om...
I am getting error messages when I try to delete from a table using
the values in the table itself. The intent is to delete all rows from
TableA where col_2 matches any of the col_1 values.

DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 =
y.col_2)

Error msg: The table 'TableA' is ambiguous.

Can this be done with SQL or should I use T-SQL with cursors here?|||Try:

DELETE FROM x
FROM TableA x
INNER JOIN TableA y ON (x.col_1 = y.col_2)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"php newbie" <newtophp2000@.yahoo.com> wrote in message
news:124f428e.0407131933.72eea682@.posting.google.c om...
> I am getting error messages when I try to delete from a table using
> the values in the table itself. The intent is to delete all rows from
> TableA where col_2 matches any of the col_1 values.
> DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 =
> y.col_2)
> Error msg: The table 'TableA' is ambiguous.
> Can this be done with SQL or should I use T-SQL with cursors here?|||Aaron,

Thanks for the tip. It worked!

On a related note, I am facing the same error when I try to update
TableA with data from the same TableA.

The command I used is this:

UPDATE TableA SET col_2 = y.col_2
FROM TableA x INNER JOIN TableA y
ON (x.col_1 = y.col_1)

Error message is: The table 'TableA' is ambiguous.

Do you have a similar solution?

"Aaron W. West" <tallpeak@.hotmail.NO.SPAM> wrote in message news:<P4udnTGzyaWGIWndRVn-hA@.speakeasy.net>...
> Try EXISTS or IN
> DELETE TableA
> WHERE EXISTS (SELECT * FROM TableA y
> WHERE TableA.col_2 = y.col_1)
> As always, I recommend you test with SELECT queries first. (No warrantees
> implied, etc...)|||On 14 Jul 2004 07:59:43 -0700, php newbie wrote:

> Aaron,
> Thanks for the tip. It worked!
> On a related note, I am facing the same error when I try to update
> TableA with data from the same TableA.
> The command I used is this:
> UPDATE TableA SET col_2 = y.col_2
> FROM TableA x INNER JOIN TableA y
> ON (x.col_1 = y.col_1)
> Error message is: The table 'TableA' is ambiguous.
> Do you have a similar solution?

If you're using "x" as a label for TableA, you need to use it throughout.

UPDATE x SET x.col_2 = y.col_2
FROM TableA x
INNER JOIN TableA y
ON (x.col_1 = y.col_1)|||I also got it to work like this:

CREATE TABLE #T (A int,B int)
INSERT #T SELECT 1,2
INSERT #T SELECT 2,2
INSERT #T SELECT 3,3
INSERT #T SELECT 4,3
INSERT #T SELECT 5,3
INSERT #T SELECT 6,4
INSERT #T SELECT 7,4

SELECT * FROM #T WHERE A IN (SELECT DISTINCT B FROM #T)

DELETE FROM #T WHERE A IN (SELECT DISTINCT B FROM #T)|||>> On a related note, I am facing the same error when I try to update
TableA with data from the same TableA. <<

Why in the world do you think that SQL has a FROM clause in UPDATE and
DELETE? You are writing unpredictable, proprietary code that EVEN
IF IT WAS ALLOWED, would not produce results.

There is no FROM clause in a Standard SQL UPDATE statement; it would
make no sense. Other products (SQL Server, Sybase and Ingres) also
use the UPDATE .. FROM syntax, but with different semantics. So it
does not port, or even worse, when you do move it, it trashes your
database. Other programmers cannot read it and maintaining it is
harder. And when Microsoft decides to change it, you will have to do
a re-write. Remember the deprecated "*=" versus "LEFT OUTER JOIN"
conversions? The last time the UPDATE FROM changed?

The correct syntax for a searched update statement is

<update statement> ::=
UPDATE <table name>
SET <set clause list>
[WHERE <search condition>]

<set clause list> ::=
<set clause> [{ , <set clause> }...]

<set clause> ::= <object column> = <update source
<update source> ::= <value expression> | NULL | DEFAULT

<object column> ::= <column name
The UPDATE clause simply gives the name of the base table or updatable
view to be changed.

Notice that no correlation name is allowed in the UPDATE clause; this
is to avoid some self-referencing problems that could occur. But it
also follows the data model in Standard SQL. When you give a table
expression a correlation name, it is to act as if a materialized table
with that correlation name has been created in the database. That
table then is dropped at the end of the statement. If you allowed
correlation names in the UPDATE clause, you would be updating the
materialized table, which would then disappear and leave the base
table untouched.

The SET clause is a list of columns to be changed or made; the WHERE
clause tells the statement which rows to use. For this discussion, we
will assume the user doing the update has applicable UPDATE privileges
for each <object column>.

* The WHERE Clause

As mentioned, the most important thing to remember about the WHERE
clause is that it is optional. If there is no WHERE clause, all rows
in the table are changed. This is a common error; if you make it,
immediately execute a ROLLBACK statement.

All rows that test TRUE for the <search condition> are marked as a
subset and not as individual rows. It is also possible that this
subset will be empty. This subset is used to construct a new set of
rows that will be inserted into the table when the subset is deleted
from the table. Note that the empty subset is a valid update that
will fire declarative referential actions and triggers.

* The SET Clause

Each assignment in the <set clause list> is executed in parallel and
each SET clause changes all the qualified rows at once. Or at least
that is the theoretical model. In practice, implementations will
first mark all of the qualified rows in the table in one pass, using
the WHERE clause. If there were no problems, then the SQL engine
makes a copy of each marked row in working storage. Each SET clause
is executed based on the old row image and the results are put in the
new row image. Finally, the old rows are deleted and the new rows are
inserted. If an error occurs during all of this, then system does a
ROLLBACK, the table is left unchanged and the errors are reported.
This parallelism is not like what you find in a traditional
third-generation programming language, so it may be hard to learn.
This feature lets you write a statement that will swap the values in
two columns, thus:

UPDATE MyTable
SET a = b, b = a;

This is not the same thing as

BEGIN ATOMIC
UPDATE MyTable
SET a = b;
UPDATE MyTable
SET b = a;
END;

In the first UPDATE, columns a and b will swap values in each row. In
the second pair of UPDATEs, column a will get all of the values of
column b in each row. In the second UPDATE of the pair, a, which now
has the same value as the original value of b, will be written back
into column b -- no change at all. There are some limits as to what
the value expression can be. The same column cannot appear more than
once in a <set clause list> -- which makes sense, given the parallel
nature of the statement. Since both go into effect at the same time,
you would not know which SET clause to use.

If a subquery expression is used in a <set clause>, and it returns a
single value, the result set is cast to a scalar; if it returns an
empty, the result set is cast to a NULL; if it returns multiple rows,
a cardinality violation is raised.

Same logic for the basic delete statement:

DELETE FROM Foobar
WHERE EXISTS
(SELECT *
FROM Foobar AS F1
WHERE Foobar.col_1 = F1.col_2)|||Dan,

This works beautifully and also applies directly to the update query. Thanks a lot!

My gratitudes also go to Russ and Jim. I appreciate your help.

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<kZ9Jc.2879$Qu5.1593@.newsread2.news.pas.earthlink.n et>...
> Try:
> DELETE FROM x
> FROM TableA x
> INNER JOIN TableA y ON (x.col_1 = y.col_2)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||newtophp2000@.yahoo.com (php newbie) wrote in message news:<124f428e.0407131933.72eea682@.posting.google.com>...
> I am getting error messages when I try to delete from a table using
> the values in the table itself. The intent is to delete all rows from
> TableA where col_2 matches any of the col_1 values.
> DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 =
> y.col_2)
> Error msg: The table 'TableA' is ambiguous.
> Can this be done with SQL or should I use T-SQL with cursors here?

Hi,
try this:
DELETE x FROM TableA AS x INNER JOIN TableA AS y ON
(x.col_1 = y.col_2)

With best regards!|||newtophp2000@.yahoo.com (php newbie) wrote in message news:<124f428e.0407131933.72eea682@.posting.google.com>...
> I am getting error messages when I try to delete from a table using
> the values in the table itself. The intent is to delete all rows from
> TableA where col_2 matches any of the col_1 values.
> DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 =
> y.col_2)
> Error msg: The table 'TableA' is ambiguous.
> Can this be done with SQL or should I use T-SQL with cursors here?

Hi,
try this:
DELETE x FROM TableA AS x INNER JOIN TableA AS y ON
(x.col_1 = y.col_2)

With best regards!|||> Remember the deprecated "*=" versus "LEFT OUTER JOIN"
> conversions?

Joe,

Just out of curiosity, was LEFT OUTER JOIN (et. al.) always part of the ANSI
standard? If so, why do you think major database vendors such as Microsoft,
Sybase and Oracle choose proprietary syntax?

--
Hope this helps.

Dan Guzman
SQL Server MVP

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:18c7b3c2.0407141855.550aba73@.posting.google.c om...
> >> On a related note, I am facing the same error when I try to update
> TableA with data from the same TableA. <<
> Why in the world do you think that SQL has a FROM clause in UPDATE and
> DELETE? You are writing unpredictable, proprietary code that EVEN
> IF IT WAS ALLOWED, would not produce results.
> There is no FROM clause in a Standard SQL UPDATE statement; it would
> make no sense. Other products (SQL Server, Sybase and Ingres) also
> use the UPDATE .. FROM syntax, but with different semantics. So it
> does not port, or even worse, when you do move it, it trashes your
> database. Other programmers cannot read it and maintaining it is
> harder. And when Microsoft decides to change it, you will have to do
> a re-write. Remember the deprecated "*=" versus "LEFT OUTER JOIN"
> conversions? The last time the UPDATE FROM changed?
> The correct syntax for a searched update statement is
> <update statement> ::=
> UPDATE <table name>
> SET <set clause list>
> [WHERE <search condition>]
> <set clause list> ::=
> <set clause> [{ , <set clause> }...]
> <set clause> ::= <object column> = <update source>
> <update source> ::= <value expression> | NULL | DEFAULT
> <object column> ::= <column name>
> The UPDATE clause simply gives the name of the base table or updatable
> view to be changed.
> Notice that no correlation name is allowed in the UPDATE clause; this
> is to avoid some self-referencing problems that could occur. But it
> also follows the data model in Standard SQL. When you give a table
> expression a correlation name, it is to act as if a materialized table
> with that correlation name has been created in the database. That
> table then is dropped at the end of the statement. If you allowed
> correlation names in the UPDATE clause, you would be updating the
> materialized table, which would then disappear and leave the base
> table untouched.
> The SET clause is a list of columns to be changed or made; the WHERE
> clause tells the statement which rows to use. For this discussion, we
> will assume the user doing the update has applicable UPDATE privileges
> for each <object column>.
> * The WHERE Clause
> As mentioned, the most important thing to remember about the WHERE
> clause is that it is optional. If there is no WHERE clause, all rows
> in the table are changed. This is a common error; if you make it,
> immediately execute a ROLLBACK statement.
> All rows that test TRUE for the <search condition> are marked as a
> subset and not as individual rows. It is also possible that this
> subset will be empty. This subset is used to construct a new set of
> rows that will be inserted into the table when the subset is deleted
> from the table. Note that the empty subset is a valid update that
> will fire declarative referential actions and triggers.
> * The SET Clause
> Each assignment in the <set clause list> is executed in parallel and
> each SET clause changes all the qualified rows at once. Or at least
> that is the theoretical model. In practice, implementations will
> first mark all of the qualified rows in the table in one pass, using
> the WHERE clause. If there were no problems, then the SQL engine
> makes a copy of each marked row in working storage. Each SET clause
> is executed based on the old row image and the results are put in the
> new row image. Finally, the old rows are deleted and the new rows are
> inserted. If an error occurs during all of this, then system does a
> ROLLBACK, the table is left unchanged and the errors are reported.
> This parallelism is not like what you find in a traditional
> third-generation programming language, so it may be hard to learn.
> This feature lets you write a statement that will swap the values in
> two columns, thus:
> UPDATE MyTable
> SET a = b, b = a;
> This is not the same thing as
> BEGIN ATOMIC
> UPDATE MyTable
> SET a = b;
> UPDATE MyTable
> SET b = a;
> END;
> In the first UPDATE, columns a and b will swap values in each row. In
> the second pair of UPDATEs, column a will get all of the values of
> column b in each row. In the second UPDATE of the pair, a, which now
> has the same value as the original value of b, will be written back
> into column b -- no change at all. There are some limits as to what
> the value expression can be. The same column cannot appear more than
> once in a <set clause list> -- which makes sense, given the parallel
> nature of the statement. Since both go into effect at the same time,
> you would not know which SET clause to use.
> If a subquery expression is used in a <set clause>, and it returns a
> single value, the result set is cast to a scalar; if it returns an
> empty, the result set is cast to a NULL; if it returns multiple rows,
> a cardinality violation is raised.
> Same logic for the basic delete statement:
> DELETE FROM Foobar
> WHERE EXISTS
> (SELECT *
> FROM Foobar AS F1
> WHERE Foobar.col_1 = F1.col_2)|||Glad it helped.

--
Dan Guzman
SQL Server MVP

"php newbie" <newtophp2000@.yahoo.com> wrote in message
news:124f428e.0407141856.659b70a8@.posting.google.c om...
> Dan,
> This works beautifully and also applies directly to the update query.
Thanks a lot!
> My gratitudes also go to Russ and Jim. I appreciate your help.|||Dan,

Both implementations pre-dated the '92 standard.

VC

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%vHJc.4441$Qu5.433@.newsread2.news.pas.earthli nk.net...
> > Remember the deprecated "*=" versus "LEFT OUTER JOIN"
> > conversions?
> Joe,
> Just out of curiosity, was LEFT OUTER JOIN (et. al.) always part of the
ANSI
> standard? If so, why do you think major database vendors such as
Microsoft,
> Sybase and Oracle choose proprietary syntax?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:18c7b3c2.0407141855.550aba73@.posting.google.c om...
> > >> On a related note, I am facing the same error when I try to update
> > TableA with data from the same TableA. <<
> > Why in the world do you think that SQL has a FROM clause in UPDATE and
> > DELETE? You are writing unpredictable, proprietary code that EVEN
> > IF IT WAS ALLOWED, would not produce results.
> > There is no FROM clause in a Standard SQL UPDATE statement; it would
> > make no sense. Other products (SQL Server, Sybase and Ingres) also
> > use the UPDATE .. FROM syntax, but with different semantics. So it
> > does not port, or even worse, when you do move it, it trashes your
> > database. Other programmers cannot read it and maintaining it is
> > harder. And when Microsoft decides to change it, you will have to do
> > a re-write. Remember the deprecated "*=" versus "LEFT OUTER JOIN"
> > conversions? The last time the UPDATE FROM changed?
> > The correct syntax for a searched update statement is
> > <update statement> ::=
> > UPDATE <table name>
> > SET <set clause list>
> > [WHERE <search condition>]
> > <set clause list> ::=
> > <set clause> [{ , <set clause> }...]
> > <set clause> ::= <object column> = <update source>
> > <update source> ::= <value expression> | NULL | DEFAULT
> > <object column> ::= <column name>
> > The UPDATE clause simply gives the name of the base table or updatable
> > view to be changed.
> > Notice that no correlation name is allowed in the UPDATE clause; this
> > is to avoid some self-referencing problems that could occur. But it
> > also follows the data model in Standard SQL. When you give a table
> > expression a correlation name, it is to act as if a materialized table
> > with that correlation name has been created in the database. That
> > table then is dropped at the end of the statement. If you allowed
> > correlation names in the UPDATE clause, you would be updating the
> > materialized table, which would then disappear and leave the base
> > table untouched.
> > The SET clause is a list of columns to be changed or made; the WHERE
> > clause tells the statement which rows to use. For this discussion, we
> > will assume the user doing the update has applicable UPDATE privileges
> > for each <object column>.
> > * The WHERE Clause
> > As mentioned, the most important thing to remember about the WHERE
> > clause is that it is optional. If there is no WHERE clause, all rows
> > in the table are changed. This is a common error; if you make it,
> > immediately execute a ROLLBACK statement.
> > All rows that test TRUE for the <search condition> are marked as a
> > subset and not as individual rows. It is also possible that this
> > subset will be empty. This subset is used to construct a new set of
> > rows that will be inserted into the table when the subset is deleted
> > from the table. Note that the empty subset is a valid update that
> > will fire declarative referential actions and triggers.
> > * The SET Clause
> > Each assignment in the <set clause list> is executed in parallel and
> > each SET clause changes all the qualified rows at once. Or at least
> > that is the theoretical model. In practice, implementations will
> > first mark all of the qualified rows in the table in one pass, using
> > the WHERE clause. If there were no problems, then the SQL engine
> > makes a copy of each marked row in working storage. Each SET clause
> > is executed based on the old row image and the results are put in the
> > new row image. Finally, the old rows are deleted and the new rows are
> > inserted. If an error occurs during all of this, then system does a
> > ROLLBACK, the table is left unchanged and the errors are reported.
> > This parallelism is not like what you find in a traditional
> > third-generation programming language, so it may be hard to learn.
> > This feature lets you write a statement that will swap the values in
> > two columns, thus:
> > UPDATE MyTable
> > SET a = b, b = a;
> > This is not the same thing as
> > BEGIN ATOMIC
> > UPDATE MyTable
> > SET a = b;
> > UPDATE MyTable
> > SET b = a;
> > END;
> > In the first UPDATE, columns a and b will swap values in each row. In
> > the second pair of UPDATEs, column a will get all of the values of
> > column b in each row. In the second UPDATE of the pair, a, which now
> > has the same value as the original value of b, will be written back
> > into column b -- no change at all. There are some limits as to what
> > the value expression can be. The same column cannot appear more than
> > once in a <set clause list> -- which makes sense, given the parallel
> > nature of the statement. Since both go into effect at the same time,
> > you would not know which SET clause to use.
> > If a subquery expression is used in a <set clause>, and it returns a
> > single value, the result set is cast to a scalar; if it returns an
> > empty, the result set is cast to a NULL; if it returns multiple rows,
> > a cardinality violation is raised.
> > Same logic for the basic delete statement:
> > DELETE FROM Foobar
> > WHERE EXISTS
> > (SELECT *
> > FROM Foobar AS F1
> > WHERE Foobar.col_1 = F1.col_2)|||> Both implementations pre-dated the '92 standard.

So it appears many vendors implemented proprietary SQL extensions to address
deficiencies in the SQL-89 standard. Once the standard was enhanced to
address the need, many vendors added support for ANSI-style joins as well.

Portability is a consideration but, IMHO, is less important than
functionality in most environments.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"VC" <boston103@.hotmail.com> wrote in message
news:LuPJc.87240$JR4.26140@.attbi_s54...
> Dan,
> Both implementations pre-dated the '92 standard.
> VC
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:%vHJc.4441$Qu5.433@.newsread2.news.pas.earthli nk.net...
> > > Remember the deprecated "*=" versus "LEFT OUTER JOIN"
> > > conversions?
> > Joe,
> > Just out of curiosity, was LEFT OUTER JOIN (et. al.) always part of the
> ANSI
> > standard? If so, why do you think major database vendors such as
> Microsoft,
> > Sybase and Oracle choose proprietary syntax?
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> > news:18c7b3c2.0407141855.550aba73@.posting.google.c om...
> > > >> On a related note, I am facing the same error when I try to update
> > > TableA with data from the same TableA. <<
> > > > Why in the world do you think that SQL has a FROM clause in UPDATE and
> > > DELETE? You are writing unpredictable, proprietary code that EVEN
> > > IF IT WAS ALLOWED, would not produce results.
> > > > There is no FROM clause in a Standard SQL UPDATE statement; it would
> > > make no sense. Other products (SQL Server, Sybase and Ingres) also
> > > use the UPDATE .. FROM syntax, but with different semantics. So it
> > > does not port, or even worse, when you do move it, it trashes your
> > > database. Other programmers cannot read it and maintaining it is
> > > harder. And when Microsoft decides to change it, you will have to do
> > > a re-write. Remember the deprecated "*=" versus "LEFT OUTER JOIN"
> > > conversions? The last time the UPDATE FROM changed?
> > > > The correct syntax for a searched update statement is
> > > > <update statement> ::=
> > > UPDATE <table name>
> > > SET <set clause list>
> > > [WHERE <search condition>]
> > > > <set clause list> ::=
> > > <set clause> [{ , <set clause> }...]
> > > > <set clause> ::= <object column> = <update source>
> > > > <update source> ::= <value expression> | NULL | DEFAULT
> > > > <object column> ::= <column name>
> > > > The UPDATE clause simply gives the name of the base table or updatable
> > > view to be changed.
> > > > Notice that no correlation name is allowed in the UPDATE clause; this
> > > is to avoid some self-referencing problems that could occur. But it
> > > also follows the data model in Standard SQL. When you give a table
> > > expression a correlation name, it is to act as if a materialized table
> > > with that correlation name has been created in the database. That
> > > table then is dropped at the end of the statement. If you allowed
> > > correlation names in the UPDATE clause, you would be updating the
> > > materialized table, which would then disappear and leave the base
> > > table untouched.
> > > > The SET clause is a list of columns to be changed or made; the WHERE
> > > clause tells the statement which rows to use. For this discussion, we
> > > will assume the user doing the update has applicable UPDATE privileges
> > > for each <object column>.
> > > > * The WHERE Clause
> > > > As mentioned, the most important thing to remember about the WHERE
> > > clause is that it is optional. If there is no WHERE clause, all rows
> > > in the table are changed. This is a common error; if you make it,
> > > immediately execute a ROLLBACK statement.
> > > > All rows that test TRUE for the <search condition> are marked as a
> > > subset and not as individual rows. It is also possible that this
> > > subset will be empty. This subset is used to construct a new set of
> > > rows that will be inserted into the table when the subset is deleted
> > > from the table. Note that the empty subset is a valid update that
> > > will fire declarative referential actions and triggers.
> > > > * The SET Clause
> > > > Each assignment in the <set clause list> is executed in parallel and
> > > each SET clause changes all the qualified rows at once. Or at least
> > > that is the theoretical model. In practice, implementations will
> > > first mark all of the qualified rows in the table in one pass, using
> > > the WHERE clause. If there were no problems, then the SQL engine
> > > makes a copy of each marked row in working storage. Each SET clause
> > > is executed based on the old row image and the results are put in the
> > > new row image. Finally, the old rows are deleted and the new rows are
> > > inserted. If an error occurs during all of this, then system does a
> > > ROLLBACK, the table is left unchanged and the errors are reported.
> > > This parallelism is not like what you find in a traditional
> > > third-generation programming language, so it may be hard to learn.
> > > This feature lets you write a statement that will swap the values in
> > > two columns, thus:
> > > > UPDATE MyTable
> > > SET a = b, b = a;
> > > > This is not the same thing as
> > > > BEGIN ATOMIC
> > > UPDATE MyTable
> > > SET a = b;
> > > UPDATE MyTable
> > > SET b = a;
> > > END;
> > > > In the first UPDATE, columns a and b will swap values in each row. In
> > > the second pair of UPDATEs, column a will get all of the values of
> > > column b in each row. In the second UPDATE of the pair, a, which now
> > > has the same value as the original value of b, will be written back
> > > into column b -- no change at all. There are some limits as to what
> > > the value expression can be. The same column cannot appear more than
> > > once in a <set clause list> -- which makes sense, given the parallel
> > > nature of the statement. Since both go into effect at the same time,
> > > you would not know which SET clause to use.
> > > > If a subquery expression is used in a <set clause>, and it returns a
> > > single value, the result set is cast to a scalar; if it returns an
> > > empty, the result set is cast to a NULL; if it returns multiple rows,
> > > a cardinality violation is raised.
> > > > Same logic for the basic delete statement:
> > > > DELETE FROM Foobar
> > > WHERE EXISTS
> > > (SELECT *
> > > FROM Foobar AS F1
> > > WHERE Foobar.col_1 = F1.col_2)|||--CELKO-- (jcelko212@.earthlink.net) writes:
> There is no FROM clause in a Standard SQL UPDATE statement; it would
> make no sense.

Of course it would.

> Other programmers cannot read it and maintaining it is harder.

Au contraire, I find nested subselects more difficult to understand
and maintain.

So you have this UPDATE statement:

UPDATE tblA
SET col1 = z.col1
col2 = y.col2
FROM tblA a
JOIN ...
WHERE ...

And we are interested in which rows this statement actually hits. A little
cut and paste, select "UPDATE tblA SET", type SELECT, press Execute et
voil!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Is this a security risk?

I'm doing some testing on a vendor’s web site and ran into the error below
. I
told the vendor that displaying this kind of error could give a hacker the
information needed to hack the db or attempt SQL injection attacks etc. (btw
this is a bank). The vendor is telling me that there is no danger in
releasing this information on the web site. I thold them they need to displa
y
something else.
Assuming you or a hacker had this information, company information and the
URL where this error occurred; do you think these pose a security risk?
*** This is the error with the table database and field names changed ****
Insert statement conflicted with COLUMN CHECK constraint
'AColumnCheckConstraint'.
The conflict occurred in database 'ADatabaseName', table 'ATableName',
column 'PaymentAmount'..,
PaymentXML: 10056AWEBWEB01-4858538-14 ... WEBSERVERNAME ...Hi
It is a problem. If I was a hacker, I now have a good load of information to
start hacking with. Based on those names, I can deduce other names.
The toughest part of hacking is getting enough information so that you can
find a hole.This is a Silver platter.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Shark Bait" <SharkBait@.discussions.microsoft.com> wrote in message
news:15B676C1-BAF5-4566-BB1E-31A52B314810@.microsoft.com...
> I'm doing some testing on a vendor's web site and ran into the error
> below. I
> told the vendor that displaying this kind of error could give a hacker the
> information needed to hack the db or attempt SQL injection attacks etc.
> (btw
> this is a bank). The vendor is telling me that there is no danger in
> releasing this information on the web site. I thold them they need to
> display
> something else.
> Assuming you or a hacker had this information, company information and the
> URL where this error occurred; do you think these pose a security risk?
> *** This is the error with the table database and field names changed ****
> Insert statement conflicted with COLUMN CHECK constraint
> 'AColumnCheckConstraint'.
> The conflict occurred in database 'ADatabaseName', table 'ATableName',
> column 'PaymentAmount'..,
> PaymentXML: 10056AWEBWEB01-4858538-14 ... WEBSERVERNAME ...
>|||of Course that is a problem.
PURE Negligence.
Greg Jackson
PDX, Oregon

Is this a new SQL BUG

For the last two days, I have been checking my SQL Server error logs and this is what I find common on all the SQL Servers, with Audit Login options ON: Login failed for 'A', Login failed for 'B', Login Failed for 'C'..all the way to the last login.
For each login defined in the SQL Server, I'm finding that the error message is getting generated 18 times. My guess is that someone is trying to run some kind of code.

Any experience?Make sure you don't have any easily guessable accounts, and DEFINITELY, - change your SA password. This is not a bug (unless you don't have the latest security patch from M$, in which case it is and has been fixed). Whatch out, someone is after your server.

Is this a bug in SQL 2000/2005?

Running the script below the error number is not generated and the
transaction is rolled back ...
Should be like this, or it should nicely give the error number and let me
decide what to do?
This is very usefull when using EXECUTE() or sp_executesql().
Here is hwo you can reproduce the problem:
CREATE TABLE xTest (IDCol int, xText varchar(100))
INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
SELECT @.@.ERROR --should be 207
DROP TABLE xTestSome errors terminates the batch. See the error handling articles at www.sommarsko
g.se for details.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Florin Lazar" <FlorinLazar@.discussions.microsoft.com> wrote in message
news:A436FCDD-B959-42C6-965F-4D40B47F6434@.microsoft.com...
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest|||The batch does not compile, and thus it is never executed. So the line
"SELECT @.@.ERROR" will never be executed.
Gert-Jan
Florin Lazar wrote:
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest

Is this a bug in SQL 2000/2005?

Running the script below the error number is not generated and the
transaction is rolled back ...
Should be like this, or it should nicely give the error number and let me
decide what to do?
This is very usefull when using EXECUTE() or sp_executesql().
Here is hwo you can reproduce the problem:
CREATE TABLE xTest (IDCol int, xText varchar(100))
INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
SELECT @.@.ERROR --should be 207
DROP TABLE xTest
Some errors terminates the batch. See the error handling articles at www.sommarskog.se for details.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Florin Lazar" <FlorinLazar@.discussions.microsoft.com> wrote in message
news:A436FCDD-B959-42C6-965F-4D40B47F6434@.microsoft.com...
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest
|||The batch does not compile, and thus it is never executed. So the line
"SELECT @.@.ERROR" will never be executed.
Gert-Jan
Florin Lazar wrote:
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest

Is this a bug in SQL 2000/2005?

Running the script below the error number is not generated and the
transaction is rolled back ...
Should be like this, or it should nicely give the error number and let me
decide what to do?
This is very usefull when using EXECUTE() or sp_executesql().
Here is hwo you can reproduce the problem:
CREATE TABLE xTest (IDCol int, xText varchar(100))
INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
SELECT @.@.ERROR --should be 207
DROP TABLE xTestSome errors terminates the batch. See the error handling articles at www.sommarskog.se for details.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Florin Lazar" <FlorinLazar@.discussions.microsoft.com> wrote in message
news:A436FCDD-B959-42C6-965F-4D40B47F6434@.microsoft.com...
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest|||The batch does not compile, and thus it is never executed. So the line
"SELECT @.@.ERROR" will never be executed.
Gert-Jan
Florin Lazar wrote:
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest

Is there support for xml path in SQL Server 2005 Express Ed?

Hi.

I tried a for xml query in SQL Server 2005 Express Ed. but it says that there is an error near path. I tried the rest of the statements in the query and they were fine. So this led me to ask if it there is for xml path support in SQL Server 20005 Express Edition?

Just in case this has anything to do with it, I have an installed version of SQL Server in my PC too. But I didnt find that in the requirements in installing the Express Edition.

I really need to know this...It's kind of hard having to do the queries in for xml explicit, which works by the way.FOR XML PATH does work in SQL 2005 Express. You want to check your statement syntax to make sure it's right.|||yes, thank you.. I was more careful this time.I still have to check it with the VPS though. Thanks a lot, its reassuring to know that at least that's definitely not a possibility and so the error is still fixable.

Is there any reason to have Rpt Svc installed on a machine without IIS?

We have a server that has the report databases installed but I can't
deploy my report. I get the error - The report server cannot open a
connection to the report server database. A connection to the datgbase
is required for all request and processing.RS is an asp.net application. It requires IIS. If you want RS on the same
machine as the SQL Server DB then that machine has to have IIS installed on
it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<phillip_putzback@.insightbb.com> wrote in message
news:1128115012.134115.42460@.g43g2000cwa.googlegroups.com...
> We have a server that has the report databases installed but I can't
> deploy my report. I get the error - The report server cannot open a
> connection to the report server database. A connection to the datgbase
> is required for all request and processing.
>

is there any editable parameters?

i have written a function in report properties, but error returns when i
tried to add 1 to a report parameter, saying that parameter.value is
read-only.
anyways to have editable parameters'
thanks in advance~I have asked almost the same question a few days ago. Apparently no one can
tell us if this can be done.
"Jasonymk" wrote:
> i have written a function in report properties, but error returns when i
> tried to add 1 to a report parameter, saying that parameter.value is
> read-only.
> anyways to have editable parameters'
> thanks in advance~

Is there any better method for DTS ?

I have created a package that just export a number of tables to an Access
Database and it works fine.
However, when I rerun the package, I get error message as it cannot create
the tables (This is because they are already exists in the Access database).
What is a better way for me to handle this problem ? Should I edit the DTS
package to remove the "Create Table" step (It involves 23 tables) OR is
there any better way to create a package that can be reused ?
ThanksYou could have an 'execute SQL task', that checks for the existence of the
table first, and drops it if needed.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:eSw2cx9oFHA.568@.TK2MSFTNGP10.phx.gbl...
I have created a package that just export a number of tables to an Access
Database and it works fine.
However, when I rerun the package, I get error message as it cannot create
the tables (This is because they are already exists in the Access database).
What is a better way for me to handle this problem ? Should I edit the DTS
package to remove the "Create Table" step (It involves 23 tables) OR is
there any better way to create a package that can be reused ?
Thanks|||"Peter" wrote:
> I have created a package that just export a number of tables to an Access
> Database and it works fine.
> However, when I rerun the package, I get error message as it cannot create
> the tables (This is because they are already exists in the Access database).
> What is a better way for me to handle this problem ? Should I edit the DTS
> package to remove the "Create Table" step (It involves 23 tables) OR is
> there any better way to create a package that can be reused ?
> Thanks
>
In the "Copy SQL Server Objects Task" in DTS, there is an option to "Drop
Destination Objects First", would this be of any use?
Cheers,
Ian|||Dear Narayana,
Thank you for your advice. However, I don't know how to create an "Execute
SQL Task" to check the existence and delete the table in the Access Table.
Can you give me some advice ?
Thanks
Peter
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23KxHhN%23oFHA.3828@.TK2MSFTNGP12.phx.gbl...
> You could have an 'execute SQL task', that checks for the existence of the
> table first, and drops it if needed.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:eSw2cx9oFHA.568@.TK2MSFTNGP10.phx.gbl...
> I have created a package that just export a number of tables to an Access
> Database and it works fine.
> However, when I rerun the package, I get error message as it cannot create
> the tables (This is because they are already exists in the Access
> database).
> What is a better way for me to handle this problem ? Should I edit the
> DTS
> package to remove the "Create Table" step (It involves 23 tables) OR is
> there any better way to create a package that can be reused ?
> Thanks
>
>|||Dear Ian,
Thank you for your advice. However, I find that the options only applies to
database object and don't work for exporting to Access Database.
Peter
"Ian Murphy" <IanMurphy@.discussions.microsoft.com> wrote in message
news:522E7384-0E4F-4EF3-8E04-45E5158C3757@.microsoft.com...
>
> "Peter" wrote:
>> I have created a package that just export a number of tables to an Access
>> Database and it works fine.
>> However, when I rerun the package, I get error message as it cannot
>> create
>> the tables (This is because they are already exists in the Access
>> database).
>> What is a better way for me to handle this problem ? Should I edit the
>> DTS
>> package to remove the "Create Table" step (It involves 23 tables) OR is
>> there any better way to create a package that can be reused ?
>> Thanks
> In the "Copy SQL Server Objects Task" in DTS, there is an option to "Drop
> Destination Objects First", would this be of any use?
> Cheers,
> Ian