?
Solved

Need help from query master

Posted on 2005-03-16
6
Medium Priority
?
418 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

764 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