?
Solved

Need help from query master

Posted on 2005-03-16
6
Medium Priority
?
424 Views
Last Modified: 2008-01-09
Hi,
There is a critical problem I am facing problem is that.
I’m working in a database (MS SQL Server) testDB. When I query to another database like test2DB  
Without where clause it works fine But when I add where clause it does not work at all.


WITHOUT WHERE CLAUSE
declare @StrQry varchar(1000)
declare @DB   varchar(20)
set @DB = 'test2DB'
set @StrQry = 'exec(''SELECT object_name(fkeyid) as Forenkey from sysreferences '')'
exec('Use '+ @DB+ ';'+ @StrQry)


WITH WHERE CLAUSE
declare @StrQry varchar(1000)
declare @DB   varchar(20)
set @DB = 'test2DB'
set @StrQry = 'exec(''SELECT object_name(fkeyid) as Forenkey from sysreferences where object_name(rkeyid) = ‘’test_Colum’’'')'
exec('Use '+ @DB+ ';'+ @StrQry)

Returns error: incorrect syntax near the ‘test_Colum’

Plz try to resolve/test it in your PC and reply the solution ASAP

farooq
0
Comment
Question by:farooqazeem
6 Comments
 
LVL 3

Expert Comment

by:alexandermancera
ID: 13556457
Hi, try this.
This work fine for me.

declare @StrQry varchar(1000)
declare @DB   varchar(20)
set @DB = 'test2DB'
set @StrQry = 'exec("SELECT object_name(fkeyid) as Forenkey from sysreferences where object_name(rkeyid) = '
+ char(39) + 'test_Colum' + char(39) + '")'
print @StrQry
exec('Use '+ @DB+ ';'+ @StrQry)
0
 
LVL 21

Accepted Solution

by:
Kevin3NF earned 280 total points
ID: 13556492
[code]

--WITH WHERE CLAUSE
declare @StrQry varchar(1000)
declare @DB   varchar(20)
set @DB = 'test2DB'
--set @StrQry = 'exec(''SELECT object_name(fkeyid) as Forenkey from sysreferences where object_name(rkeyid) = ‘’test_Colum’’'')'
set @StrQry = 'SELECT object_name(fkeyid) as Forenkey from sysreferences where object_name(rkeyid) = ''test_Colum'''
--exec('Use '+ @DB+ ';'+ @StrQry)
Print @strqry

/*
result:
SELECT object_name(fkeyid) as Forenkey from sysreferences where object_name(rkeyid) = 'test_Colum'
*/

[/code]

paste that into your query analyzer...I left your set @strqry in for comparison
0
 
LVL 3

Expert Comment

by:ajaypappan
ID: 13557082
kevin is right... ur test_column hs too many inverted commas..use
'test_column' and it should work
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:farooqazeem
ID: 13562907
thank Kevin
Your answer works fine if I execute it separately but my requirement is that I have to run it in a Stored Procedure with a CURSOR. Where I am giving a table name in SP cursor, deletes all depending tables of a Master table.
Here problem is that EXEC is not accepted in a cursor instead of SELECT statement

Please give me solution.

/* Code here

CREATE PROCEDURE Del_U_Det_Tab
(@Tab_Name             varchar(20),
@RetRst                  int output)
AS
Begin
set @RetRst = 1
declare       @DB varchar(20), @StrQry varchar(500), @Str_Det_Tab_Get varchar(50), @Str_Det_Tab_Del varchar(200)
set @DB = 'test2DB'
set @StrQry =' SELECT fkeyid from sysreferences where object_name(rkeyid) = ''U_' + @Tab_Name  + ''''
  Declare Curs_Del_All_Dep_Tab  cursor  
  for  exec('Use '+@DB+';'+@StrQry)
  open Curs_Del_All_Dep_Tab  
      fetch next from Curs_Del_All_Dep_Tab into @Str_Det_Tab_Get
             while (@@fetch_status=0)
              begin
            set @Str_Det_Tab_Del = ' Drop Table '+object_name(@Str_Det_Tab_Get)
            exec (@Str_Det_Tab_Del)
      fetch next from Curs_Del_All_Dep_Tab into @Str_Det_Tab_Get
              end
      close Curs_Del_All_Dep_Tab
      deallocate Curs_Del_All_Dep_Tab  
if(@@error <> 0 ) goto ErH
return @RetRst
ErH:
  set @RetRst = 0
 return @RetRst
End
GO
      */
0
 
LVL 3

Expert Comment

by:alexandermancera
ID: 13565856
I am not sure, but I understand that what wants is drop the tables with some reference with a given table. This can generate errors if the table for drop is refereced by some other. Anyway this is the procedure to drop the tables.

CREATE PROCEDURE Del_U_Det_Tab
(
@Tab_Name           varchar(20),
@RetRst               int output
)
as
begin
      set @RetRst = 1
      declare @DB varchar(20), @StrQry varchar(500), @Str_Det_Tab_Get varchar(50), @Str_Det_Tab_Del varchar(200)
      set @DB = 'Reportes'

      exec ('use ' + @DB)
      declare Curs_Del_All_Dep_Tab cursor  
      for SELECT object_name(fkeyid)
            from sysreferences
            where object_name(rkeyid) = @Tab_Name
      open Curs_Del_All_Dep_Tab  

      fetch next from Curs_Del_All_Dep_Tab into @Str_Det_Tab_Get, @Fk_name
      while (@@fetch_status=0)
      begin
            set @Str_Det_Tab_Del = ' Drop Table ' + @Str_Det_Tab_Get
            exec (@Str_Det_Tab_Del)
            fetch next from Curs_Del_All_Dep_Tab into @Str_Det_Tab_Get
      end
      close Curs_Del_All_Dep_Tab
      deallocate Curs_Del_All_Dep_Tab

      if(@@error <> 0 ) goto ErH
      return @RetRst
ErH:
      set @RetRst = 0
      return @RetRst
end
go

0
 
LVL 3

Expert Comment

by:alexandermancera
ID: 13565893
If you wants eliminate the references for drop the table, the following procedure drop the fks that references the selected table.

CREATE PROCEDURE Del_U_Det_Tab
(
@Tab_Name           varchar(20),
@RetRst               int output
)
as
begin
      set @RetRst = 1
      declare @DB varchar(20), @StrQry varchar(500), @Str_Det_Tab_Get varchar(50), @Str_Det_Tab_Del varchar(200),
            @Fk_name varchar(200)
      set @DB = 'Reportes'

      exec ('use ' + @DB)
      declare Curs_Del_All_Dep_Tab cursor  
      for SELECT object_name(fkeyid), object_name(constid)
            from sysreferences
            where object_name(rkeyid) = @Tab_Name
      open Curs_Del_All_Dep_Tab  

      fetch next from Curs_Del_All_Dep_Tab into @Str_Det_Tab_Get, @Fk_name
      while (@@fetch_status=0)
      begin
            set @Str_Det_Tab_Del = ' Alter Table ' + @Str_Det_Tab_Get + ' drop constraint ' + @Fk_name
            exec (@Str_Det_Tab_Del)
            fetch next from Curs_Del_All_Dep_Tab into @Str_Det_Tab_Get, @Fk_name
      end
      close Curs_Del_All_Dep_Tab
      deallocate Curs_Del_All_Dep_Tab

      if(@@error <> 0 ) goto ErH
      return @RetRst
ErH:
      set @RetRst = 0
      return @RetRst
end
go
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question