August 13, 2003 at 1:54 pm
I am having problems with a stored procedure on our SQL Server 2000. The stored procedure is a universal SP I call to run queries on remote SQL Servers for our clients. Here it is:
CREATE PROCEDURE dbo.spUniversalCall
@server varchar(32),
@dbase varchar(32),
@user varchar(32),
@sp varchar(48),
@param1 varchar(2048) = NULL,
@tblname varchar(48) = NULL,
@numcols smallint = 0
AS
set ANSI_Warnings on
SET NOCOUNT ON
DECLARE @cmd varchar(512), @qry as varchar(4096), @sql varchar(4096), @count smallint
IF @tblname = NULL
BEGIN
GOTO runQuery
END
ELSE
BEGIN
IF OBJECTPROPERTY(OBJECT_ID(@tblname), 'IsTable') IS NULL
BEGIN
GOTO buildTable
END
ELSE
BEGIN
SET @cmd = 'DROP TABLE mecusr.' + @tblname
EXEC(@cmd)
GOTO buildTable
END
END
buildTable:
SET @cmd = 'CREATE TABLE mecusr.' + @tblname + '(colid int IDENTITY(1,1) PRIMARY KEY CLUSTERED'
SET @count = 1
WHILE @count <= @numcols
BEGIN
SET @cmd = @cmd + ', col' + LTRIM(STR(@count)) + ' varchar(255) NULL'
SET @count = @count + 1
END
SET @cmd = @cmd + ')'
--PRINT '@cmd: ' + @cmd
EXEC(@cmd)
SET @qry = @server + '.' + @dbase + '.' + @user + '.' + @sp
SET @sql =
'INSERT INTO mecusr.' + @tblname + ' EXEC ' + @qry + ' "' + @param1 + '"'
--PRINT '@sql: ' + @sql
EXEC(@sql)
runQuery:
SET @qry = @server + '.' + @dbase + '.' + @user + '.' + @sp + ' "' + @param1 + '"'
--PRINT '@qry: ' + @qry
EXEC(@qry)
GO
This stored procedure resides on our local SQL Server and is used to pass a query and connection info to run against a remote SQL Server. I can also send a table name and number of columns to dynamically create and populate a temp table on our local server. This SP has worked beautifully on our production server. I recently moved the DB to a development server and now I am experiencing some problems. I have had these problems in the past when I developed this SP and I managed to fix it (I think I changed some server options, but I can't remember what I did). When run on the development server I receive this error:
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with '
select distinct
isnull(gthosts.hostname, gthosts.ipaddr) as hostname,
socbotscanjobs.starttime,
socbotscanjobs.endtime,
so' is too long. Maximum length is 128.
Here is the call to the SP:
Exec spUniversalCall '[SOCMONW]','intersoc','dbo','spExecuteSql','
select distinct
isnull(gthosts.hostname, gthosts.ipaddr) as hostname,
socbotscanjobs.starttime,
socbotscanjobs.endtime,
socbotscanpolicy.policyname,
nessusplugins.Name,
nessusplugins.summary,
scanresults.portid,
scanresults.protocol,
scanresults.servicename,
nessusplugins.risk,
nessusplugins.family,
nessusplugins.cveid,
scanresults.data
from gthosts inner join socbotscanjobs on gthosts.hostid = socbotscanjobs.hostid
inner join scanresults on socbotscanjobs.botscanjobid = scanresults.botscanjobid
inner join nessusplugins on scanresults.pluginid = nessusplugins.pluginid
inner join socbotscanpolicy on socbotscanjobs.scanpolicyid = socbotscanpolicy.scanpolicyid
and socbotscanjobs.scanpolicyid = socbotscanpolicy.scanpolicyid
where gthosts.companyid = 1016 and gthosts.hostid = 1 order by nessusplugins.risk','temp_2_rpt136',13
It doesn't matter if I run this query from an ASP page or in Query Analyzer, I still get the same results. I have checked the settings (that I can think of) between both servers and tried turning off and on QUOTED_IDENTIFIERS, nothing seems to help. If anyone can be of assistance, I would greatly appreciate it!
August 13, 2003 at 2:47 pm
I figured out the problem. It seems that in the SP where the code reads:
' "' + @param1 + '"'
There were double quotes, which made the query treat param1 as an identifier instead of a string literal. It was hard to discern double quotes (") from 2 single quotes (''). I have modified the SP and taken care of this issue. No need to post any replies. Maybe someone else having this problem will find this post and see the error of their ways 😉
August 14, 2003 at 2:26 am
Just to complete this.
When you resolved the issue during development, you probably changed the value of QUOTED_IDENTIFIER.
SET QUOTED_IDENTIFIER OFF
August 14, 2003 at 2:33 am
I remeber having similar problems in the past and now whenever I create dynamic SQL I use char(39) to put in a single quote into the string.
Jeremy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply