Post reply

Problems with manipulation of character data.

  • September 23, 2003 at 9:50 am

    #81155

    I have the following batch:

    declare @tracetable sysname

    declare @traceid integer

    declare @dbid integer

    declare @objid integer

    declare @dbname varchar(30)

    declare @objname varchar(100)

    set @traceid=1

    set @tracetable='TraceSummary'

    exec ('DECLARE DBID_cursor CURSOR FOR SELECT distinct databaseid FROM ' + @tracetable + ' where traceid=' + @traceid)

    OPEN DBID_cursor

    FETCH NEXT FROM DBID_CURSOR into @dbid

    set @dbname=(select name from sqop.master.dbo.sysdatabases where dbid=@dbid)

    exec ('DECLARE OBJ_cursor CURSOR FOR SELECT distinct objectid FROM ' + @tracetable + ' where databaseid=' + @dbid + ' and traceid=' + @traceid)

    OPEN Obj_Cursor

    FETCH NEXT FROM OBJ_Cursor into @objid

    Execute ('Insert Into TraceObjects Select ' + @dbid + ' , ' + @dbname +' , ' + @objid + ', name from Servername.' + @dbname + '.dbo.sysobjects where id=' + @objid)

    CLOSE OBJ_Cursor

    DEALLOCATE OBJ_Cursor

    CLOSE DBID_Cursor

    DEALLOCATE DBID_Cursor

    The variable @dbname holds the first database name retieved which in this case is master. When the insert into statement is run I just recieve the following error: Invalid column name 'master'.

    I have tried setting quoted identifiers on to allow the insert to work with the char value held by @dbname but I stil have no success. Any bright ideas?

    Many Thanks.

  • September 23, 2003 at 3:41 pm

    #475501

    I am afraid that your insert statement is, in the technical tems used where I work, 'hosed' 

    'Insert Into TraceObjects Select ' + @dbid + ' , ' + @dbname +' , ' + @objid + ', name 

    from Servername.' + @dbname + '.dbo.sysobjects where id=' + @objid

    Now, if @dbname = 'master', @dbid = 1, and @objID = 2, then the final query is 

     'Insert Into TraceObjects Select 1, master , 2, name 

    from Servername.master.dbo.sysobjects where id=2

    My bet: the value in @dbname needs quotes, so you should build your command like:

    'Insert Into TraceObjects Select ' + @dbid + ' , ''' + @dbname +''' , ' + @objid + ', name from Servername.' + @dbname + '.dbo.sysobjects where id=' + @objid

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply