December 9, 2008 at 5:53 am
Hi all,
i am trying to migrate some code from one database to a new database to release resource on the first DB.
I have a stored procedure which loops through creating generic SQL, this has been working for months on the original server.
The problem I have is that a substring command is not returning the same results on the new server?
Declare @sql nvarchar(max)
set @sql = 'Select * from Fiat_Inbound , '
Print @sql
Set @sql = substring(@sql,1 , len(@sql) - 1)
Print @sql
Server 1 results
Select * from Fiat_Inbound ,
Select * from Fiat_Inbound
Note the trailing comma has been deleted
Server 2 results
Select * from Fiat_Inbound ,
Select * from Fiat_Inbound ,
Note comma still present.
Declare @sql nvarchar(max)
set @sql = 'Select * from Fiat_Inbound , '
Print @sql
set @sql = ltrim(rtrim(@sql))
Set @sql = substring(@sql,1 , len(@sql) - 1)
Print @sql
This now works, however I really need to get my two database working the same way rather than patching the problem.
Anybody got any ideas?
regards
Tom
December 9, 2008 at 6:47 am
check the collation settings between each database, the connection settings (with focus on ANSI settings) and, check the service packs and hot fixes to ensure they're both the same.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 9, 2008 at 7:50 am
I'd check on what Grant suggested.
Also, is this a data problem in any way or just differences in the way that the code is being interpreted?
December 9, 2008 at 7:55 am
Its definatly how data is being interprated as I am not returning any data from databases in the example above.
The only difference I can see on the machine is the version of SQL, I am currently downloading SP1 and hoping that resolves the issue!
regards
December 10, 2008 at 1:03 am
Applying SP1 has resolved the problem
Thanks everyone
December 10, 2008 at 6:28 am
That's great. Thanks for following up. It's always good to know what worked and what didn't.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply