Identifier too long - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Identifier too long

Author

Topic

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-10-14 : 08:15:33

hi there, i have following code

SET QUOTED_IDENTIFIER ON
DECLARE @sTableDiff varchar(1000)
SET @sTableDiff= " ""C:\Program Files\Microsoft SQL Server\90\COM\tablediff"" -sourceserver ""VSNET1"" -sourceuser ""sa"" -sourcepassword ""sa2345"" -sourcedatabase ""PMSTEMP"" -sourcetable ""DEMAT"" -destinationserver ""VSNET1"" -destinationuser ""sa"" -destinationpassword ""sa2345"" -destinationdatabase ""PMSRESTORE"" -destinationtable ""DEMAT"" -f ""c:\Diff"""
PRINT @sTableDiff
EXEC XP_CMDSHELL @sTableDiff

When i execute it, throws error as
Msg 103, Level 15, State 4, Line 3
The identifier that starts with ' "C:\Program Files\Microsoft SQL Server\90\COM\tablediff" -sourceserver "VSNET1" -sourceuser "sa" -sourcepassword "sysadm" -sour' is too long. Maximum length is 128.

When i split the Identifier as

SET QUOTED_IDENTIFIER ON
DECLARE @sFull varchar(1000)
DECLARE @s1 varchar(1000)
DECLARE @s2 varchar(1000)
DECLARE @s3 varchar(1000)
DECLARE @s4 varchar(1000)
SET @s1= '""C:\Program Files\Microsoft SQL Server\90\COM\tablediff""'
SET @s2= ' -sourceserver "VSNET1" -sourceuser "sa" -sourcepassword "sa2345" -sourcedatabase "PMSTEMP" '
SET @s3=' -sourcetable "DEMAT" -destinationserver "VSNET1" -destinationuser "sa" -destinationpassword '
SET @s4=' "sa2345" -destinationdatabase "PMSRESTORE" -destinationtable "DEMAT" -f "c:\Diff"'
SET @sFull = @s1 +@s2 + @s3 + @s4
EXEC XP_CMDSHELL @sFull

this executes fine but the optput is as

The input line is too long.
NULL

What is this?
how could i execute such long commad using CMDSHELL?

Regards,
Thansks.
Gurpreet S. Gill

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-14 : 09:04:41

create a batch file with parameter and call the batch file via cmdshell and pass in the values as parameters

Kristen
Test

22859 Posts

Posted - 2006-10-14 : 09:06:48

With SET QUOTED_IDENTIFIER ON you need to delimit your Text Strings with single quotes. Change the first and last double quote to a single quote and you should be fine:

SET @sTableDiff= ' ""C:\Program ... -f ""c:\Diff""'

but you will probably need to change the doubled-up double-quotes to singleton double-quotes.

Kristen

marat
Yak Posting Veteran

85 Posts

Posted - 2006-10-14 : 09:29:57

This should work:
SET QUOTED_IDENTIFIER ON
DECLARE @sTableDiff nvarchar(1000)
SET @sTableDiff= ' "C:\Program Files\Microsoft SQL Server\90\COM\tablediff" -sourceserver VSNET1 -sourceuser sa -sourcepassword sa2345 -sourcedatabase PMSTEMP -sourcetable DEMAT -destinationserver VSNET1 -destinationuser sa -destinationpassword sa2345 -destinationdatabase PMSRESTORE -destinationtable DEMAT -f c:\Diff'
PRINT @sTableDiff
EXEC XP_CMDSHELL @sTableDiff

Also, why not as a first step, to look at SQL server Books Online?
Syntax
xp_cmdshell {'command_string'} [, no_output]

Arguments
'command_string'

Is the command string to execute at the operating-system command shell. command_string is varchar(255) or nvarchar(4000), with no default. command_string cannot contain more than one set of double quotation marks. A single pair of quotation marks is necessary if any spaces are present in the file paths or program names referenced by command_string. If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround.

no_output

Is an optional parameter executing the given command_string, and does not return any output to the client.

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-10-16 : 01:29:46

Thanks Marat & Kristen
Your code works.
Regards,
Thanks
Gurpreet S. Gill

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources