error opening Enterprise manager

Greetings,
When I open up SQL Server 2000, I get the error:
"Run-time error!
Program: C:\Winnt\system32\mmc.exe
R6025
-pure virtual function call"
Any ideas what I need to do? I really appreciate it.
Thank you,
Jimuh reinstall sql client tools?|||Well, I did reinsatall SQL DMO 2000; is that what you mean, or do you mean something else.

I know there's SQL Server 2000 installation, and sqldmo2000 installation.

Is "sql client tools" something different?

Thank you,
Jim

Error on Update, but not when using cursor

I've implemented a UDF in SQL Server 2005 written in C#. The function with its assembly has been registered ok with SQL Server and works fine. It accepts three short strings (nvarchar of lengths 5, 35, and 35) and returns a SQL formatted string (SqlString).

When I run the function to test it it works just fine, and the same is true if I run the function inside a cursor to update a field in a table. But when I do a simple update it crashes. I've so far received two different errors: first one error saying a string could not be converted into an integer (but the error does not occur when I enter the same input values manually via a test Windows form, or through the new Query Analyzer as a single query - or using it inside a cursor). Then one error saying a string was too short (I couldn't use substring(X, Y) because the string, I was told, was too short - it wasn't).

The problem thus cannot be with the function since it works just fine if I do like this:

UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3) WHERE PersonId = 10000001

And it works fine while doing the same thing inside a cursor (for instance working with the first 10, 100 or 1000 records).

But when I do this it crashes:

UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3)

For your information the table has about 1.5M records (for testing, it contain more data when on the production server) and my aim is to update the CodeField column as quickly as possible. The CodeField is a 12-character string that is based on a rather complex algorithm including the Field1, Field2 and Field3 strings. I'm using C# because it manages strings much better than SQL Server - and it is so much easier coding this stuff.

Anyhow, I've had this kind of problem before with SQL Servers 2000 and 7 (maybe even 6.5) and it seems the problem occurs when I let SQL Server go about its business at its own pace. But when I do something to control that it really takes one record at a time (through using a cursor or executing the query with a WHERE clause like the one above) it works splendidly.

The problem here is that a cursor is way too slow, and there really shouldn't be a problem with a simple UPDATE command, should it? After all, everything works just fine except when I let SQL Server do what it does best (i.e. update the field at its own speed, whatever that is).

Any ideas? This is very frustrating since it is impossible to try and find the error - it isn't there when testing! And it is frustrating since I remember having had the same kind of problem (but every time with different errors arising) before without finding a solution (except for slowing everything down - not an option here).

Is there a certain tweak I can do to make things work out, or should I code things differently?

Thanks!

Hi Bylund

Your update statement will fail because it does not know which records you are trying to update.

Am I correct in thinking you want to update ALL records in the DB?

It seems to me, despite your function, you still need to provide some sort of where clause.

Let me know so I can help further.

Barry Andrew (Pace)sql

Error on Update, but not when using cursor

I've implemented a UDF in SQL Server 2005 written in C#. The function with its assembly has been registered ok with SQL Server and works fine. It accepts three short strings (nvarchar of lengths 5, 35, and 35) and returns a SQL formatted string (SqlString).

When I run the function to test it it works just fine, and the same is true if I run the function inside a cursor to update a field in a table. But when I do a simple update it crashes. I've so far received two different errors: first one error saying a string could not be converted into an integer (but the error does not occur when I enter the same input values manually via a test Windows form, or through the new Query Analyzer as a single query - or using it inside a cursor). Then one error saying a string was too short (I couldn't use substring(X, Y) because the string, I was told, was too short - it wasn't).

The problem thus cannot be with the function since it works just fine if I do like this:

UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3) WHERE PersonId = 10000001

And it works fine while doing the same thing inside a cursor (for instance working with the first 10, 100 or 1000 records).

But when I do this it crashes:

UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3)

For your information the table has about 1.5M records (for testing, it contain more data when on the production server) and my aim is to update the CodeField column as quickly as possible. The CodeField is a 12-character string that is based on a rather complex algorithm including the Field1, Field2 and Field3 strings. I'm using C# because it manages strings much better than SQL Server - and it is so much easier coding this stuff.

Anyhow, I've had this kind of problem before with SQL Servers 2000 and 7 (maybe even 6.5) and it seems the problem occurs when I let SQL Server go about its business at its own pace. But when I do something to control that it really takes one record at a time (through using a cursor or executing the query with a WHERE clause like the one above) it works splendidly.

The problem here is that a cursor is way too slow, and there really shouldn't be a problem with a simple UPDATE command, should it? After all, everything works just fine except when I let SQL Server do what it does best (i.e. update the field at its own speed, whatever that is).

Any ideas? This is very frustrating since it is impossible to try and find the error - it isn't there when testing! And it is frustrating since I remember having had the same kind of problem (but every time with different errors arising) before without finding a solution (except for slowing everything down - not an option here).

Is there a certain tweak I can do to make things work out, or should I code things differently?

Thanks!

Hi Bylund

Your update statement will fail because it does not know which records you are trying to update.

Am I correct in thinking you want to update ALL records in the DB?

It seems to me, despite your function, you still need to provide some sort of where clause.

Let me know so I can help further.

Barry Andrew (Pace)

Error on a view

I am getting an error when I open a view err is:

Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.

How do I work aorund this issue?

On the assumption that you are using SQL Server 2000, there are a couple of KB articles on this:

http://support.microsoft.com/kb/818406
http://support.microsoft.com/kb/828269

Either way, it seems that the solution is to upgrade to SQL Server 2000 SP4.

Chris

|||

Chris,

That would be a long term fix. Is there any shortterm workaorund for this? pl let me know.

|||

Well, according to the second link I posted:

"If you still receive the "Msg 4414" error message, you may have to break up the query so that a lower number of tables or views are referenced in the query. You may change the view definition that is referenced in the problematic query to lower the number of tables that are referenced in the view."

Chris

Error occurs when tried to view table design in Enterprise Manager

Dear All,
I have a problem
When I tried to view the table design throught Enterprise Manager's "Design
Table" function
If prompted
Table "XXXXX" could not be loaded
ODBC error: [Microsoft][ODBC SQL Server Driver]Communication link failure
What's happen?
Thanks~~Have both the server instance and your client tools been patched with at
least SP3a? Are you connected to Enterprise Manager on the same machine as
the database, or are you accessing it from a remote machine? Is your server
registration set up using windows auth or sql auth? Have you considered
managing objects using Query Analyzer?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Utada P.W. SIU" <wing0508@.hotmail.com> wrote in message
news:OHNwtHV7EHA.3120@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> I have a problem
> When I tried to view the table design throught Enterprise Manager's
"Design
> Table" function
> If prompted
> Table "XXXXX" could not be loaded
> ODBC error: [Microsoft][ODBC SQL Server Driver]Communication link failure
> What's happen?
> Thanks~~
>|||My client pc haven't patch sp3a yet
however, when I using that sql server's Enterprise Manager to view the table
design, it prompt the same error to me...
Any idea'
Thanks~|||> Any idea'
Yes, install SP3a?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Installed...but still cannot view table design....
oh!!! how come!!!
it is very hard to create table by using sql statement...change table field
type by sql statement
add/delete/update field info by sql statement...
oh!!!|||> it is very hard to create table by using sql statement...change table
field
> type by sql statement
Have you looked at CREATE TABLE and ALTER TABLE in Books Online?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Of course
I mean that it is too trouble to using code to create table or alter table
structure,...
hehe~

Error Notification from User Defined Function

Is there any reason why I shouldn't cause an arithmetic error(say by
dividing by zero) in a User Defined Function for a situation where in a
stored procedure you would use RAISERROR or in code you would throw an
exception?

In most situations, I can check the return value of the UDF to see that
it is valid. However, I want to sum the output of UDF and therefore
cannot check the return value. Errors should be so rare that I would
rather not use a cursor to do the sum--and take the peformance hit.

Following is the code:

SELECT SUM([dbo].[udf_MyFunction]([Column1],[Column2]))
FROM tblMyTable

Thanks ~ MattMatt (mattmorg55@.gmail.com) writes:
> Is there any reason why I shouldn't cause an arithmetic error(say by
> dividing by zero) in a User Defined Function for a situation where in a
> stored procedure you would use RAISERROR or in code you would throw an
> exception?
> In most situations, I can check the return value of the UDF to see that
> it is valid. However, I want to sum the output of UDF and therefore
> cannot check the return value. Errors should be so rare that I would
> rather not use a cursor to do the sum--and take the peformance hit.

