Wednesday, March 21, 2012
Msg 911, Level 1, State 10, Line 1, Could not locate entry in
it. I find some blog and follow the instructions, but when I execute
it, I got the Msg 911, Level 1, State 10, Line 1 error
Here is what I did:
C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
\pipe\mssql$microsoft##ssee\sql\query -E
1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
2> to disk = 'd:\sharepoint\config.bak'
3> go
Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
Could not locate entry in sysdatabases for database
'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
No entry found with that name. Make sure that the name is entered
correctly.
Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
Backup log is terminating abnormally.
Can you help? Thanks.
AaronThe backup command tries to backup a database named
"SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639". Do you have such database? If not, specify
the correct database name for the backup command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<achan@.rosewater.com> wrote in message
news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
> The SharePoint_config log file is getting too big, I want to shrink
> it. I find some blog and follow the instructions, but when I execute
> it, I got the Msg 911, Level 1, State 10, Line 1 error
> Here is what I did:
> C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
> \pipe\mssql$microsoft##ssee\sql\query -E
> 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
> 2> to disk = 'd:\sharepoint\config.bak'
> 3> go
> Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
> Could not locate entry in sysdatabases for database
> 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
> No entry found with that name. Make sure that the name is entered
> correctly.
> Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
> Backup log is terminating abnormally.
> Can you help? Thanks.
> Aaron|||Hi
I have never seen the name pipe connection in the server parameter for
SQLCMD but it seems to work!! It would be easier to do something like
np:(local) though!!
The error message says that it can find the database in sysdatabases and
that seems to be the issue.
Try
SELECT Name FROM master..sysdatabases where NAME LIKE 'SharePoint_config%'
to get the name
John
<achan@.rosewater.com> wrote in message
news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
> The SharePoint_config log file is getting too big, I want to shrink
> it. I find some blog and follow the instructions, but when I execute
> it, I got the Msg 911, Level 1, State 10, Line 1 error
> Here is what I did:
> C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
> \pipe\mssql$microsoft##ssee\sql\query -E
> 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
> 2> to disk = 'd:\sharepoint\config.bak'
> 3> go
> Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
> Could not locate entry in sysdatabases for database
> 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
> No entry found with that name. Make sure that the name is entered
> correctly.
> Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
> Backup log is terminating abnormally.
> Can you help? Thanks.
> Aaron|||I did that and it did display the file name.
"John Bell" wrote:
> Hi
> I have never seen the name pipe connection in the server parameter for
> SQLCMD but it seems to work!! It would be easier to do something like
> np:(local) though!!
> The error message says that it can find the database in sysdatabases and
> that seems to be the issue.
> Try
> SELECT Name FROM master..sysdatabases where NAME LIKE 'SharePoint_config%'
> to get the name
> John
>
> <achan@.rosewater.com> wrote in message
> news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
> > The SharePoint_config log file is getting too big, I want to shrink
> > it. I find some blog and follow the instructions, but when I execute
> > it, I got the Msg 911, Level 1, State 10, Line 1 error
> >
> > Here is what I did:
> >
> > C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
> > \pipe\mssql$microsoft##ssee\sql\query -E
> > 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
> > 2> to disk = 'd:\sharepoint\config.bak'
> > 3> go
> > Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
> > Could not locate entry in sysdatabases for database
> > 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
> > No entry found with that name. Make sure that the name is entered
> > correctly.
> > Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
> > Backup log is terminating abnormally.
> >
> > Can you help? Thanks.
> >
> > Aaron
>
>|||Yes, I use the select name from sysdatabases and it display the name. I also
look into the c:\windows\sysmsi\ssee\mssql.2005\mssql\data where it reside
and the file name is there.
"Tibor Karaszi" wrote:
> The backup command tries to backup a database named
> "SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639". Do you have such database? If not, specify
> the correct database name for the backup command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> <achan@.rosewater.com> wrote in message
> news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
> > The SharePoint_config log file is getting too big, I want to shrink
> > it. I find some blog and follow the instructions, but when I execute
> > it, I got the Msg 911, Level 1, State 10, Line 1 error
> >
> > Here is what I did:
> >
> > C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
> > \pipe\mssql$microsoft##ssee\sql\query -E
> > 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
> > 2> to disk = 'd:\sharepoint\config.bak'
> > 3> go
> > Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
> > Could not locate entry in sysdatabases for database
> > 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
> > No entry found with that name. Make sure that the name is entered
> > correctly.
> > Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
> > Backup log is terminating abnormally.
> >
> > Can you help? Thanks.
> >
> > Aaron
>|||Then There's probably something special with the SQL Server that comes with SharePoint (assuming
that you are 100% positive of the correct database name and that you connected to the correct SQL
Server instance). You might want to try this in a SharePoint group and see if they have some hints.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:AC3CD1A0-A462-4398-A560-84842EB40433@.microsoft.com...
> Yes, I use the select name from sysdatabases and it display the name. I also
> look into the c:\windows\sysmsi\ssee\mssql.2005\mssql\data where it reside
> and the file name is there.
> "Tibor Karaszi" wrote:
>> The backup command tries to backup a database named
>> "SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639". Do you have such database? If not,
>> specify
>> the correct database name for the backup command.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> <achan@.rosewater.com> wrote in message
>> news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
>> > The SharePoint_config log file is getting too big, I want to shrink
>> > it. I find some blog and follow the instructions, but when I execute
>> > it, I got the Msg 911, Level 1, State 10, Line 1 error
>> >
>> > Here is what I did:
>> >
>> > C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
>> > \pipe\mssql$microsoft##ssee\sql\query -E
>> > 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
>> > 2> to disk = 'd:\sharepoint\config.bak'
>> > 3> go
>> > Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
>> > Could not locate entry in sysdatabases for database
>> > 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
>> > No entry found with that name. Make sure that the name is entered
>> > correctly.
>> > Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
>> > Backup log is terminating abnormally.
>> >
>> > Can you help? Thanks.
>> >
>> > Aaron
>>|||Thanks Tibor.
"Tibor Karaszi" wrote:
> Then There's probably something special with the SQL Server that comes with SharePoint (assuming
> that you are 100% positive of the correct database name and that you connected to the correct SQL
> Server instance). You might want to try this in a SharePoint group and see if they have some hints.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Aaron" <Aaron@.discussions.microsoft.com> wrote in message
> news:AC3CD1A0-A462-4398-A560-84842EB40433@.microsoft.com...
> > Yes, I use the select name from sysdatabases and it display the name. I also
> > look into the c:\windows\sysmsi\ssee\mssql.2005\mssql\data where it reside
> > and the file name is there.
> >
> > "Tibor Karaszi" wrote:
> >
> >> The backup command tries to backup a database named
> >> "SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639". Do you have such database? If not,
> >> specify
> >> the correct database name for the backup command.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> <achan@.rosewater.com> wrote in message
> >> news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
> >> > The SharePoint_config log file is getting too big, I want to shrink
> >> > it. I find some blog and follow the instructions, but when I execute
> >> > it, I got the Msg 911, Level 1, State 10, Line 1 error
> >> >
> >> > Here is what I did:
> >> >
> >> > C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
> >> > \pipe\mssql$microsoft##ssee\sql\query -E
> >> > 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
> >> > 2> to disk = 'd:\sharepoint\config.bak'
> >> > 3> go
> >> > Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
> >> > Could not locate entry in sysdatabases for database
> >> > 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
> >> > No entry found with that name. Make sure that the name is entered
> >> > correctly.
> >> > Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
> >> > Backup log is terminating abnormally.
> >> >
> >> > Can you help? Thanks.
> >> >
> >> > Aaron
> >>
> >>
>|||Hi
In which case try
SELECT 'backup log [' + name + '] to disk = ''d:\sharepoint\config.bak''
FROM master..sysdatabases where NAME LIKE 'SharePoint_config%'
And the run the command generated in the output.
John
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:005608A7-6260-4D19-BF44-A09A87E4CD90@.microsoft.com...
>I did that and it did display the file name.
> "John Bell" wrote:
>> Hi
>> I have never seen the name pipe connection in the server parameter for
>> SQLCMD but it seems to work!! It would be easier to do something like
>> np:(local) though!!
>> The error message says that it can find the database in sysdatabases and
>> that seems to be the issue.
>> Try
>> SELECT Name FROM master..sysdatabases where NAME LIKE
>> 'SharePoint_config%'
>> to get the name
>> John
>>
>> <achan@.rosewater.com> wrote in message
>> news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
>> > The SharePoint_config log file is getting too big, I want to shrink
>> > it. I find some blog and follow the instructions, but when I execute
>> > it, I got the Msg 911, Level 1, State 10, Line 1 error
>> >
>> > Here is what I did:
>> >
>> > C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
>> > \pipe\mssql$microsoft##ssee\sql\query -E
>> > 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
>> > 2> to disk = 'd:\sharepoint\config.bak'
>> > 3> go
>> > Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
>> > Could not locate entry in sysdatabases for database
>> > 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
>> > No entry found with that name. Make sure that the name is entered
>> > correctly.
>> > Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
>> > Backup log is terminating abnormally.
>> >
>> > Can you help? Thanks.
>> >
>> > Aaron
>>
Monday, March 19, 2012
Msg 8621: The query processor ran out of stack space during query optimization
I got the above error when I tried to execute the following query
delete from BUSINESS_ASSOCIATE
where ba_name is null
As you can see the query isn't exactly complicated.
What the table does have is lots of dependancies. I got SSMS to show me all the dependancies and there are literally hundreds (maybe thousands - I'm not going to count them all) of dependant tables and i suspect that is what is causing the problem.
So what do I do about it? This is a valid data-model (industry standard in fact) but SQL won't let me delete data from this table and that's a huge problem for me. Any advice about how to progress would be welcomed.
-Jamie
anyone?
|||
Hi Jamie,
I doubt there's a "quick fix" here, but just out of curiosity, if you set maxdop to 1 for this statement, does it make any difference (assuming you've a multi cpu machine)?
Aside from that there's the obvious workaround of throwing more memory at the server, which I'm sure you've considered anyway.
Cheers
Rob
|||Could you please file a bug at http://connect.microsoft.com/sqlserver with repro script? Can you post the estimated showplan results if the query optimizer can even generate a plan?|||Hi guys,
I sure can. I'll do that from the office tomorrow when I see if the optimiser can produce anything..
-Jamie
|||
Jamie Thomson wrote:
Hi guys,
I sure can. I'll do that from the office tomorrow when I see if the optimiser can produce anything..
-Jamie
OK, its up here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=227892
Its private though (cos I've uploaded a DB with data in it) so oly MSFT can see it.
-Jamie
|||Thanks for creating the bug and the repro. I assigned it to the right team and I was able to reproduce the problem on a later build also. They will get back to you soon.|||Is the max SQL memory setting too low? If it is fixed, try raising it. Also, I usually leave index creation memory set to 0. Is there a value for that server there?|||This is not related to the memory allocated to SQL Server or any of those settings.|||
Umachandar Jayachandran - MS wrote:
This is not related to the memory allocated to SQL Server or any of those settings.
Thanks Uma. I was just about to go away and test that. Good job I checked here first
-Jamie
|||
It looks like you hit the documented limit on the maximum number of FK references that QP can handle on a single table. It is 253 according to BOL. See below topic:
http://msdn2.microsoft.com/en-us/library/ms143432.aspx
|||Was there ever a workaround or a resolution on this?|||There is no workaround other than simplifying your schema to reduce the number of FK references.
Msg 8621: The query processor ran out of stack space during query optimization
I got the above error when I tried to execute the following query
delete from BUSINESS_ASSOCIATE
where ba_name is null
As you can see the query isn't exactly complicated.
What the table does have is lots of dependancies. I got SSMS to show me all the dependancies and there are literally hundreds (maybe thousands - I'm not going to count them all) of dependant tables and i suspect that is what is causing the problem.
So what do I do about it? This is a valid data-model (industry standard in fact) but SQL won't let me delete data from this table and that's a huge problem for me. Any advice about how to progress would be welcomed.
-Jamie
anyone?
|||
Hi Jamie,
I doubt there's a "quick fix" here, but just out of curiosity, if you set maxdop to 1 for this statement, does it make any difference (assuming you've a multi cpu machine)?
Aside from that there's the obvious workaround of throwing more memory at the server, which I'm sure you've considered anyway.
Cheers
Rob
|||Could you please file a bug at http://connect.microsoft.com/sqlserver with repro script? Can you post the estimated showplan results if the query optimizer can even generate a plan?|||Hi guys,
I sure can. I'll do that from the office tomorrow when I see if the optimiser can produce anything..
-Jamie
|||
Jamie Thomson wrote:
Hi guys,
I sure can. I'll do that from the office tomorrow when I see if the optimiser can produce anything..
-Jamie
OK, its up here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=227892
Its private though (cos I've uploaded a DB with data in it) so oly MSFT can see it.
-Jamie
|||Thanks for creating the bug and the repro. I assigned it to the right team and I was able to reproduce the problem on a later build also. They will get back to you soon.|||Is the max SQL memory setting too low? If it is fixed, try raising it. Also, I usually leave index creation memory set to 0. Is there a value for that server there?|||This is not related to the memory allocated to SQL Server or any of those settings.|||
Umachandar Jayachandran - MS wrote:
This is not related to the memory allocated to SQL Server or any of those settings.
Thanks Uma. I was just about to go away and test that. Good job I checked here first
-Jamie
|||
It looks like you hit the documented limit on the maximum number of FK references that QP can handle on a single table. It is 253 according to BOL. See below topic:
http://msdn2.microsoft.com/en-us/library/ms143432.aspx
|||Was there ever a workaround or a resolution on this?|||There is no workaround other than simplifying your schema to reduce the number of FK references.
Msg 512 on cursor
Here is my code:
create procedure insert_sku_info
AS
Declare
@.method varchar(40),
@.sku int,
@.location varchar(40)
Declare insert_cur Cursor For
select a.method, s.sku, s.location
from archive_sku a INNER JOIN sku s on a.sku = s.sku
and a.location = s.location
Open insert_cur
Fetch Next from insert_cur
Into @.method, @.sku, @.location
While (@.@.Fetch_Status = 0)
Begin
print @.method + @.sku + @.location
Update sku
set method = @.method
where sku = @.sku
and location = @.location
Fetch Next from insert_cur
Into @.method, @.sku, @.location
End
close insert_cur
deallocate insert_curNever mind I figured it out.
That print statement I had listing all the variables was the problem.
Thanks anyway.
Msg 512 but no duplicates!?!
message. The Stored Procedure runs correctly about 1550 times, but
receive the following error three times:
Server: Msg 512, Level 16, State 1, Procedure BackFillNetworkHours,
Line 68
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
I've done some digging, and the error message is moderately
self-explanatory.
The problem is that there is no Line 68 in the Stored Procedure. It's
the comment line:
-- Need to find out how many hours the employee is scheduled etc.
Also, there are no duplicate records in the Employee table nor the
WeeklyProfile table. At least I assume so - if the following SQL to
detect duplicates is correct!
SELECT E.*
FROM
Employee E
join
(select EmployeeID
from
Employee
Group by EmployeeID
having count(*) > 1) as E2
On
(E.EmployeeID = E2.EmployeeID)
SELECT
W.*
FROM
WeekProfile W
join
(Select
WeekProfileID
FROM
WeekProfile
GROUP BY
EmployeeID, MondayHours, WeekProfileID
HAVING COUNT(*) > 1) AS W2
ON
W.WeekProfileID = W2.WeekProfileID
NOTE: In the second statement, I have tried for MondayHours thru
FridayHours.
Anyone got any ideas? The TableDefs are set up in this thread:
<http://groups-beta.google.com/group/comp.databases.ms-sqlserver/browse_frm/thread/fff4ef21e9964ab8/f5ce136923ebffc3?q=teddysnips&rnum=1&hl=en#f5ce136923ebffc3
The Stored Procedure that causes the error is here:
--************************************************** ***********
CREATE PROCEDURE BackFillNetworkHours
AS
DECLARE @.EmployeeID int
DECLARE @.TimesheetDate DateTime
DECLARE @.NumMinutes int
DECLARE @.NetworkCode int
-- Get the WorkID corresponding to Project Code 2002
SELECT
@.NetworkCode = WorkID
FROM
[Work]
WHERE
(WorkCode = '2002')
-- Open a cursor on a SELECT for all Network Support Employees where
any single workday comprises fewer than 7.5 hours
DECLARE TooFewHours CURSOR FOR
SELECT
EmployeeID,
CONVERT(CHAR(8), Start, 112) AS TimesheetDate,
SUM(NumMins) AS TotalMins
FROM
(SELECT
TI.EmployeeID,
W.WorkCode,
TI.Start AS Start,
SUM(TI.DurationMins) AS NumMins
FROM
TimesheetItem TI LEFT JOIN
[Work] W ON TI.WorkID = W.WorkID
WHERE EXISTS
(SELECT
*
FROM
Employee E
WHERE
((TI.EmployeeID = E.EmployeeID) AND
(E.DepartmentID = 2)))
GROUP BY TI.EmployeeID, TI.Start, W.WorkCode) AS x
GROUP BY
EmployeeID,
CONVERT(char(8), Start, 112)
HAVING
SUM(NumMins) < 450
ORDER BY
EmployeeID,
CONVERT(CHAR(8), Start, 112)
-- Get the EmployeeID, Date and Number of Minutes from the cursor
OPEN TooFewHours
FETCH NEXT FROM TooFewHours INTO @.EmployeeID, @.TimesheetDate,
@.NumMinutes
WHILE (@.@.FETCH_STATUS=0)
BEGIN
DECLARE @.NewWorkTime datetime
DECLARE @.TimesheetString varchar(50)
DECLARE @.Duration int
DECLARE @.RequiredDuration int
-- Set the correct date to 08:30 - by default the cast from the
cursor's select statement is midday
SET @.TimesheetString = @.TimesheetDate + ' 08:30'
SET @.NewWorkTime = CAST(@.TimesheetString AS Datetime)
-- Need to find out how many hours the employee is scheduled to work
that day.
SET @.RequiredDuration = CASE (DATEPART(dw, @.NewWorkTime))
WHEN 1 THEN
(SELECT CAST((60 * SundayHours) AS int) FROM WeekProfile WHERE
(EmployeeID = @.EmployeeID))
WHEN 2 THEN
(SELECT CAST((60 * MondayHours) AS int) FROM WeekProfile WHERE
(EmployeeID = @.EmployeeID))
WHEN 3 THEN
(SELECT CAST((60 * TuesdayHours) AS int) FROM WeekProfile WHERE
(EmployeeID = @.EmployeeID))
WHEN 4 THEN
(SELECT CAST((60 * WednesdayHours) AS int) FROM WeekProfile WHERE
(EmployeeID = @.EmployeeID))
WHEN 5 THEN
(SELECT CAST((60 * ThursdayHours) AS int) FROM WeekProfile WHERE
(EmployeeID = @.EmployeeID))
WHEN 6 THEN
(SELECT CAST((60 * FridayHours) AS int) FROM WeekProfile WHERE
(EmployeeID = @.EmployeeID))
WHEN 7 THEN
(SELECT CAST((60 * SaturdayHours) AS int) FROM WeekProfile WHERE
(EmployeeID = @.EmployeeID))
END
IF @.NumMinutes < @.RequiredDuration
BEGIN
-- Set the Start for the dummy work block to 08:30 + the number of
minutes the employee has already worked that day
SET @.NewWorkTime = DateAdd(minute, @.NumMinutes, @.NewWorkTime)
-- Set the duration for the dummy work block to be required duration
less the amount they've already worked
SET @.Duration = @.RequiredDuration - @.NumMinutes
-- Now we have the correct data - insert into table.
INSERT INTO TimesheetItem
(EmployeeID,
Start,
DurationMins,
WorkID)
VALUES
(@.EmployeeID,
@.NewWorkTime,
@.Duration,
@.NetworkCode)
END
FETCH NEXT FROM TooFewHours INTO @.EmployeeID, @.TimesheetDate,
@.NumMinutes
END
CLOSE TooFewHours
DEALLOCATE TooFewHours
GO
--************************************************** ***********
Thanks
EdwardLine 68 refers to the 68th line of text in a batch after a GO command;
this may cause you to miss where the failing subquery is. Obviously
it's the section where you set @.RequiredDuration; you mentioned that
you tested Monday-Friday, what about Saturday and Sunday?
Stu|||On 28 Jul 2005 07:03:11 -0700, Will wrote:
>I am attempting to execute the Stored Procedure at the foot of this
>message. The Stored Procedure runs correctly about 1550 times, but
>receive the following error three times:
>Server: Msg 512, Level 16, State 1, Procedure BackFillNetworkHours,
>Line 68
>Subquery returned more than 1 value. This is not permitted when the
>subquery follows =, !=, <, <= , >, >= or when the subquery is used as
>an expression.
(snip)
Hi Will/Edward,
Before I get to some other issues, let's first tackle this error. I
can't trace back where your line 68 is due to line breaks inserted by
either your Usenet posting software or my reader, but I'm willing to bet
that it is one of the seven subqueries in this part:
>SET @.RequiredDuration = CASE (DATEPART(dw, @.NewWorkTime))
>WHEN 1 THEN
>(SELECT CAST((60 * SundayHours) AS int) FROM WeekProfile WHERE
>(EmployeeID = @.EmployeeID))
(snippety)
>WHEN 7 THEN
>(SELECT CAST((60 * SaturdayHours) AS int) FROM WeekProfile WHERE
>(EmployeeID = @.EmployeeID))
Checking the thread you refered to for the table definitions, I see that
WeekProfile has a compound PRIMARY KEY constraint on EmployeeID plus
PeriodStart. The subselects above are only guaranteed to bring back one
value if the PK is on EmployeeID only. Since changes to the work profile
of employees tend to be rare, the subquery would still return 1 row for
most of your employees, but apparently, 3 out of your 1550 employees now
have a new WeekProfile and your subquery brings up both. Run this to
find the offenders:
SELECT EmployeeID, COUNT(*)
FROM WeekProfile
GROUP BY EmployeeID
HAVING COUNT(*) > 1
The thread you refered to was an interesting read in itself. This was
the first time I saw it, since I was on holiday when you first posted
it, and I decided to skip most unread messages when I came back from the
holiday and found well over a thousand new messages in the groups I
frequent.
Anyway, several things bother me. You asked for a way to avoid the
cursor in this code, Erland supplied you with one - and now, you are
busy solving bugs in the same cursor-based code you said you wanted to
replace. Why didn't you implement Erland's suggestion?
Also, Joe Celko has already given you his usual treatment so I won't
comment on the database design too much (and yes, I did read that you
are only the poor guy who inherited this DB). But I do agree with Joe:
the design has much room for improvement.
Finally:
>-- Set the correct date to 08:30 - by default the cast from the
>cursor's select statement is midday
>SET @.TimesheetString = @.TimesheetDate + ' 08:30'
Not midday, but mignight. I know it's "only" the comment, but small
errors in comments will waste many hours of your successor's time.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||
Hugo Kornelis wrote:
> (snip)
> Hi Will/Edward,
> Before I get to some other issues, let's first tackle this error. I
> can't trace back where your line 68 is due to line breaks inserted by
> either your Usenet posting software or my reader, but I'm willing to bet
> that it is one of the seven subqueries in this part:
> >SET @.RequiredDuration = CASE (DATEPART(dw, @.NewWorkTime))
> >WHEN 1 THEN
> >(SELECT CAST((60 * SundayHours) AS int) FROM WeekProfile WHERE
> >(EmployeeID = @.EmployeeID))
> (snippety)
> >WHEN 7 THEN
> >(SELECT CAST((60 * SaturdayHours) AS int) FROM WeekProfile WHERE
> >(EmployeeID = @.EmployeeID))
> Checking the thread you refered to for the table definitions, I see that
> WeekProfile has a compound PRIMARY KEY constraint on EmployeeID plus
> PeriodStart. The subselects above are only guaranteed to bring back one
> value if the PK is on EmployeeID only. Since changes to the work profile
> of employees tend to be rare, the subquery would still return 1 row for
> most of your employees, but apparently, 3 out of your 1550 employees now
> have a new WeekProfile and your subquery brings up both. Run this to
> find the offenders:
> SELECT EmployeeID, COUNT(*)
> FROM WeekProfile
> GROUP BY EmployeeID
> HAVING COUNT(*) > 1
You're right - there was an offender. Thank you.
> Anyway, several things bother me. You asked for a way to avoid the
> cursor in this code, Erland supplied you with one - and now, you are
> busy solving bugs in the same cursor-based code you said you wanted to
> replace. Why didn't you implement Erland's suggestion?
Well, the problem was (and is) that in order to have any hope of
getting an answer here, I felt it necessary to simplify matters
greatly. When it came to solving the actual problem, I couldn't work
out a way to do it with "raw" SQL. Plus, this is a one-time only data
cleansing exercise, so it can run in a batch over the weekend and
performance isn't an issue. I asked the question because I was
interested in an abstract way whether the problem could be solved.
> Also, Joe Celko has already given you his usual treatment so I won't
> comment on the database design too much (and yes, I did read that you
> are only the poor guy who inherited this DB). But I do agree with Joe:
> the design has much room for improvement.
What he probably doesn't appreciate (and why should he - I didn't tell
him!) is that this is purely an internal system, that is modelled on at
least two existing systems from which data must be extracted. Sure
there are kludges, but it does work (it's in Beta test at the moment
and seems remarkably stable and reliable, pace the legacy data which I
am in the process of cleaning up.)
> Finally:
> >-- Set the correct date to 08:30 - by default the cast from the
> >cursor's select statement is midday
> >SET @.TimesheetString = @.TimesheetDate + ' 08:30'
> Not midday, but mignight. I know it's "only" the comment, but small
> errors in comments will waste many hours of your successor's time.
I couldn't agree more - the comments have been changed. However, vide
supra, this is a one-time, throw away operation and the comments are
more for the benefit of the group than any later developer.
But many thanks to you and the others for your kind and helpful
suggestions.
Edward
Monday, March 12, 2012
Msg 468, Level 16, State 9, Line 1 --Cannot resolve the collation conflict between "Latin1_
execute the following in any user database...
So far I've been able to only reproduce this on SQL 2005 Standard instances..for some reason I don't get the error on Enterprise server.
It appears that perhaps the system objects have different collation attributes..?!!
select name from sysusers where name not in ( select name from master..syslogins )
Microsoft SQL Server 2005 - 9.00.1399.06 (X64)
Oct 14 2005 00:35:21
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
(1 row(s) affected)
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
This is because your master is in a different collation than your user database.
Try adding the COLLATE to your query.
selectnamefrom sysusers wherenamenotin(selectname COLLATE Latin1_General_CI_AIfrom master..syslogins )
Not sure which collation is your master's but just make sure they use the same collation for the comparison.
I personally like to keep all my collations the same over the entire server.
hth
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Wesley- I have a similar issue but I certainly did NOT reset any collations on purpose. I'm trying to get a match between the sysobjects.name and the objname returned by FN_LISTEXTENDEDPROPERTY from the extended properties table (or view or wherever they hide that stuff in SQL 2005). I tried your suggestion using both collations mentioned in the error -- but no luck. Here's the second attempt:
select o.[id] as 'table_id', o.[name] COLLATE Latin1_General_CI_AI as 'table_name',
0 as 'column_order', NULL as 'column_name', NULL as 'column_datatype',
NULL as 'column_length', Cast(e.value as varchar(500)) as 'column_description'
from sysobjects o
left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', N'MyTable', null, default) e on o.name = e.objname
where o.name = 'MyTable'
Any thoughts? Thanks in advance.
Hi,
Rewrite your join like this
on o.nameCOLLATE Latin1_General_CI_AS = e.objname
hth
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Superb! Works like a charm. Wonder how the COLLATIONS ever got out of synch on system tables?
|||
Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.
If I find some time I'll try to figure out why.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.
If I find some time I'll try to figure out why
Any progress on this one? I'm having a little trouble understanding when I need to add this COLLATE clause.
Dave
|||Something odd is definitely going on here. We just moved to SQL Server 2005 and this problem is coming up. These are fresh installs of 2k5 and we didn't change any collations on the system meta-views. The default install options, I believe, were accepted on this.
BTW, I'm getting this error on a user database that we scripted as a CREATE Database from a production db. It has a compatibility of 90, not 80, and does have the collation of SQL_Latin1_CP1_CI_AS. All the system databases on SQL 2000 has that same collation. When I look at the properties of the system dbs on 2k5, the collation is also SQL_Latin1_CP1_CI_AS, so maybe it's a column collation issue?
Point is, I don't see a collation for Latin1_General_CI_AI anywhere on my SQL 2k5 server. So why am I getting this error?
Catadmin
|||Same thing here. This SQL joins input from two system sources. Why they are coming up as two different collations I do not yet know. The objname from the extended property and the table_name (changed to colObjectName in the query) from sp_tables seem to use two different collations. Our install was a converstion from SQL 2000 to SQL 2005 but all DBs are set to Compatibility level of 90 and using SQL_Latin1_General_CP1_CI_AS. I'll keep digging but hopefully someone will post a quick answer here. This is also reproduceable on a test server that was not a conversion from SQL 2005 (was a fresh install).
Select colObjectName,Convert(varchar(1000),value)as colDescription
From(
Select table_name as colObjectName
FromOpenquery(yourservername,'yourdbname..sp_tables')
) procs
LeftJoin
::FN_LISTEXTENDEDPROPERTY (
'Description',
'User','dbo',
'Table',default,default,default
)
on colObjectName
-- collate SQL_Latin1_General_CP1_CI_AS --uncomment this line is required to work on our server
= objname
Where colObjectName like'tbl%' --the prefix we use for in house developed tables
|||I did some research, and my guess is that fn_listextendedproperty returns the property ofmssqlsystemresource, although I have not verified that mssqlsystemresource has this collation. I suspect that this is difficult to address in terms of functionality. Remember that the resouce database is like a DLL, so it cannot be in the server collation, it must always be in one and the same collation.
What should be done is to document it. I filed
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215
Msg 468, Level 16, State 9, Line 1 --Cannot resolve the collation conflict between "Latin1_
execute the following in any user database...
So far I've been able to only reproduce this on SQL 2005 Standard instances..for some reason I don't get the error on Enterprise server.
It appears that perhaps the system objects have different collation attributes..?!!
select name from sysusers where name not in ( select name from master..syslogins )
Microsoft SQL Server 2005 - 9.00.1399.06 (X64)
Oct 14 2005 00:35:21
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
(1 row(s) affected)
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
This is because your master is in a different collation than your user database.
Try adding the COLLATE to your query.
select name from sysusers where name not in ( select name COLLATE Latin1_General_CI_AI from master..syslogins )
Not sure which collation is your master's but just make sure they use the same collation for the comparison.
I personally like to keep all my collations the same over the entire server.
hth
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Wesley- I have a similar issue but I certainly did NOT reset any collations on purpose. I'm trying to get a match between the sysobjects.name and the objname returned by FN_LISTEXTENDEDPROPERTY from the extended properties table (or view or wherever they hide that stuff in SQL 2005). I tried your suggestion using both collations mentioned in the error -- but no luck. Here's the second attempt:
select o.[id] as 'table_id', o.[name] COLLATE Latin1_General_CI_AI as 'table_name',
0 as 'column_order', NULL as 'column_name', NULL as 'column_datatype',
NULL as 'column_length', Cast(e.value as varchar(500)) as 'column_description'
from sysobjects o
left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', N'MyTable', null, default) e on o.name = e.objname
where o.name = 'MyTable'
Any thoughts? Thanks in advance.
Hi,
Rewrite your join like this
on o.name COLLATE Latin1_General_CI_AS = e.objname
hth
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Superb! Works like a charm. Wonder how the COLLATIONS ever got out of synch on system tables?
|||
Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.
If I find some time I'll try to figure out why.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.
If I find some time I'll try to figure out why
Any progress on this one? I'm having a little trouble understanding when I need to add this COLLATE clause.
Dave
|||Something odd is definitely going on here. We just moved to SQL Server 2005 and this problem is coming up. These are fresh installs of 2k5 and we didn't change any collations on the system meta-views. The default install options, I believe, were accepted on this.
BTW, I'm getting this error on a user database that we scripted as a CREATE Database from a production db. It has a compatibility of 90, not 80, and does have the collation of SQL_Latin1_CP1_CI_AS. All the system databases on SQL 2000 has that same collation. When I look at the properties of the system dbs on 2k5, the collation is also SQL_Latin1_CP1_CI_AS, so maybe it's a column collation issue?
Point is, I don't see a collation for Latin1_General_CI_AI anywhere on my SQL 2k5 server. So why am I getting this error?
Catadmin
|||Same thing here. This SQL joins input from two system sources. Why they are coming up as two different collations I do not yet know. The objname from the extended property and the table_name (changed to colObjectName in the query) from sp_tables seem to use two different collations. Our install was a converstion from SQL 2000 to SQL 2005 but all DBs are set to Compatibility level of 90 and using SQL_Latin1_General_CP1_CI_AS. I'll keep digging but hopefully someone will post a quick answer here. This is also reproduceable on a test server that was not a conversion from SQL 2005 (was a fresh install).
Select colObjectName, Convert(varchar(1000),value) as colDescription
From (
Select table_name as colObjectName
From Openquery (yourservername,'yourdbname..sp_tables')
) procs
Left Join
::FN_LISTEXTENDEDPROPERTY (
'Description',
'User','dbo',
'Table', default, default, default
)
on colObjectName
-- collate SQL_Latin1_General_CP1_CI_AS --uncomment this line is required to work on our server
= objname
Where colObjectName like 'tbl%' --the prefix we use for in house developed tables
|||I did some research, and my guess is that fn_listextendedproperty returns the property ofmssqlsystemresource, although I have not verified that mssqlsystemresource has this collation. I suspect that this is difficult to address in terms of functionality. Remember that the resouce database is like a DLL, so it cannot be in the server collation, it must always be in one and the same collation.
What should be done is to document it. I filed
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215
Msg 468, Level 16, State 9, Line 1 --Cannot resolve the collation conflict between "Latin1_
execute the following in any user database...
So far I've been able to only reproduce this on SQL 2005 Standard instances..for some reason I don't get the error on Enterprise server.
It appears that perhaps the system objects have different collation attributes..?!!
select name from sysusers where name not in ( select name from master..syslogins )
Microsoft SQL Server 2005 - 9.00.1399.06 (X64)
Oct 14 2005 00:35:21
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
(1 row(s) affected)
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
This is because your master is in a different collation than your user database.
Try adding the COLLATE to your query.
selectnamefrom sysusers wherenamenotin(selectname COLLATE Latin1_General_CI_AIfrom master..syslogins )
Not sure which collation is your master's but just make sure they use the same collation for the comparison.
I personally like to keep all my collations the same over the entire server.
hth
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Wesley- I have a similar issue but I certainly did NOT reset any collations on purpose. I'm trying to get a match between the sysobjects.name and the objname returned by FN_LISTEXTENDEDPROPERTY from the extended properties table (or view or wherever they hide that stuff in SQL 2005). I tried your suggestion using both collations mentioned in the error -- but no luck. Here's the second attempt:
select o.[id] as 'table_id', o.[name] COLLATE Latin1_General_CI_AI as 'table_name',
0 as 'column_order', NULL as 'column_name', NULL as 'column_datatype',
NULL as 'column_length', Cast(e.value as varchar(500)) as 'column_description'
from sysobjects o
left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', N'MyTable', null, default) e on o.name = e.objname
where o.name = 'MyTable'
Any thoughts? Thanks in advance.
Hi,
Rewrite your join like this
on o.nameCOLLATE Latin1_General_CI_AS = e.objname
hth
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Superb! Works like a charm. Wonder how the COLLATIONS ever got out of synch on system tables?
|||
Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.
If I find some time I'll try to figure out why.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.
If I find some time I'll try to figure out why
Any progress on this one? I'm having a little trouble understanding when I need to add this COLLATE clause.
Dave
|||Something odd is definitely going on here. We just moved to SQL Server 2005 and this problem is coming up. These are fresh installs of 2k5 and we didn't change any collations on the system meta-views. The default install options, I believe, were accepted on this.
BTW, I'm getting this error on a user database that we scripted as a CREATE Database from a production db. It has a compatibility of 90, not 80, and does have the collation of SQL_Latin1_CP1_CI_AS. All the system databases on SQL 2000 has that same collation. When I look at the properties of the system dbs on 2k5, the collation is also SQL_Latin1_CP1_CI_AS, so maybe it's a column collation issue?
Point is, I don't see a collation for Latin1_General_CI_AI anywhere on my SQL 2k5 server. So why am I getting this error?
Catadmin
|||Same thing here. This SQL joins input from two system sources. Why they are coming up as two different collations I do not yet know. The objname from the extended property and the table_name (changed to colObjectName in the query) from sp_tables seem to use two different collations. Our install was a converstion from SQL 2000 to SQL 2005 but all DBs are set to Compatibility level of 90 and using SQL_Latin1_General_CP1_CI_AS. I'll keep digging but hopefully someone will post a quick answer here. This is also reproduceable on a test server that was not a conversion from SQL 2005 (was a fresh install).
Select colObjectName,Convert(varchar(1000),value)as colDescription
From(
Select table_name as colObjectName
FromOpenquery(yourservername,'yourdbname..sp_tables')
) procs
LeftJoin
::FN_LISTEXTENDEDPROPERTY (
'Description',
'User','dbo',
'Table',default,default,default
)
on colObjectName
-- collate SQL_Latin1_General_CP1_CI_AS --uncomment this line is required to work on our server
= objname
Where colObjectName like'tbl%' --the prefix we use for in house developed tables
|||I did some research, and my guess is that fn_listextendedproperty returns the property ofmssqlsystemresource, although I have not verified that mssqlsystemresource has this collation. I suspect that this is difficult to address in terms of functionality. Remember that the resouce database is like a DLL, so it cannot be in the server collation, it must always be in one and the same collation.
What should be done is to document it. I filed
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215
Msg 468, Level 16, State 9, Line 1 --Cannot resolve the collation conflict between "Latin1_
execute the following in any user database...
So far I've been able to only reproduce this on SQL 2005 Standard instances..for some reason I don't get the error on Enterprise server.
It appears that perhaps the system objects have different collation attributes..?!!
select name from sysusers where name not in ( select name from master..syslogins )
Microsoft SQL Server 2005 - 9.00.1399.06 (X64)
Oct 14 2005 00:35:21
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
(1 row(s) affected)
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
This is because your master is in a different collation than your user database.
Try adding the COLLATE to your query.
selectnamefrom sysusers wherenamenotin(selectname COLLATE Latin1_General_CI_AIfrom master..syslogins )
Not sure which collation is your master's but just make sure they use the same collation for the comparison.
I personally like to keep all my collations the same over the entire server.
hth
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Wesley- I have a similar issue but I certainly did NOT reset any collations on purpose. I'm trying to get a match between the sysobjects.name and the objname returned by FN_LISTEXTENDEDPROPERTY from the extended properties table (or view or wherever they hide that stuff in SQL 2005). I tried your suggestion using both collations mentioned in the error -- but no luck. Here's the second attempt:
select o.[id] as 'table_id', o.[name] COLLATE Latin1_General_CI_AI as 'table_name',
0 as 'column_order', NULL as 'column_name', NULL as 'column_datatype',
NULL as 'column_length', Cast(e.value as varchar(500)) as 'column_description'
from sysobjects o
left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', N'MyTable', null, default) e on o.name = e.objname
where o.name = 'MyTable'
Any thoughts? Thanks in advance.
Hi,
Rewrite your join like this
on o.nameCOLLATE Latin1_General_CI_AS = e.objname
hth
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Superb! Works like a charm. Wonder how the COLLATIONS ever got out of synch on system tables?
|||
Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.
If I find some time I'll try to figure out why.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.
If I find some time I'll try to figure out why
Any progress on this one? I'm having a little trouble understanding when I need to add this COLLATE clause.
Dave
|||Something odd is definitely going on here. We just moved to SQL Server 2005 and this problem is coming up. These are fresh installs of 2k5 and we didn't change any collations on the system meta-views. The default install options, I believe, were accepted on this.
BTW, I'm getting this error on a user database that we scripted as a CREATE Database from a production db. It has a compatibility of 90, not 80, and does have the collation of SQL_Latin1_CP1_CI_AS. All the system databases on SQL 2000 has that same collation. When I look at the properties of the system dbs on 2k5, the collation is also SQL_Latin1_CP1_CI_AS, so maybe it's a column collation issue?
Point is, I don't see a collation for Latin1_General_CI_AI anywhere on my SQL 2k5 server. So why am I getting this error?
Catadmin
|||Same thing here. This SQL joins input from two system sources. Why they are coming up as two different collations I do not yet know. The objname from the extended property and the table_name (changed to colObjectName in the query) from sp_tables seem to use two different collations. Our install was a converstion from SQL 2000 to SQL 2005 but all DBs are set to Compatibility level of 90 and using SQL_Latin1_General_CP1_CI_AS. I'll keep digging but hopefully someone will post a quick answer here. This is also reproduceable on a test server that was not a conversion from SQL 2005 (was a fresh install).
Select colObjectName,Convert(varchar(1000),value)as colDescription
From(
Select table_name as colObjectName
FromOpenquery(yourservername,'yourdbname..sp_tables')
) procs
LeftJoin
::FN_LISTEXTENDEDPROPERTY (
'Description',
'User','dbo',
'Table',default,default,default
)
on colObjectName
-- collate SQL_Latin1_General_CP1_CI_AS --uncomment this line is required to work on our server
= objname
Where colObjectName like'tbl%' --the prefix we use for in house developed tables
|||I did some research, and my guess is that fn_listextendedproperty returns the property ofmssqlsystemresource, although I have not verified that mssqlsystemresource has this collation. I suspect that this is difficult to address in terms of functionality. Remember that the resouce database is like a DLL, so it cannot be in the server collation, it must always be in one and the same collation.
What should be done is to document it. I filed
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215
Msg 468, Level 16, State 9, Line 1 --Cannot resolve the collation conflict between "Lat
execute the following in any user database...
So far I've been able to only reproduce this on SQL 2005 Standard instances..for some reason I don't get the error on Enterprise server.
It appears that perhaps the system objects have different collation attributes..?!!
select name from sysusers where name not in ( select name from master..syslogins )
Microsoft SQL Server 2005 - 9.00.1399.06 (X64)
Oct 14 2005 00:35:21
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
(1 row(s) affected)
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
This is because your master is in a different collation than your user database.
Try adding the COLLATE to your query.
select name from sysusers where name not in ( select name COLLATE Latin1_General_CI_AI from master..syslogins )
Not sure which collation is your master's but just make sure they use the same collation for the comparison.
I personally like to keep all my collations the same over the entire server.
hth
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Wesley- I have a similar issue but I certainly did NOT reset any collations on purpose. I'm trying to get a match between the sysobjects.name and the objname returned by FN_LISTEXTENDEDPROPERTY from the extended properties table (or view or wherever they hide that stuff in SQL 2005). I tried your suggestion using both collations mentioned in the error -- but no luck. Here's the second attempt:
select o.[id] as 'table_id', o.[name] COLLATE Latin1_General_CI_AI as 'table_name',
0 as 'column_order', NULL as 'column_name', NULL as 'column_datatype',
NULL as 'column_length', Cast(e.value as varchar(500)) as 'column_description'
from sysobjects o
left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', N'MyTable', null, default) e on o.name = e.objname
where o.name = 'MyTable'
Any thoughts? Thanks in advance.
Hi,
Rewrite your join like this
on o.name COLLATE Latin1_General_CI_AS = e.objname
hth
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Superb! Works like a charm. Wonder how the COLLATIONS ever got out of synch on system tables?
|||
Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.
If I find some time I'll try to figure out why.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.
If I find some time I'll try to figure out why
Any progress on this one? I'm having a little trouble understanding when I need to add this COLLATE clause.
Dave
|||Something odd is definitely going on here. We just moved to SQL Server 2005 and this problem is coming up. These are fresh installs of 2k5 and we didn't change any collations on the system meta-views. The default install options, I believe, were accepted on this.
BTW, I'm getting this error on a user database that we scripted as a CREATE Database from a production db. It has a compatibility of 90, not 80, and does have the collation of SQL_Latin1_CP1_CI_AS. All the system databases on SQL 2000 has that same collation. When I look at the properties of the system dbs on 2k5, the collation is also SQL_Latin1_CP1_CI_AS, so maybe it's a column collation issue?
Point is, I don't see a collation for Latin1_General_CI_AI anywhere on my SQL 2k5 server. So why am I getting this error?
Catadmin
|||Same thing here. This SQL joins input from two system sources. Why they are coming up as two different collations I do not yet know. The objname from the extended property and the table_name (changed to colObjectName in the query) from sp_tables seem to use two different collations. Our install was a converstion from SQL 2000 to SQL 2005 but all DBs are set to Compatibility level of 90 and using SQL_Latin1_General_CP1_CI_AS. I'll keep digging but hopefully someone will post a quick answer here. This is also reproduceable on a test server that was not a conversion from SQL 2005 (was a fresh install).
Select colObjectName, Convert(varchar(1000),value) as colDescription
From (
Select table_name as colObjectName
From Openquery (yourservername,'yourdbname..sp_tables')
) procs
Left Join
::FN_LISTEXTENDEDPROPERTY (
'Description',
'User','dbo',
'Table', default, default, default
)
on colObjectName
-- collate SQL_Latin1_General_CP1_CI_AS --uncomment this line is required to work on our server
= objname
Where colObjectName like 'tbl%' --the prefix we use for in house developed tables
|||I did some research, and my guess is that fn_listextendedproperty returns the property ofmssqlsystemresource, although I have not verified that mssqlsystemresource has this collation. I suspect that this is difficult to address in terms of functionality. Remember that the resouce database is like a DLL, so it cannot be in the server collation, it must always be in one and the same collation.
What should be done is to document it. I filed
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215
Wednesday, March 7, 2012
MSDTC problem ... I think ..
I have a SSIS package in which there are lot of Sequence Containers. Each sequence container has two Execute Sql tasks which brings 2 values, and if the comparison of the value is true, it runs a data flow task. Comparison is done using Expression.
If any of the sequence container fails the comparison, I wanted whole package to rollback so I had to use Transactions. I set "Required Transaction" on the package and "Supports Transaction" on each Sequence Container and Tasks respectively.
Now that I am trying to run the package, it runs few sequence containers fine and on few it gives this error.
[Execute SQL Task] Error: Executing the query "SELECT ISNULL(MAX(ClaimBillTypeKey), -1) ClaimBillTypeKey FROM dbo.Dim_ClaimBillType" failed with the following error: "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Also, this is not consistent, one time one Seq. Cont will fail and next time it will run fine but something else will fail. What is going on?
Hello, please anyone? Help?|||I encountered this issue on the RTM version of SSIS. It seem to occur primarily when we were accessing a SQL Server that was behind a firewall. We never did find a solution - we ended up implementing our own transaction and recovery logic.
I haven't tried it in SP1 or SP2, so I'm not sure if might have been fixed by one of those releases.