There is already an object named '#XYZ' in the database 
Author Message
 There is already an object named '#XYZ' in the database

We're passing a reasonably complex SQL statement (enclosed) to ADO 2.6 and
it's coming back with the following error message:

Description: There is already an object named '#TempProducts' in the
database.
Native error: 2714
SQL State: Microsoft OLE DB Provider for SQL Server

The same error message is displayed by Cystal Reports 8.5 when we copy and
paste the same SQL statement into it's native ADO connection so it kind of
suggests it's not problem in our VB code.

As you can see, we are dropping the temporary table at the end of the SQL.
We have a very similar kind of construct that works fine but with slightly
fewer inner joins.

The same SQL works find in Query Analyser.

Bit stuck with this one... Ohh, changing the temporary table to a view and
the problem goes away but that creates another problem for us.

Rob.

Set NoCount On

Select 'Ref'='150.'+Cast(P.ID As nvarchar(8)), 'KB_ID'=150, 'KB_Name'='Med
Info', 'ProductID'=P.ID, 'ProductName'=P.Name
Into #TempProducts
From [Dev Med Info KB].[dbo].[Products] As P
Union
Select 'Ref'='168.'+Cast(P.ID As nvarchar(8)), 'KB_ID'=168,
'KB_Name'='PharmaCo Demo', 'ProductID'=P.ID, 'ProductName'=P.Name
From [Dev PharmaCo Demo KB].[dbo].[Products] As P
Union
Select 'Ref'='173.'+Cast(P.ID As nvarchar(8)), 'KB_ID'=173,
'KB_Name'='PharmaCo Test', 'ProductID'=P.ID, 'ProductName'=P.Name
From [Dev PharmaCo Test KB].[dbo].[Products] As P

Select R.ID, R.Created, 'Ref'=P.Ref, 'KB_ID'=P.KB_ID,
'ProductID'=P.ProductID, 'KB'=P.KB_Name, 'Product'=P.ProductName, C.Surname,
C.Firstname, 'Profession'=PF.Name
From Requests As R
Inner Join (Questions As T Inner Join #TempProducts As P On
T.KnowledgeBaseID=P.KB_ID And T.ProductID=P.ProductID)
On T.RequestID=R.ID
Inner Join (People As C Inner Join Professions As PF On
C.ProfessionID=PF.ID)
On R.CustomerID=C.ID
Union
Select R.ID, R.Created, 'Ref'=P.Ref, 'KB_ID'=P.KB_ID,
'ProductID'=P.ProductID, 'KB'=P.KB_Name, 'Product'=P.ProductName, C.Surname,
C.Firstname, 'Profession'=PF.Name
From Requests As R
Inner Join (Orders As T Inner Join #TempProducts As P On
T.KnowledgeBaseID=P.KB_ID And T.ProductID=P.ProductID)
On T.RequestID=R.ID
Inner Join (People As C Inner Join Professions As PF On
C.ProfessionID=PF.ID)
On R.CustomerID=C.ID
Union
Select R.ID, R.Created, 'Ref'=P.Ref, 'KB_ID'=P.KB_ID,
'ProductID'=P.ProductID, 'KB'=P.KB_Name, 'Product'=P.ProductName, C.Surname,
C.Firstname, 'Profession'=PF.Name
From Requests As R
Inner Join (AdverseEvents As T Inner Join #TempProducts As P On
T.KnowledgeBaseID=P.KB_ID And T.ProductID=P.ProductID)
On T.RequestID=R.ID
Inner Join (People As C Inner Join Professions As PF On
C.ProfessionID=PF.ID)
On R.CustomerID=C.ID
Union
Select R.ID, R.Created, 'Ref'=P.Ref, 'KB_ID'=P.KB_ID,
'ProductID'=P.ProductID, 'KB'=P.KB_Name, 'Product'=P.ProductName, C.Surname,
C.Firstname, 'Profession'=PF.Name
From Requests As R
Inner Join (GeneralQueries As T Inner Join #TempProducts As P On
T.KnowledgeBaseID=P.KB_ID And T.ProductID=P.ProductID)
On T.RequestID=R.ID
Inner Join (People As C Inner Join Professions As PF On
C.ProfessionID=PF.ID)
On R.CustomerID=C.ID
Order By R.Created

Drop Table #TempProducts



Tue, 07 Sep 2004 01:59:05 GMT  
 There is already an object named '#XYZ' in the database
Hi,

How do you execute those SQL statements from VB? Could you
post code, please?

Thanks,

Val

Quote:
>-----Original Message-----
>We're passing a reasonably complex SQL statement

(enclosed) to ADO 2.6 and
Quote:
>it's coming back with the following error message:

>Description: There is already an object

named '#TempProducts' in the
Quote:
>database.
>Native error: 2714
>SQL State: Microsoft OLE DB Provider for SQL Server

>The same error message is displayed by Cystal Reports 8.5
when we copy and
>paste the same SQL statement into it's native ADO

connection so it kind of
Quote:
>suggests it's not problem in our VB code.

>As you can see, we are dropping the temporary table at
the end of the SQL.
>We have a very similar kind of construct that works fine
but with slightly
>fewer inner joins.

>The same SQL works find in Query Analyser.

>Bit stuck with this one... Ohh, changing the temporary
table to a view and
>the problem goes away but that creates another problem
for us.

>Rob.

>Set NoCount On

>Select 'Ref'='150.'+Cast(P.ID As nvarchar

(8)), 'KB_ID'=150, 'KB_Name'='Med
Quote:
>Info', 'ProductID'=P.ID, 'ProductName'=P.Name
>Into #TempProducts
>From [Dev Med Info KB].[dbo].[Products] As P
>Union
>Select 'Ref'='168.'+Cast(P.ID As nvarchar
(8)), 'KB_ID'=168,
>'KB_Name'='PharmaCo

Demo', 'ProductID'=P.ID, 'ProductName'=P.Name
Quote:
>From [Dev PharmaCo Demo KB].[dbo].[Products] As P
>Union
>Select 'Ref'='173.'+Cast(P.ID As nvarchar
(8)), 'KB_ID'=173,
>'KB_Name'='PharmaCo

Test', 'ProductID'=P.ID, 'ProductName'=P.Name

- Show quoted text -

Quote:
>From [Dev PharmaCo Test KB].[dbo].[Products] As P

>Select R.ID, R.Created, 'Ref'=P.Ref, 'KB_ID'=P.KB_ID,
>'ProductID'=P.ProductID, 'KB'=P.KB_Name, 'Product'=P.Produ
ctName, C.Surname,
>C.Firstname, 'Profession'=PF.Name
>From Requests As R
>Inner Join (Questions As T Inner Join #TempProducts As P
On
>T.KnowledgeBaseID=P.KB_ID And T.ProductID=P.ProductID)
>On T.RequestID=R.ID
>Inner Join (People As C Inner Join Professions As PF On
>C.ProfessionID=PF.ID)
>On R.CustomerID=C.ID
>Union
>Select R.ID, R.Created, 'Ref'=P.Ref, 'KB_ID'=P.KB_ID,
>'ProductID'=P.ProductID, 'KB'=P.KB_Name, 'Product'=P.Produ
ctName, C.Surname,
>C.Firstname, 'Profession'=PF.Name
>From Requests As R
>Inner Join (Orders As T Inner Join #TempProducts As P On
>T.KnowledgeBaseID=P.KB_ID And T.ProductID=P.ProductID)
>On T.RequestID=R.ID
>Inner Join (People As C Inner Join Professions As PF On
>C.ProfessionID=PF.ID)
>On R.CustomerID=C.ID
>Union
>Select R.ID, R.Created, 'Ref'=P.Ref, 'KB_ID'=P.KB_ID,
>'ProductID'=P.ProductID, 'KB'=P.KB_Name, 'Product'=P.Produ
ctName, C.Surname,
>C.Firstname, 'Profession'=PF.Name
>From Requests As R
>Inner Join (AdverseEvents As T Inner Join #TempProducts
As P On
>T.KnowledgeBaseID=P.KB_ID And T.ProductID=P.ProductID)
>On T.RequestID=R.ID
>Inner Join (People As C Inner Join Professions As PF On
>C.ProfessionID=PF.ID)
>On R.CustomerID=C.ID
>Union
>Select R.ID, R.Created, 'Ref'=P.Ref, 'KB_ID'=P.KB_ID,
>'ProductID'=P.ProductID, 'KB'=P.KB_Name, 'Product'=P.Produ
ctName, C.Surname,
>C.Firstname, 'Profession'=PF.Name
>From Requests As R
>Inner Join (GeneralQueries As T Inner Join #TempProducts
As P On
>T.KnowledgeBaseID=P.KB_ID And T.ProductID=P.ProductID)
>On T.RequestID=R.ID
>Inner Join (People As C Inner Join Professions As PF On
>C.ProfessionID=PF.ID)
>On R.CustomerID=C.ID
>Order By R.Created

>Drop Table #TempProducts

>.



Tue, 07 Sep 2004 03:21:13 GMT  
 There is already an object named '#XYZ' in the database

Quote:
> How do you execute those SQL statements from VB? Could you
> post code, please?

Here's the entire routine we use to encapsulate ADO calls. It handles either
calls to sprocs or, in this case, an SQL statement.

Cheers, Rob.

' +--------------------------------------+
' | Execute - Execute A Stored Procedure |
' +--------------------------------------+

Function Execute( _
    ByVal SQL As String, _
    Optional SProc As Boolean = True, _
    Optional ReturnRecordset As ADODB.Recordset, _
    Optional ReturnValue As Variant, _
    Optional CursorType As CursorTypeEnum = adOpenStatic, _
    Optional LockType As LockTypeEnum = adLockReadOnly, _
    Optional Params As Collection) _
As CError

' Get the ADO connection.

Dim Connection As ADODB.Connection
Set Execute = GetConnection(Connection)
If Not Execute Is Nothing Then Exit Function

' Create and initialise ADO command object.

Dim cmd As New ADODB.Command
With cmd
    .ActiveConnection = Connection
    .CommandText = SQL
    .CommandTimeout = Timeout
    .CommandType = IIf(SProc, adCmdStoredProc, adCmdText)
End With

' Add return parameter.

If ReturnRecordset Is Nothing Then
    Dim Param As ADODB.Parameter
    Set Param = New ADODB.Parameter
    With Param
        .Type = adInteger
        .Size = 4
        .Direction = adParamReturnValue
    End With
    cmd.Parameters.Append Param
End If

' Add procedure parameters.

If Not Params Is Nothing Then
    Dim InputParam As Variant
    For Each InputParam In Params
        Set Param = New ADODB.Parameter
        With Param
            If VarType(InputParam) = vbString Then
                .Type = adBSTR
                .Size = Len(InputParam)
            Else
                .Type = adInteger
                .Size = 4
            End If
            .Direction = adParamInput
            .Value = InputParam
        End With
        cmd.Parameters.Append Param
    Next
End If

' Execute the command or open recordset.

On Error Resume Next
If Not ReturnRecordset Is Nothing Then
    With cmd
        .Properties("Preserve On Commit") = True
        .Properties("Preserve On Abort") = True
    End With
    ReturnRecordset.CacheSize = CacheSize
    ReturnRecordset.Open cmd, , CursorType, LockType
Else
    cmd.Execute , , adExecuteNoRecords
End If

' Check for errors.

If Err Then
    Dim Param1 As New CString
    If SProc Then
        Param1 = SQL & " sproc. "
    Else
        Param1 = SQL
        Param1 = "SQL:" & vbCrLf & vbCrLf & Param1.Truncate(200, "...") &
vbCrLf & vbCrLf
    End If
    Set Execute = New CError
    Execute.Define Me, CantExecute, Err.Number, False, Param1,
GetADO_Errors(Connection)
    Exit Function
End If
On Error GoTo 0

' Get return parameter.

If ReturnRecordset Is Nothing Then
    ReturnValue = cmd.Parameters(0).Value
End If

End Function



Thu, 09 Sep 2004 00:18:57 GMT  
 There is already an object named '#XYZ' in the database

Hello,

Thank you for using the Microsoft VB Newsgroups.

If you use GO to signals the end of a batch of Transact-SQL statements,
does it make any difference?

Hope it helps. If you have any questions, please reply to this post.

Regards,  

Jian-Wei Yu
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.



Fri, 10 Sep 2004 10:11:21 GMT  
 There is already an object named '#XYZ' in the database

Hello,

Thank you for using the Microsoft VB Newsgroups.

If you use GO to signals the end of a batch of Transact-SQL statements,
does it make any difference?

Hope it helps. If you have any questions, please reply to this post.

Regards,  

Jian-Wei Yu
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.



Fri, 10 Sep 2004 10:11:44 GMT  
 There is already an object named '#XYZ' in the database
Hi,

Actually GO will not work from ADO. What you need to do is
to execute each statement respectively. First execute your
SELECT statement with UNIONs and next DROP TABLE. You
cannot execute it in one shot like this. Another way is to
separate SQL statement by semicolon (;) and in that case
you will be able to execute statements in one batch

Val

Quote:
>-----Original Message-----

>Hello,

>Thank you for using the Microsoft VB Newsgroups.

>If you use GO to signals the end of a batch of Transact-
SQL statements,
>does it make any difference?

>Hope it helps. If you have any questions, please reply to
this post.

>Regards,  

>Jian-Wei Yu
>Microsoft Support

>This posting is provided "AS IS" with no warranties, and
confers no rights.

>.



Fri, 10 Sep 2004 20:28:10 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Undefined Function 'xyz' in Expression

2. Undefined Function 'xyz' in Expression

3. Object required: 'f.Name'

4. Help changing an object's name via a variable's value

5. XMComm Control 'Port Already Open'

6. Run-Time error '20520': Job already started

7. database object doesn't exist in database main(13)

8. Just the BASIC's ma'am.

9. OLE object's path name

10. Access names of an object's properties

11. ERROR in Passing Object's name

12. Object's method name as a variable

 

 
Powered by phpBB® Forum Software