"INSERT INTO" working weirdly with a 3642 char. varchar(7500)
-
Hi,
I have a stored proc which copy datas from a distant server's database's table (DISTANT_TABLE) to a table in the local one (LOCAL_TABLE) which contains one more column containing a varchar(128) identifying the server.
-----------------------------------------------------------------
CREATE TABLE [dbo].[MY_DISTANT_TABLE] (
[TABLE_NAME] [varchar] (128) NULL ,
[COLS_LIST] [varchar] (7500) NULL
) ON [PRIMARY]
GO
-----------------------------------------------------------------
CREATE TABLE [dbo].[MY_LOCAL_TABLE] (
[SERVER_NAME] [varchar] (128) NULL ,
[TABLE_NAME] [varchar] (128) NULL ,
[COLS_LIST] [varchar] (7500) NULL
) ON [PRIMARY]
GO
-----------------------------------------------------------------
CREATE PROCEDURE ADM_TRANSFER_DATAS (@serverName varchar(128))
AS
BEGIN
DECLARE @CommandString varchar (1024)
DELETE FROM MY_LOCAL_TABLE WHERE SERVER_NAME = @serverName
set @CommandString = 'INSERT INTO MY_LOCAL_TABLE SELECT ' + ''''+ @serverName + '''' + ', TABLE_NAME, COLS_LIST FROM [' + @serverName + '].[MY_DISTANT_DATABASE].[dbo].MY_DISTANT_TABLE'
exec (@CommandString)
END
GO[/code]
-----------------------------------------------------------------
You see ? Easy ...
This works on many servers without any incident reported to me before this afternoon...
Today though I've encountered a problem on a server (MS SQL 2000 , same for for all involved servers). A problem I haven't solved yet.
From both of the distant server it seems I cannot insert one field which means, one col of a row ...
If I execute the following T-SQL statement:
SELECT TABLE_NAME, LEN(COLS_LIST) FROM [MY_DISTANT_SERVER].[MY_DISTANT_DATABASE].[dbo].MY_DISTANT_TABLE
from my local server (!!!) I have the following result:
Table13642
Table2566
when I execute the stored proc given before or when I executed its statements of insertion by hand I just don't copy the 3642 characters field while I copy all the rest.
I if do only a select in the query analyser it works fine too, it just goes wrong when I do the full query with "INSERT INTO".
Any idea why it's not working ? ...
-
Your @commandstring seems to be getting truncated. Increase the size to 4-5000 and do a PRINT @commandstring to see how its getting built.
******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
Try changing to:
set @CommandString = 'SELECT ' + ''''+ @serverName + '''' + ', TABLE_NAME, COLS_LIST FROM [' + @serverName + '].[MY_DISTANT_DATABASE].[dbo].MY_DISTANT_TABLE'
INSERT INTO MY_LOCAL_TABLE
exec (@CommandString)
END
Andy
-
Thanks for your replies.
I'm gonna try but I don't think it come from here as, under the query analyser:
SELECT TABLE_NAME, LEN(COLS_LIST) FROM [MY_DISTANT_SERVER].[MY_DISTANT_DATABASE].[dbo].MY_DISTANT_TABLE
is working and
INSERT INTO MY_LOCAL_TABLE SELECT TABLE_NAME, LEN(COLS_LIST) FROM [MY_DISTANT_SERVER].[MY_DISTANT_DATABASE].[dbo].MY_DISTANT_TABLE
isn't ....
And shall the request be truncated, I would not have only one col for one line missing ...
Again it's happening on a very specific server as till now I've never had any problem with that stored proc ... :/
So I guess it must be something in the settings somehow somewhere but I have no idea where to look ...
-
Oki I had doubts about their chances of success but I've tried the modifications you've suggested ... The result is the same :/
Really weird ...
-
I don't have remote access on the server so I'm remoting my costumer.
I've made several deployments with him so he knows a bit of SQL Server and I'm sending him all queries to execute for the tests by mail. I think I'm remoting him closely enough so he doesn't make mistakes ...
I've asked him to check all settings he could to see if something would catch his attention.
He has compared with all the other central servers and the only difference he has found is the following. We have this on this current and non-working server:
...while for all the other servers (which are working well, with the very same stored proc and stuff*) we have the following:
* Even the data field which is not copied must be the same on several of these servers as it depends of the application version and this installation is not a test one but directly in production as it is in other subsidiaries...
I don't see how but the SQL service account could have these kind of consequences, I mean, I would expect the insert to work totally or to not work at all but not "partially" ...
What do you think ?
-
I've finally managed to write that field in a test with that query from the central server query analyser. My distant server is FRER0263\PSXC.
DECLARE @ResultString varchar (7500)SELECT @ResultString= COLS_LIST FROM [FRER0263\PSXC].[SEA].[dbo].ADM_EXP_COLSLIST WHERE TABLE_NAME='Data_Supat_Info_Of'
-- print (@ResultString)
INSERT INTO dbo.CFG_SEA_DATASFORMAT VALUES('test', 'Data_Supat_Info_OF', @ResultString)
GO
SELECT * FROM dbo.CFG_SEA_DATASFORMAT
GO
Now I'm gonna try with two rows ...
-
WAIT !!!
Here is what I've just received by mail from my costumer
le résultat entre l'analyser et le manager est différent, dans le premier on voit les champs et dans le second non.
translation from french: the result is different between the analyser and the manager, in the first one we see the fields and in the second we don't
Well ... I'm very afraid now about realizing very soon that it is only a display problem in the manager. A display problem we don't have on the other servers but JUST a display problem ...
... I'll keep you informed ...
-
It is indeed only a display problem ...
For an unknown reason the field in question doesn't appear when you ask the Enterprise Manager to send all the rows.
But they all appear in the Query Analyser.
Well at least from now on I'll always think about mentioning the Query Analyzer for checking the results announced to me from phone ...
You can close this topic now ...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply