@@Error help

Im not really understanding how @.@.Error works. If I run this code:
exec sp_rename 'Table3','Table1'
select @.@.Error
in a DB with no Table3, I get the error message:
Server: Msg 15225, Level 11, State 1, Procedure sp_rename, Line 273
No item by the name of 'Table3' could be found in the current database
'ZipCode', given that @.itemtype was input as '(null)'.
but @.@.Error is still 0. If Im getting an error message, why is @.@.Error 0?
TIA,
ChrisR
run this for fun
create table blah (id int)
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 0
--run again
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 1
So it looks like it returns 0 when there is no error
http://sqlservercode.blogspot.com/
|||I dont get it... I need to make it fail twice in order to be used?
TIA,
ChrisR
"SQL" wrote:

> run this for fun
> create table blah (id int)
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 0
> --run again
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 1
>
> So it looks like it returns 0 when there is no error
> http://sqlservercode.blogspot.com/
>
|||No
I was just giving you the code so that you could test what the proc
returns
If you run this, the @.id should be 1 (or better not 0)
declare @.id int
exec sp_rename 'Table3','Table1'
select @.id
|||Certain types of errors are not trapped by @.@.ERROR, or the new TRY...CATCH
in SQL Server 2005. Basically, things like syntax errors or invalid object
references can prevent the batch from being successfully compiled and
executed. @.@.ERROR and TRY...CATCH only get invoked if the batch was
successfully compiled and executed. So errors like a WHERE clause that
references and invalid key value or something that does a divide by zero
will generate errors during the execution of the batch that can be processed
by @.@.ERROR or TRY...CATCH. A typo like writing SEELCT instead of SELECT
prevents the batch from being compiled, so the Database Engine simply
returns the syntax error to the calling application.
Alan Brewer [MSFT]
Content Architect, SQL Server Documentation Team
SQL Server Developer Center: http://msdn.microsoft.com/sql
SQL Server TechCenter: http://technet.microsoft.com/sql/
This posting is provided "AS IS" with no warranties, and confers no rights.
|||See http://msdn2.microsoft.com/en-us/library/ms190193.aspx
for more information on how to properly use @.@.ERROR.
/sym.
symbiote
Posted via http://www.codecomments.com

@@Error help

Im not really understanding how @.@.Error works. If I run this code:
exec sp_rename 'Table3','Table1'
select @.@.Error
in a DB with no Table3, I get the error message:
Server: Msg 15225, Level 11, State 1, Procedure sp_rename, Line 273
No item by the name of 'Table3' could be found in the current database
'ZipCode', given that @.itemtype was input as '(null)'.
but @.@.Error is still 0. If Im getting an error message, why is @.@.Error 0?
--
TIA,
ChrisRrun this for fun
create table blah (id int)
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 0
--run again
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 1
So it looks like it returns 0 when there is no error
http://sqlservercode.blogspot.com/|||I dont get it... I need to make it fail twice in order to be used?
--
TIA,
ChrisR
"SQL" wrote:
> run this for fun
> create table blah (id int)
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 0
> --run again
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 1
>
> So it looks like it returns 0 when there is no error
> http://sqlservercode.blogspot.com/
>|||No
I was just giving you the code so that you could test what the proc
returns
If you run this, the @.id should be 1 (or better not 0)
declare @.id int
exec sp_rename 'Table3','Table1'
select @.id|||Certain types of errors are not trapped by @.@.ERROR, or the new TRY...CATCH
in SQL Server 2005. Basically, things like syntax errors or invalid object
references can prevent the batch from being successfully compiled and
executed. @.@.ERROR and TRY...CATCH only get invoked if the batch was
successfully compiled and executed. So errors like a WHERE clause that
references and invalid key value or something that does a divide by zero
will generate errors during the execution of the batch that can be processed
by @.@.ERROR or TRY...CATCH. A typo like writing SEELCT instead of SELECT
prevents the batch from being compiled, so the Database Engine simply
returns the syntax error to the calling application.
--
Alan Brewer [MSFT]
Content Architect, SQL Server Documentation Team
SQL Server Developer Center: http://msdn.microsoft.com/sql
SQL Server TechCenter: http://technet.microsoft.com/sql/
This posting is provided "AS IS" with no warranties, and confers no rights.|||See [url]http://msdn2.microsoft.com/en-us/library/ms190193.aspx[/url]
for more information on how to properly use @.@.ERROR.
/sym.
symbiote
---
Posted via http://www.codecomments.com
---sql

@@Error help

Im not really understanding how @.@.Error works. If I run this code:
exec sp_rename 'Table3','Table1'
select @.@.Error
in a DB with no Table3, I get the error message:
Server: Msg 15225, Level 11, State 1, Procedure sp_rename, Line 273
No item by the name of 'Table3' could be found in the current database
'ZipCode', given that @.itemtype was input as '(null)'.
but @.@.Error is still 0. If Im getting an error message, why is @.@.Error 0?
TIA,
ChrisRrun this for fun
create table blah (id int)
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 0
--run again
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 1
So it looks like it returns 0 when there is no error
http://sqlservercode.blogspot.com/|||I dont get it... I need to make it fail twice in order to be used?
--
TIA,
ChrisR
"SQL" wrote:

> run this for fun
> create table blah (id int)
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 0
> --run again
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 1
>
> So it looks like it returns 0 when there is no error
> http://sqlservercode.blogspot.com/
>|||No
I was just giving you the code so that you could test what the proc
returns
If you run this, the @.id should be 1 (or better not 0)
declare @.id int
exec sp_rename 'Table3','Table1'
select @.id|||Certain types of errors are not trapped by @.@.ERROR, or the new TRY...CATCH
in SQL Server 2005. Basically, things like syntax errors or invalid object
references can prevent the batch from being successfully compiled and
executed. @.@.ERROR and TRY...CATCH only get invoked if the batch was
successfully compiled and executed. So errors like a WHERE clause that
references and invalid key value or something that does a divide by zero
will generate errors during the execution of the batch that can be processed
by @.@.ERROR or TRY...CATCH. A typo like writing SEELCT instead of SELECT
prevents the batch from being compiled, so the Database Engine simply
returns the syntax error to the calling application.
Alan Brewer [MSFT]
Content Architect, SQL Server Documentation Team
SQL Server Developer Center: http://msdn.microsoft.com/sql
SQL Server TechCenter: http://technet.microsoft.com/sql/
This posting is provided "AS IS" with no warranties, and confers no rights.|||See [url]http://msdn2.microsoft.com/en-us/library/ms190193.aspx[/url
]
for more information on how to properly use @.@.ERROR.
/sym.
symbiote
---
Posted via http://www.codecomments.com
---

.Net COM Interop Class not working via sp_OACreate

this thread is actually to help another guy out in a seperate thread lol...I am not a COM expert but I do have a basic understanding of it from a dev perspective. I am trying to get COM interop via VS2005 to work from a sql2005 sp_OACreate call. below is the code. I can replace my custom object with say a sqldmo reference and IT WORKS! So I was thinking maybe security issues, but even when I run sql under an admin account it does not work. and i am connecting via sa for the code.

Also, I have tested the COM object via windows script host/.vbs file: dim oOjbect set oObject = CreateObject(myCOMObject.Math) msgbox oObject.Add(1,1). And the object appears to be listed correctly in the registry under HKEY_Classes

Imports System

Imports System.IO

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports Microsoft.VisualBasic

Imports System.Diagnostics

Public Class Math

Public Function Add(ByVal iFirstNum As Integer, ByVal iSecondNum As Integer)

Return (iFirstNum + iSecondNum)

End Function

End Class

DECLARE @.object int

DECLARE @.hr int

DECLARE @.src varchar(255), @.desc varchar(255)

EXEC @.hr = sp_OACreate 'myCOMObject.Math', @.object OUT,5

IF @.hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT

SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc

RETURN

END

HI,
I find an extended stored procedure "xp_cmdshell" to create and append the text file from stored procedure. whose example is as given below:

xp_cmdshell "@.ECHO test message >> C:\file.txt"

By using this method currently it seems that my goal can achieve such that i fectch the record and write in the file, but I just want to know how much this extended method is secure to use if i call it from my own DB's stored procedure.

Although, I am also looking forward for the solution of exception by using "OA_Create"

Looking forward for quick response.

Best Regards,
Jawad Naeem

|||

Security risks depends on how the SPs are designed, how users access the SP/SQL Server, your application design (how it calls the SPs etc) among many other things. Generally, xp_cmdshell is a security risk since it can be used to compromise entire server/network/organization if used improperly. This is one of the reasons as part of SQL Server 2005 installation we now disable it by default along with features like CLR, OLE automation SPs etc that allow access to resources outside of SQL Server.

So you need to think about the following: Is it absolutely necessary to create text files from SPs? Why do you need to do that? What purpose does this process serve by creating text files from SPs? It is going to be an inefficient process anyway since you have to do it row-by-row thereby sacrificing performance & scalability. You can do this with few lines of code on the client side or using utilities like BCP without writing any code or SSIS/DTS in an efficient and robust manner.

|||HI,
Yes, i know there is security risk to use xp_cmdshell extended SP.
Actualy, the goal which i want to achieve is: A job should run through SQL Server at some specific time like fortnight, which fetches the record from database and write them in a file. So that's why i create a dll in Visual Basic.Net to fetch the record and write them in a txt file. And i write a stored procedure to call the dll. but i am getting an exception as already mentioned in the very first posting of this thread. If i success to remove the exception then i will create a job which runs at some specific time and date and will call my designed stored procedure which will be create to call the dll
so this is my problem and i need its solution badly,

Thanks
Jawad Naeem|||

For your problem, what you are doing seems too complicated. Any reason, why you want to write VB.NET code that runs in the database to extract rows to a file? I am still lost as to purpose of using a SP to call the VB.NET code. You didn't mention the version of SQL Server you are using, running .NET code from the engine is not supported under older versions of SQL Server. See below KB article for more details.

http://support.microsoft.com/default.aspx?scid=kb;en-us;322884

In any case, depending on the file format that you need you just need to create a BCP format file (you may not need one at all) and use BCP to dump data to a file. So you don't really need to write any line of code. You could schedule the BCP using SQLAgent or NT Task Scheduler. You can also write VB Script code in a SQLAgent ActiveScript task or DTS/SSIS package to do the same.

So the answer to your sp_OA* problem is that it is not supported to run .NET code in SQL Server using OLE automation. Using even with xp_cmdshell might be a problem. You will have to try and see. My suggestion would be that you can simplify your implementation and make it more robust by using one of the methods described in the previous paragraph.

|||sorry but he is right...I DID NOT KNOW ABOUT THE CONTENT OF THIS KB article...I guess you can ONLY call TRUE COM objects via sp_OA procs. If your going to leave your database at CompLevel 80 then you must either use a pre.Net COM tool OR use another tier of your application to create/manipulate flat files with.