Trapping errors from UDF is not any simple affair. The normal procedure
would be:

SELECT SUM([dbo].[udf_MyFunction]([Column1],[Column2]))
FROM tblMyTable
SELECT @.err = @.@.error

But unfortunately, when you get an error in a UDF, @.@.error is not
set for the caller. (This has been addressed in SQL 2005, which also
has a vastly improved error handling.)

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

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

Error message question..

I am getting the error message
Procedure or function sp_MSaddinitialarticle has too many arguments specified. The step failed.
Please help me resolve this issue (The knowledge base did not have any relevent information).
Thank you for a quick response,
Carl
You are trying to put more arguments to a
procedure or funtion than it needs.
for example: (psuedo code)
sp_myproc @.A, @.B, @.C
and then you
EXEC sp_myproc 1, 2, 3, 4
would get you the error because you
have too many arguments (4) for the
procedure (3)
Bob M.
"Carl" <Carl@.discussions.microsoft.com> wrote in message
news:AB31BB38-3C7A-4772-93C4-6E453ACC3C63@.microsoft.com...
> I am getting the error message
> Procedure or function sp_MSaddinitialarticle has too many arguments
specified. The step failed.
> Please help me resolve this issue (The knowledge base did not have any
relevent information).
> Thank you for a quick response,
> Carl
|||Carl,
Whats the SQLServer edition and service pack?
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Carl" <Carl@.discussions.microsoft.com> wrote in message
news:AB31BB38-3C7A-4772-93C4-6E453ACC3C63@.microsoft.com...
> I am getting the error message
> Procedure or function sp_MSaddinitialarticle has too many arguments
specified. The step failed.
> Please help me resolve this issue (The knowledge base did not have any
relevent information).
> Thank you for a quick response,
> Carl

Error message question..

I am getting the error message
Procedure or function sp_MSaddinitialarticle has too many arguments specifie
d. The step failed.
Please help me resolve this issue (The knowledge base did not have any relev
ent information).
Thank you for a quick response,
CarlYou are trying to put more arguments to a
procedure or funtion than it needs.
for example: (psuedo code)
sp_myproc @.A, @.B, @.C
and then you
EXEC sp_myproc 1, 2, 3, 4
would get you the error because you
have too many arguments (4) for the
procedure (3)
Bob M.
"Carl" <Carl@.discussions.microsoft.com> wrote in message
news:AB31BB38-3C7A-4772-93C4-6E453ACC3C63@.microsoft.com...
> I am getting the error message
> Procedure or function sp_MSaddinitialarticle has too many arguments
specified. The step failed.
> Please help me resolve this issue (The knowledge base did not have any
relevent information).
> Thank you for a quick response,
> Carl|||Carl,
Whats the SQLServer edition and service pack?
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Carl" <Carl@.discussions.microsoft.com> wrote in message
news:AB31BB38-3C7A-4772-93C4-6E453ACC3C63@.microsoft.com...
> I am getting the error message
> Procedure or function sp_MSaddinitialarticle has too many arguments
specified. The step failed.
> Please help me resolve this issue (The knowledge base did not have any
relevent information).
> Thank you for a quick response,
> Carl

Error message question..

I am getting the error message
Procedure or function sp_MSaddinitialarticle has too many arguments specified. The step failed.
Please help me resolve this issue (The knowledge base did not have any relevent information).
Thank you for a quick response,
CarlYou are trying to put more arguments to a
procedure or funtion than it needs.
for example: (psuedo code)
sp_myproc @.A, @.B, @.C
and then you
EXEC sp_myproc 1, 2, 3, 4
would get you the error because you
have too many arguments (4) for the
procedure (3)
Bob M.
"Carl" <Carl@.discussions.microsoft.com> wrote in message
news:AB31BB38-3C7A-4772-93C4-6E453ACC3C63@.microsoft.com...
> I am getting the error message
> Procedure or function sp_MSaddinitialarticle has too many arguments
specified. The step failed.
> Please help me resolve this issue (The knowledge base did not have any
relevent information).
> Thank you for a quick response,
> Carl|||Carl,
Whats the SQLServer edition and service pack?
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Carl" <Carl@.discussions.microsoft.com> wrote in message
news:AB31BB38-3C7A-4772-93C4-6E453ACC3C63@.microsoft.com...
> I am getting the error message
> Procedure or function sp_MSaddinitialarticle has too many arguments
specified. The step failed.
> Please help me resolve this issue (The knowledge base did not have any
relevent information).
> Thank you for a quick response,
> Carl

Error Message 443

Hi,

I'm trying to create the following function:

CREATE FUNCTION dbo.get_id(@.name NVARCHAR(50)) RETURNS INT AS
BEGIN
DECLARE @.id INT
SET @.id = (SELECT id FROM dbo.language WHERE name = @.name)
IF @.@.ROWCOUNT = 0
RAISERROR('Specified item does not exist', 16, 1)

RETURN @.id
END
GO

And I get the following error message:
Invalid use of side-effecting or time-dependent operator in 'RAISERROR' within a function.

I can't find any references to the use of RAISERROR within functions or stored procedures, so I have no idea why this is not working.
What I'm trying to do is check for the existence of the row inside the function and not outside, on all the places I need to call this function. I need to have an exception in my code if this happens, that's why I thought RAISERROR is what I needed...

Any help would be appreciated!

Thanks,
Florin

You cannot use RAISERROR from within TSQL UDFs. You need to put this logic in a stored procedure or use a SQLCLR UDF. Please take a look at http://msdn2.microsoft.com/en-us/library/ms191320(en-US,SQL.90).aspx topic also.|||

Thanks, I eventually figured out that I can't use RAISERROR within a function, so I changed it to a stored procedure. My problem now is that RAISERROR doesn't stop the execution of my stored procedure, but continues with the next statement. Is there a way to achieve the same kind of functionality as with primary key or constraint violations? I'm testing some conditions inside my stored procedure and I want to stop the execution under certain circumstances and get an exception in my .NET calling code. Is that at all possible with RAISERROR?
I noticed that when an error is produced by the SQL Server engine, besides the red error message (which I can also create using RAISERROR) there is another message saying "The statement has been terminated". How can I achieve the same? :-)

Thanks, Florin

|||While you can't raise an error in a udf. . .

this function:

CREATE FUNCTION dbo.get_id(@.name NVARCHAR(50)) RETURNS INT AS
BEGIN
DECLARE @.id INT
SELECT @.id = id FROM dbo.language WHERE name = @.name
RETURN @.id
END
GO

will return a null if name is not found.

Now, I contend, trying to select an id for a name that doesn't exist shouldn't be an error. . . It should return a null. Trying to use the null in a place that requires non-null will raise the error. . . and that might make perfect logical sense.

Just a thought.

On the other hand, if you want to use a procedure, look up @.@.ERROR in the help file.|||

Well, in the application logic, the foreign key can be null, which means that record has no references, but in this particular case I need to throw an error because the calling client has given a foreign key value that does not exist. Null is a valid case, non-existing referenced record is not. That's why I need the error. I want to do as much as I can on the database in one stored procedure call, to avoid multiple database calls/roundtrips...

Florin

|||

huh? I think you misunderstood. . .

run this script. . .

-- BEGIN SCRIPT =========================

create table Song(ID int not null identity(1,1) primary key, name varchar(255))

go

create table Band(ID int not null identity(1,1) primary key, name varchar(255))

go

create table Record(BandID int not null references Band(ID), SongID int not null references Song(ID) )

go

CREATE FUNCTION dbo.getSongID(@.name NVARCHAR(50)) RETURNS INT AS

BEGIN

DECLARE @.id INT

SELECT @.id = id FROM dbo.Song WHERE name = @.name

RETURN @.id

END

go

CREATE FUNCTION dbo.getBandID(@.name NVARCHAR(50)) RETURNS INT AS

BEGIN

DECLARE @.id INT

SELECT @.id = id FROM dbo.Band WHERE name = @.name

RETURN @.id

END

go

Insert into Song(Name) values('Love Will Tear Us Apart')

Insert into Band(Name) values('Joy Division')

-- This works

Insert into Record values(dbo.getBandID('Joy Division'), dbo.getSongID('Love Will Tear Us Apart'))

-- This doesn't and the error is on the database

Insert into Record values(dbo.getBandID('Captain and Tennille'), dbo.getSongID('Love Will Tear Us Apart'))

-- This doesn't and the error is on the database

Insert into Record values(dbo.getBandID('Joy Division'), dbo.getSongID('Love Will Keep Us Together'))

-- END SCRIPT =========================