Too many characters - 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 2005 Forums

Transact-SQL (2005)

Too many characters

Author

Topic

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-22 : 07:31:45

I have 56 databases, one for each client, and each needs the same stored procedure placed on it. I wrote this:
BEGIN
SET NOCOUNT ON
DECLARE @tbl TABLE
(RecNo int IDENTITY(1,1)
,dbName varchar(50)
)

INSERT INTO @tbl
SELECT DATABASENAME
FROM ctldatabases
WHERE databasename NOT IN ('COMMON','MASTER','MSDB','tempdb','model')

DECLARE @recno int
SET @recno =1
-----------------------------------------------------------------------------
WHILE EXISTS (select * from @tbl where recno = @recno)
BEGIN
DECLARE @databasename varchar (50)
SET @databasename = (select dbname from @tbl where recno = @recno)

DECLARE @SQL varchar(max)
DECLARE @SQL1 varchar(50)
DECLARE @SQL2 varchar(max)

SET @SQL2 =
'lots and lots of code here'

SET @SQL1 = 'USE ' + @databasename + '
GO
'

SET @recno = @recno + 1

SET @SQL = @SQL1 + @SQL2

SELECT (@sql)

END --WHILE

END

which writes a CREATE PROCEDURE statement for each database which I can then copy and paste in to the analyzer and run it there. It works fine but now I need to add more code, more than will print in the query window. I tried doing an EXEC statement, but I get this error
CREATE/ALTER PROCEDURE must be the first statement in a query batch.
I tried EXEC on the USE database statement separately, but still got the error. Any ideas how I can get all the produced code to print, or execute?

Thanks,

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 07:55:46

Add a "GO" command in the dynamic sql query.

E 12°55'05.25"
N 56°04'39.16"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-08-22 : 07:58:42

Instead of doing this...

SET @SQL = @SQL1 + @SQL2

SELECT (@sql)

... do this...

EXEC (@SQL1 + @SQL2)

It will allow for hundreds of such concatenations. If you don't think so, take a look at how Microsoft did it in the sp_execresultset proc found in the Master database.

--Jeff Moden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 08:17:08

quote:
Originally posted by jimf

I tried doing an EXEC statement, but I get this error
CREATE/ALTER PROCEDURE must be the first statement in a query batch.
I tried EXEC on the USE database statement separately, but still got the error. Any ideas how I can get all the produced code to print, or execute?

set @sql = 'USE xxx' -- here you add either a ";" or a "GO" statement to separate the commands in batches.
set @sql = @sql + 'GO' + CHAR(13) + CHAR(10)

Just like the error message says, the CREATE statement must be the first in the batch.

E 12°55'05.25"
N 56°04'39.16"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-22 : 08:29:41

I had the GO in there, but perhaps int the wrong place. Here's the whole boatload of code that produces a bunch of CREATE/ALTER Procedure errors. Also the second insert statement doesn't print out fully in the results window (hence the problem). It is this second part that I am trying to add; however, even without it I got the CREATE/ALTER Procedure error.
Thanks,

Jim

BEGIN
SET NOCOUNT ON
----------------------------------------------------------------------------------
DECLARE @tbl TABLE
(RecNo int IDENTITY(1,1)
,dbName varchar(50)
)

INSERT INTO @tbl
SELECT DATABASENAME
FROM results..ctldatabases
WHERE databasename NOT IN ('COMMON','MASTER','MSDB','tempdb','model')

DECLARE @recno int
SET @recno =1
-----------------------------------------------------------------------------
WHILE EXISTS (select * from @tbl where recno = @recno)
BEGIN
DECLARE @databasename varchar (50)
SET @databasename = (select dbname from @tbl where recno = @recno)

DECLARE @SQL varchar(max)
DECLARE @SQL1 varchar(max)
DECLARE @SQL2 varchar(max)

SET @SQL2 =
'
CREATE PROCEDURE [dbo].[Load_rnetV2InboundSummary]( @databasename varchar(50),@Date datetime)

AS
BEGIN

INSERT INTO [wrkRnetV2InboundSummary]
SELECT
''Client'' = ISNULL(db.clientname,''Unknown'')
,'''
+ @databasename +
'''
,''Skill'' = chd.Skill
,''SkillName'' = ISNULL(skills.description,''Unknown'')
,''VDN'' = ISNULL(chd.VDN,''Unknown'')
,''VDNName'' = ISNULL(vdns.description,''Unknown'')
,''CallDate'' = chd.date
,''Location'' = chd.location
,''LocationName'' = loc.[description]
,''DNISCalls'' = sum ( case when chd.disp not in (''error'',''staff'') then 1 else 0 END)
,''CallsHandled'' = sum ( case when chd.disp not in (''error'',''staff'')
and chd.opid <> ''0000''
then 1 else 0
end)
,''CallsAbandoned'' = sum( case when chd.disp like ''ABAND%'' THEN 1 ELSE 0 END)
,''OutBoundTransfer'' = SUM (
CASE
WHEN chd.disp = ''TRANSFER'' and LEFT (chd.source,3) = ''OUT''
THEN 1 ELSE 0
END
)
,''OutBoundDirectDials'' = SUM (
CASE
WHEN LEFT(chd.Source,3) = ''MAN''
THEN 1 ELSE 0
END
)
,''OutBoundDirectTalkTime'' = SUM (
CASE
WHEN LEFT(chd.Source,3) = ''MAN''
THEN DATEDIFF(s,chd.talkstamp,chd.wrapstamp) /CAST(3600 as numeric(10,4))
ELSE 0
END
)
,''OutBoundDirectWrapTime'' = SUM (
CASE
WHEN LEFT(chd.Source,3) = ''MAN''
THEN DATEDIFF(s,chd.wrapstamp,chd.endstamp) /CAST(3600 as numeric(10,4))
ELSE 0
END
)

,''TalkTime'' = sum( case
when left(chd.source, 3) = ''inb'' and chd.source not like ''inb0000%'' and chd.source <> ''inb''
then datediff(s,chd.talkstamp,chd.wrapstamp)/CAST(3600.00 as numeric(10,4))
else 0
end)
,''WrapTime'' = sum( case
when left(chd.source, 3) = ''inb'' and chd.source not like ''inb0000%'' and chd.source <> ''inb''
then datediff(s,chd.wrapstamp,chd.endstamp)/CAST(3600 as numeric(10,4))
else 0
end)
,''HoldTime'' = sum ( case
when left(chd.source, 3) = ''inb'' and chd.source not like ''inb0000%'' and chd.source <> ''inb''
and chd.disp not in (''error'',''staff'')
then queuetime/cast(3600 as numeric(10,4))
else 0
end)
,''HandledHoldTime'' = sum ( case
when left(chd.source, 3) = ''inb'' and chd.source not like ''inb0000%'' and chd.source <> ''inb''
and chd.disp not like ''ABAND%''
then queuetime/cast(3600 as numeric(10,4))
else 0
end)
,''WaitTime'' = sum( case
when left(chd.source, 3) = ''inb'' and chd.source not like ''inb0000%'' and chd.source <> ''inb''
then datediff(s,chd.setupstamp,chd.talkstamp)/CAST(3600 as numeric(10,4))
else 0
end)
,''Sales'' = sum ( case when chd.disp in (''sale'',''xsale'') then 1 else 0 end)
,''Cancels'' = sum ( case when chd.disp in (''vcancels'') then 1 else 0 end)

,''logTime'' = sum(datediff(s,chd.setupstamp,chd.endstamp) )/CAST(3600 as numeric(10,4))
,''StaffSource'' = sum ( case when chd.disp = ''staff''
then datediff(s,chd.wrapstamp,chd.endstamp)/CAST(3600 as numeric(10,4))
else 0
end)

,''AbandonedInService'' = sum ( case
when chd.disp like ''ABAND%''
and chd.queuetime between 1 and svc.serviceThreshold
then 1 else 0
end)
,''AbandonedOutService'' = sum ( case
when chd.disp like ''ABAND%''
and chd.queuetime > svc.serviceThreshold
then 1 else 0
end)
,''AbandonedInQueue'' = sum ( case
when chd.disp like ''ABAND%''
and chd.queuetime = 0
then 1 else 0
end)
,''ServiceLevel'' = sum ( case
when chd.disp not like ''ABAND%''
and left(chd.source, 3) = ''inb''
and chd.source not like ''inb0000%''
and chd.source <> ''inb''
and chd.disp not in (''staff'')
and chd.queuetime <= svc.serviceThreshold

then 1 else 0
end)
,''Aux0Hours'' = SUM( CASE WHEN chd.source = ''Aux0'' THEN datediff(s,chd.setupstamp,chd.endstamp) /CAST(3600 as numeric(10,4)) ELSE 0 END)
,''Aux1Hours'' = SUM( CASE WHEN chd.source = ''Aux1'' THEN datediff(s,chd.setupstamp,chd.endstamp) /CAST(3600 as numeric(10,4)) ELSE 0 END)
,''Aux2Hours'' = SUM( CASE WHEN chd.source = ''Aux2'' THEN datediff(s,chd.setupstamp,chd.endstamp) /CAST(3600 as numeric(10,4)) ELSE 0 END)
,''Aux3Hours'' = SUM( CASE WHEN chd.source = ''Aux3'' THEN datediff(s,chd.setupstamp,chd.endstamp) /CAST(3600 as numeric(10,4)) ELSE 0 END)
,''Aux4Hours'' = SUM( CASE WHEN chd.source = ''Aux4'' THEN datediff(s,chd.setupstamp,chd.endstamp) /CAST(3600 as numeric(10,4)) ELSE 0 END)
,''Aux5Hours'' = SUM( CASE WHEN chd.source = ''Aux5'' THEN datediff(s,chd.setupstamp,chd.endstamp) /CAST(3600 as numeric(10,4)) ELSE 0 END)
,''Aux6Hours'' = SUM( CASE WHEN chd.source = ''Aux6'' THEN datediff(s,chd.setupstamp,chd.endstamp) /CAST(3600 as numeric(10,4)) ELSE 0 END)
,''Aux7Hours'' = SUM( CASE WHEN chd.source = ''Aux7'' THEN datediff(s,chd.setupstamp,chd.endstamp) /CAST(3600 as numeric(10,4)) ELSE 0 END)
,''Aux8Hours'' = SUM( CASE WHEN chd.source = ''Aux8'' THEN datediff(s,chd.setupstamp,chd.endstamp) /CAST(3600 as numeric(10,4)) ELSE 0 END)
,''Aux9Hours'' = SUM( CASE WHEN chd.source = ''Aux9'' THEN datediff(s,chd.setupstamp,chd.endstamp) /CAST(3600 as numeric(10,4)) ELSE 0 END)
,''AuxHoldTime'' = SUM(CASE WHEN left(chd.source,3) IN (''INB'',''MAN'') THEN chd.holdtime ELSE 0 END )
,''OtherTime'' = SUM(CASE WHEN left(chd.source,3) IN (''INB'',''MAN'') THEN chd.othertime ELSE 0 END )
FROM
wrkchd chd (NOLOCK)
LEFT JOIN
results..ctlLocations loc (NOLOCK)
ON chd.location = loc.location
LEFT JOIN
results.dbo.ctlSkills skills (NOLOCK)
ON
chd.skill = skills.skill
LEFT JOIN
results.dbo.ctlVDNs VDNs
ON
chd.VDN = VDNS.Vdn
LEFT JOIN
results.dbo.ctlDatabases db
ON
skills.databasename = db.databasename
LEFT JOIN
results.dbo.ctlskillthresholds svc
ON
chd.skill = svc.skill
WHERE
chd.source not in (''error'')
and chd.skill is not null
GROUP BY
chd.skill,db.clientname
,skills.description
,vdns.description
,chd.vdn
,chd.date
,chd.location
,loc.description

-----------------------------------------------------------------------------------
------------------------------------------------------------------------------------
INSERT INTO dbo.wrkRnetV2InboundSummary

SELECT
''Client'' = ISNULL(db.clientname,''Unknown'')
,'''
+ @databasename +
'''
,''Skill'' = chd.Skill
,''SkillName'' = ISNULL(skills.description,chd.skill)
,''VDN'' = ISNULL(chd.VDN,''Unknown'')
,''VDNName'' = ISNULL(vdns.description,chd.vdn)
,''CallDate'' = chd.date
,''Location'' = chd.location
,''LocationName'' = loc.description
,''DNISCalls'' = 0
,''CallsHandled'' = 0
,''CallsAbandoned'' = 0
,''OutBoundTransfer'' = 0
,''OutBoundDirectDials'' = 0
,''OutBoundDirectTalkTime'' = 0
,''OutBoundDirectWrapTime'' = 0
,''TalkTime'' = 0
,''WrapTime'' = 0
,''HoldTime'' = 0
,''HandledHoldTime'' = 0
,''WaitTime'' = 0
,''Sales'' = 0
,''Cancels'' = 0
,''logTime'' = 0
,''StaffSource'' = 0
,''AbandonedInService'' = 0
,''AbandonedOutService'' = 0
,''AbandonedInQueue'' = 0
,''ServiceLevel'' = 0
,''Aux0Hours'' = SUM( CASE WHEN chd.source = ''Aux0'' THEN DATEDIFF(s,chd.setupstamp,chd.endstamp) ELSE 0 END)
,''Aux1Hours'' = SUM( CASE WHEN chd.source = ''Aux1'' THEN DATEDIFF(s,chd.setupstamp,chd.endstamp) ELSE 0 END)
,''Aux2Hours'' = SUM( CASE WHEN chd.source = ''Aux2'' THEN DATEDIFF(s,chd.setupstamp,chd.endstamp) ELSE 0 END)
,''Aux3Hours'' = SUM( CASE WHEN chd.source = ''Aux3'' THEN DATEDIFF(s,chd.setupstamp,chd.endstamp) ELSE 0 END)
,''Aux4Hours'' = SUM( CASE WHEN chd.source = ''Aux4'' THEN DATEDIFF(s,chd.setupstamp,chd.endstamp) ELSE 0 END)
,''Aux5Hours'' = SUM( CASE WHEN chd.source = ''Aux5'' THEN DATEDIFF(s,chd.setupstamp,chd.endstamp) ELSE 0 END)
,''Aux6Hours'' = SUM( CASE WHEN chd.source = ''Aux6'' THEN DATEDIFF(s,chd.setupstamp,chd.endstamp) ELSE 0 END)
,''Aux7Hours'' = SUM( CASE WHEN chd.source = ''Aux7'' THEN DATEDIFF(s,chd.setupstamp,chd.endstamp) ELSE 0 END)
,''Aux8Hours'' = SUM( CASE WHEN chd.source = ''Aux8'' THEN DATEDIFF(s,chd.setupstamp,chd.endstamp) ELSE 0 END)
,''Aux9Hours'' = SUM( CASE WHEN chd.source = ''Aux9'' THEN DATEDIFF(s,chd.setupstamp,chd.endstamp) ELSE 0 END)
,0
,0
FROM
[dbo].errchdload chd (nolock)
LEFT JOIN
results..ctlLocations loc (NOLOCK)
ON chd.location = loc.location
LEFT JOIN
results.dbo.ctlSkills skills (NOLOCK)
ON
chd.skill = skills.skill
LEFT JOIN
results.dbo.ctlVDNs VDNs
ON
chd.VDN = VDNS.Vdn
LEFT JOIN
results.dbo.ctlDatabases db
ON
skills.databasename = db.databasename
WHERE
chd.source not in (''error'')
and chd.skill is not null
and left(chd.source,3) = ''AUX''
and chd.date = @date
GROUP BY chd.Skill
,chd.location,db.clientname
,skills.description
,vdns.description
,chd.vdn
,chd.date
,chd.location
,loc.description
END
GO
'

SET @SQL1 = 'USE ' + @databasename + '
GO
'

SET @recno = @recno + 1

--SET @SQL = @SQL1 + @SQL2
EXEC(@sql1 + @sql2)
-- SELECT (@sql)

END --WHILE

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources