Problema de concatenacion
Publicado por Cahilapo (11 intervenciones) el 19/11/2008 17:06:25
Necesito enviar una cadena o string a sql usando sql de paso, pero tengo un problema con la cadena, basicamente es sript de t-sql en el cual dentro del mismo armo otra cadena de string para ejecutar un exec, cuando paso todo esto a foxpro y concateno me elimina todos las "" que necesito para concatenar pero en t-sql no en fox aqui esta el ej:
Asi esta en FoxPro
lcCmd as string
lcCmd = ""
lcCmd = lcCmd + "Set Quoted_Identifier Off "
lcCmd = lcCmd + "Declare @NomBD As Varchar(150) "
lcCmd = lcCmd + "Declare @Comando As Varchar(1000) "
lcCmd = lcCmd + "Declare Cursor_dbSist Cursor "
lcCmd = lcCmd + " For Select name From master..sysdatabases Where name like 'tr%' or name like 'bd%' Order By Name "
lcCmd = lcCmd + "Open Cursor_dbSist "
lcCmd = lcCmd + "Fetch Next From Cursor_dbSist Into @NomBD "
lcCmd = lcCmd + "While @@FETCH_STATUS = 0 "
lcCmd = lcCmd + " Begin "
lcCmd = lcCmd + " Set @Comando = " + "Use " + "@NomBD "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "If Not Exists(Select * From dbo.sysUsers Where name = N'Usuario')" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Begin Exec sp_grantdbaccess N'Usuario', N'Usuario' End" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Declare @NombTabla As Varchar(150)" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Declare @Comando As Varchar(250)" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Declare Cursor_TablasSist Cursor" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "For Select name From sysObjects Where xtype = 'U' And name like 't%' Order By name" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Open Cursor_TablasSist" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Fetch Next From Cursor_TablasSist" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Into @NombTabla" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "While @@FETCH_STATUS = 0" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Begin" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Set @Comando = 'Grant References, Select, Insert, Update, Delete On ' + @NombTabla + ' To Usuario'" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Exec (@Comando)" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Set @Comando = ''" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Fetch Next From Cursor_TablasSist" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Into @NombTabla" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "End" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Close Cursor_TablasSist " + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Deallocate Cursor_TablasSist" + " "
lcCmd = lcCmd + " Exec (@Comando) "
lcCmd = lcCmd + " Set @Comando = '' "
lcCmd = lcCmd + " Fetch Next From Cursor_dbSist "
lcCmd = lcCmd + " Into @NomBD "
lcCmd = lcCmd + " End "
lcCmd = lcCmd + "Close Cursor_dbSist "
lcCmd = lcCmd + "Deallocate Cursor_dbSist "
Pero me tiene que quedar asi en SQL:
Set Quoted_Identifier Off
Declare @NomBD As Varchar(150)
Declare @Comando As Varchar(1000)
Declare Cursor_dbSist Cursor
For Select name From master..sysdatabases Where name like 'tr%' or name like 'bd%' Order By Name
Open Cursor_dbSist
Fetch Next From Cursor_dbSist Into @NomBD
While @@FETCH_STATUS = 0
Begin
Set @Comando = "Use " + @NomBD
Set @Comando = @Comando + " If Not Exists(Select * From dbo.sysUsers Where name = N'Usuario')"
Set @Comando = @Comando + " Begin Exec sp_grantdbaccess N'Usuario', N'Usuario' End"
Set @Comando = @Comando + " Declare @NombTabla As Varchar(150)"
Set @Comando = @Comando + " Declare @Comando As Varchar(250)"
Set @Comando = @Comando + " Declare Cursor_TablasSist Cursor"
Set @Comando = @Comando + " For Select name From sysObjects Where xtype = 'U' And name like 't%' Order By name"
Set @Comando = @Comando + " Open Cursor_TablasSist"
Set @Comando = @Comando + " Fetch Next From Cursor_TablasSist"
Set @Comando = @Comando + " Into @NombTabla"
Set @Comando = @Comando + " While @@FETCH_STATUS = 0"
Set @Comando = @Comando + " Begin"
Set @Comando = @Comando + " Set @Comando = 'Grant References, Select, Insert, Update, Delete On ' + @NombTabla + ' To Usuario'"
Set @Comando = @Comando + " Exec (@Comando)"
Set @Comando = @Comando + " Set @Comando = ''"
Set @Comando = @Comando + " Fetch Next From Cursor_TablasSist"
Set @Comando = @Comando + " Into @NombTabla"
Set @Comando = @Comando + " End"
Set @Comando = @Comando + " Close Cursor_TablasSist"
Set @Comando = @Comando + " Deallocate Cursor_TablasSist"
Exec (@Comando)
Set @Comando = ''
Fetch Next From Cursor_dbSist
Into @NomBD
End
Close Cursor_dbSist
Deallocate Cursor_dbSist
Asi esta en FoxPro
lcCmd as string
lcCmd = ""
lcCmd = lcCmd + "Set Quoted_Identifier Off "
lcCmd = lcCmd + "Declare @NomBD As Varchar(150) "
lcCmd = lcCmd + "Declare @Comando As Varchar(1000) "
lcCmd = lcCmd + "Declare Cursor_dbSist Cursor "
lcCmd = lcCmd + " For Select name From master..sysdatabases Where name like 'tr%' or name like 'bd%' Order By Name "
lcCmd = lcCmd + "Open Cursor_dbSist "
lcCmd = lcCmd + "Fetch Next From Cursor_dbSist Into @NomBD "
lcCmd = lcCmd + "While @@FETCH_STATUS = 0 "
lcCmd = lcCmd + " Begin "
lcCmd = lcCmd + " Set @Comando = " + "Use " + "@NomBD "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "If Not Exists(Select * From dbo.sysUsers Where name = N'Usuario')" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Begin Exec sp_grantdbaccess N'Usuario', N'Usuario' End" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Declare @NombTabla As Varchar(150)" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Declare @Comando As Varchar(250)" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Declare Cursor_TablasSist Cursor" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "For Select name From sysObjects Where xtype = 'U' And name like 't%' Order By name" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Open Cursor_TablasSist" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Fetch Next From Cursor_TablasSist" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Into @NombTabla" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "While @@FETCH_STATUS = 0" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Begin" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Set @Comando = 'Grant References, Select, Insert, Update, Delete On ' + @NombTabla + ' To Usuario'" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Exec (@Comando)" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Set @Comando = ''" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Fetch Next From Cursor_TablasSist" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Into @NombTabla" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "End" + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Close Cursor_TablasSist " + " "
lcCmd = lcCmd + " Set @Comando = @Comando + " + "Deallocate Cursor_TablasSist" + " "
lcCmd = lcCmd + " Exec (@Comando) "
lcCmd = lcCmd + " Set @Comando = '' "
lcCmd = lcCmd + " Fetch Next From Cursor_dbSist "
lcCmd = lcCmd + " Into @NomBD "
lcCmd = lcCmd + " End "
lcCmd = lcCmd + "Close Cursor_dbSist "
lcCmd = lcCmd + "Deallocate Cursor_dbSist "
Pero me tiene que quedar asi en SQL:
Set Quoted_Identifier Off
Declare @NomBD As Varchar(150)
Declare @Comando As Varchar(1000)
Declare Cursor_dbSist Cursor
For Select name From master..sysdatabases Where name like 'tr%' or name like 'bd%' Order By Name
Open Cursor_dbSist
Fetch Next From Cursor_dbSist Into @NomBD
While @@FETCH_STATUS = 0
Begin
Set @Comando = "Use " + @NomBD
Set @Comando = @Comando + " If Not Exists(Select * From dbo.sysUsers Where name = N'Usuario')"
Set @Comando = @Comando + " Begin Exec sp_grantdbaccess N'Usuario', N'Usuario' End"
Set @Comando = @Comando + " Declare @NombTabla As Varchar(150)"
Set @Comando = @Comando + " Declare @Comando As Varchar(250)"
Set @Comando = @Comando + " Declare Cursor_TablasSist Cursor"
Set @Comando = @Comando + " For Select name From sysObjects Where xtype = 'U' And name like 't%' Order By name"
Set @Comando = @Comando + " Open Cursor_TablasSist"
Set @Comando = @Comando + " Fetch Next From Cursor_TablasSist"
Set @Comando = @Comando + " Into @NombTabla"
Set @Comando = @Comando + " While @@FETCH_STATUS = 0"
Set @Comando = @Comando + " Begin"
Set @Comando = @Comando + " Set @Comando = 'Grant References, Select, Insert, Update, Delete On ' + @NombTabla + ' To Usuario'"
Set @Comando = @Comando + " Exec (@Comando)"
Set @Comando = @Comando + " Set @Comando = ''"
Set @Comando = @Comando + " Fetch Next From Cursor_TablasSist"
Set @Comando = @Comando + " Into @NombTabla"
Set @Comando = @Comando + " End"
Set @Comando = @Comando + " Close Cursor_TablasSist"
Set @Comando = @Comando + " Deallocate Cursor_TablasSist"
Exec (@Comando)
Set @Comando = ''
Fetch Next From Cursor_dbSist
Into @NomBD
End
Close Cursor_dbSist
Deallocate Cursor_dbSist
Valora esta pregunta


0