July 7, 2010 at 8:41 am
Hello.
I have a machine with sql server 2008 developer edition with sp1 and XP, and i have other machine with sql server 2008 entreprise edition with sp1 and windows server 2008.
The BD's have the same table with the same information
In the machine with developer edition, a select with LIKE operator has a good function, but in the machine with enterprise edition, the function is bad, less rows. The LIKE operator is over a column nvarchar(max).
I can't explain. I need help.
Thanks for all.
July 7, 2010 at 8:46 am
The edition of SQL server shouldn't make a difference when using LIKE.
Can you post the code that you are using? and are you sure both tables have the same rows.?
July 7, 2010 at 8:51 am
Yes, both tables are equals.
The select:
select pknummicrolote
from IPA.bdversion
where codigo like '%CreaBD%'
July 7, 2010 at 9:28 am
Its really hard to say without seeing the data, do you have an example of data that was returned by one query and not the other.?
July 7, 2010 at 9:34 am
Have you checked both installations are running with the same collation? This looks like it could be something like a case sensitive collation on one and a case insensitive on the other.
As a previous post says - please post a sample of the data you think you should get back and what is missing - along with the full DDL of the table.
Mike John
July 7, 2010 at 9:36 am
why need you to see the data?
I understand, but the column is nvarchar(max) with text.
I don't know how to put the data in the post. I know to put, but to see the data is complicated.
Same collate in both tables.
July 7, 2010 at 9:37 am
Without seeing the data, most people trying to help will just be stabbing the dark, haing the data and the table structure means people are able to setup a test on their machine... it just makes it easier to help
July 7, 2010 at 9:42 am
If the collations match and the data is really the same then I would start to suspect a corruption somewhere, so details of all indexes and the execution plan to see if the query is trying an index or a table scan would start to help.
Forcing the query to do a scan (see query hints) may help pin it down. Running DBCC to check for a physical corruption in an index may also give us some some clues.
If the query really has % at each end of the string then it will probably be doing a table scan anyway - but worth checking.
Good luck
Mike John
July 7, 2010 at 9:43 am
How many rows?
Try download something like SQLDataCompare (Redgate) demo and use it to verify that the data in the two tables really is absolutely identical.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2010 at 9:45 am
Mike John (7/7/2010)
If the collations match and the data is really the same then I would start to suspect a corruption somewhere,
Corruption (if you're talking actual data page corruption) would throw high severity errors (severity 24) if encountered by a query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2010 at 9:46 am
---- AL DTS dbo SSISCopiaTablasPrecarga AL PR IPA usp_CreaBD
alter procedure IPA.usp_CreaBD (@ServidorOrigen nvarchar(128),@BDOrigen nvarchar(128),@UsuarioOrigen nvarchar(128),@ClaveOrigen nvarchar(128),@ServidorDestino nvarchar(128),@BDDestino nvarchar(128),@UsuarioDestino nvarchar(128),@ClaveDestino nvarchar(128),@ServidorPaquete nvarchar(128),@BDPaquete nvarchar(128),@UsuarioPaquete nvarchar(128),@ClavePaquete nvarchar(128),@CorreoRemite nvarchar(128) = null,@CorreoDestino nvarchar(128) = null,@ServidorCorreo nvarchar(128) = null,@TareasAEjecutar nvarchar(5),@TablasACopiar nvarchar(512) = null,@Vardecimal nvarchar(1),@Collate nvarchar(128),@Ficheros nvarchar(2048),@Paquete nvarchar(128),@Mensaje nvarchar(max) output)
with ENCRYPTION
as
declare @p as varchar(8000)
declare @v as varchar(7000)
declare @dir as nvarchar(128)
declare @opcxml as xml
declare @idoc as int
declare @paquetaso as nvarchar(256)
declare @ret as int
declare @cod as nvarchar(4000)
declare @erp table (err nvarchar(max))
declare @err as nvarchar(max)
declare @merr as nvarchar(max)
declare @sw as bit
set @ret=0
set @mensaje=''
set @merr=''
set @TablasACopiar=isnull(@TablasACopiar,'select distinct t2.esquema+&apos~.&apos~+t2.ntab,null from ipa.qry_tabcolfast as t2 where t2.TipoTabla=&apos~MBD&apos~ and t2.ntab<~>~&apos~DLLPaths&apos~ and t2.ntab<~>~&apos~Log&apos~')
begin try
set @opcxml=cast('<root>'+replace(replace(@Ficheros,char(13),''),char(10),'')+'</root>' as xml)
EXEC sp_xml_preparedocument @idoc OUTPUT, @opcxml
select @dir=(select Directorio from openxml(@idoc,'/root/File',1) with (Fichero nvarchar(25),Tamaño nvarchar(10),Incremento nvarchar(10),Directorio nvarchar(512)) where Fichero='PRIMARY')
EXEC sp_xml_removedocument @idoc
end try
begin catch
set @ret=1
goto FIN
end catch
set @v='<root>'
set @v=@v+replace(replace(@Ficheros,char(13),''),char(10),'')
set @v=@v+'<CadenaDestino Valor=''Provider=SQLOLEDB.1~Password='+@ClaveDestino+'~User id='+@UsuarioDestino+'~Initial Catalog=master~Data Source='+@ServidorDestino+'~Connection Timeout=6000~Current Language=Spanish~''/>'
set @v=@v+'<CadenaOrigen Valor=''Provider=SQLOLEDB.1~Password='+@ClaveOrigen+'~User id='+@UsuarioOrigen+'~Initial Catalog='+@BDOrigen+'~Data Source='+@ServidorOrigen+'~Connection Timeout=600~''/>'
set @v=@v+'<CadenaPaquete Valor=''Provider=SQLOLEDB.1~Password='+@ClavePaquete+'~User id='+@UsuarioPaquete+'~Initial Catalog='+@BDPaquete+'~Data Source='+@ServidorPaquete+'~Connection Timeout=600~''/>'
set @v=@v+'<Collate Valor='''+@Collate+'''/>'
set @v=@v+'<Directorio Valor='''+@dir+'''/>'
set @v=@v+'<DLLDir Valor='''+@dir+'\DLL''/>'
set @v=@v+'<NombreBD Valor='''+@BDDestino+'''/>'
set @v=@v+'<Vardecimal Valor='''+@Vardecimal+'''/>'
set @v=@v+'<TablasACopiar Valor='''+@TablasACopiar+'''/>'
set @v=@v+'<TareasAEjecutar Valor='''+@TareasAEjecutar+'''/>'
set @v=@v+'<Usuario Valor='''+isnull(@CorreoDestino,@UsuarioPaquete)+'''/>'
set @v=@v+'<UsuarioRemite Valor='''+isnull(@CorreoRemite,@UsuarioPaquete)+'''/>'
set @v=@v+'<ServidorCorreo Valor='''+isnull(@ServidorCorreo,@ServidorPaquete)+'''/>'
set @v=@v+'<Correo Valor='''+(case when @ServidorCorreo is null then '0' else '1' end)+'''/>'
set @v=@v+'</root>'
begin try
set @v=replace(replace(@v,'~',';'),Char(39),Char(34))
set @opcxml=cast(@v as xml)
end try
begin catch
set @ret=2
goto FIN
end catch
if @ServidorCorreo is null
begin
if object_id('dbo.ERR'+@BDDestino) is null
begin
set @cod='create table dbo.ERR'+@BDDestino+' (ServidorDestino nvarchar(128),Tipo nvarchar(3),Error nvarchar(max)) on [TMPUT]'
exec (@cod)
end
else
begin
set @cod='delete from dbo.ERR'+@BDDestino+' where ServidorDestino='''+@ServidorDestino+''' and Tipo=''CDB'''
exec (@cod)
end
end
select @paquetaso=(select '"'+t1.Path+'\SSISCreaBD.dtsx"' from IPA.DLLPaths as t1 where t1.PkDLL='SSISCreaBD.dtsx')
set @p='dtexec /F '+@paquetaso+' /Decrypt '+@Paquete+' /MaxConcurrent "1" '
set @v='/set \Package.Variables[User::XML].Properties[Value];"'
set @v=@v+'<root>'
set @v=@v+replace(replace(@Ficheros,char(13),''),char(10),'')
set @v=@v+'<CadenaDestino Valor=''Provider=SQLOLEDB.1~Password='+@ClaveDestino+'~User id='+@UsuarioDestino+'~Initial Catalog=master~Data Source='+@ServidorDestino+'~Connection Timeout=6000~Current Language=Spanish~''/>'
set @v=@v+'<CadenaOrigen Valor=''Provider=SQLOLEDB.1~Password='+@ClaveOrigen+'~User id='+@UsuarioOrigen+'~Initial Catalog='+@BDOrigen+'~Data Source='+@ServidorOrigen+'~Connection Timeout=600~''/>'
set @v=@v+'<CadenaPaquete Valor=''Provider=SQLOLEDB.1~Password='+@ClavePaquete+'~User id='+@UsuarioPaquete+'~Initial Catalog='+@BDPaquete+'~Data Source='+@ServidorPaquete+'~Connection Timeout=600~''/>'
set @v=@v+'<Collate Valor='''+@Collate+'''/>'
set @v=@v+'<Directorio Valor='''+@dir+'''/>'
set @v=@v+'<DLLDir Valor='''+@dir+'\DLL''/>'
set @v=@v+'<NombreBD Valor='''+@BDDestino+'''/>'
set @v=@v+'<Vardecimal Valor='''+@Vardecimal+'''/>'
set @v=@v+'<TablasACopiar Valor='''+@TablasACopiar+'''/>'
set @v=@v+'<TareasAEjecutar Valor='''+@TareasAEjecutar+'''/>'
set @v=@v+'<Usuario Valor='''+isnull(@CorreoDestino,@UsuarioPaquete)+'''/>'
set @v=@v+'<UsuarioRemite Valor='''+isnull(@CorreoRemite,@UsuarioPaquete)+'''/>'
set @v=@v+'<ServidorCorreo Valor='''+isnull(@ServidorCorreo,@ServidorPaquete)+'''/>'
set @v=@v+'<Correo Valor='''+(case when @ServidorCorreo is null then '0' else '1' end)+'''/>'
set @v=@v+'</root>"'
set @p=@p+@v
insert into @erp exec xp_cmdshell @p
if exists(select 1 from @erp where charindex('Error',err,1)>0)
begin
set @sw=0
declare errpaq cursor fast_forward for
select err from @erp
open errpaq
fetch next from errpaq into @err
while (@@fetch_status=0)
begin
if left(@err,5)='Error'
begin
set @sw=1
set @merr=@merr+@err+char(13)+char(10)
end
if @sw=1 and left(@err,1)=' '
set @merr=@merr+@err+char(13)+char(10)
if @sw=1 and left(@err,1)<>' ' and left(@err,5)<>'Error'
set @sw=0
fetch next from errpaq into @err
end
close errpaq
deallocate errpaq
if @ServidorCorreo is null
begin
set @cod='insert into dbo.ERR'+@BDDestino+' values('''+@ServidorDestino+''',''CDB'','''+@merr+''')'
exec sp_executesql @cod
end
else
set @mensaje=@mensaje+@merr+char(13)+char(10)
end
if @ServidorCorreo is null
begin
set @cod='select * from dbo.ERR'+@BDDestino+' where ServidorDestino='''+@ServidorDestino+''' and Tipo=''CDB'''
exec (@cod)
end
IF (CAST(@TareasAEjecutar AS INT) & 2)=2
BEGIN
IF (right(@BDOrigen,5) in('00000','00005') AND @ServidorOrigen='trevinca') or (right(@BDOrigen,5)='00010' and @ServidorOrigen='rande')
begin
exec ('update ['+@ServidorDestino+'].['+@BDDestino+'].IPA.BDVersion set codigo=null where Condicion=3')
end
END
FIN:
if @ret<>0
set @mensaje=@mensaje+(case when @ret=1 then 'Estructura XML de ficheros de la BD incorrecta' else 'Estructura XML para creacion BD incorrecta' end) + char(13)+char(10)
return @ret
--
go
This is a content of the column "codigo" of the table.
In a developer edition, OK, in the enterprise edition, isn't in the results of the select.
July 7, 2010 at 9:57 am
Very true - I was thinking a less severe (and unlikely!) such as an index page simply not having an entry for the row in the data page or something equally odd. But with that query as posted it must (well almost must - there could be a covering index it scans I suppose) be doing a table scan.
As the two databases are allegedly the same it would be useful to know how one was copied to the other as that could eliminate fair number of things:
eg backup and restore, dts etc etc.
Like the match the data suggestion first though.
Edit - The post above appears tbe a stored proc calling a dts package - not the contents of the table which was what people were asking for to try and help....
Mike
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply