Hello Itzik,
I used xp_execresultset to execute code. My intention was to execute code longer than 4000 characters. But the procedure truncates the code at 4000 characters. This means that it is not possible to execute code longer than 4000 characters with your method.

However, I did find a solution to execute code longer than 4000 characters. You have to write the code to a text file with BCP and run this text file with te osql command.

Clemens van der Veen.

cvdv10/21/2004 3:13:25 AM

the code sp_distinctcount contains the following errors:
1) @CNT AS INT OUTPUT
2) DECLARE @SQL AS NVARCHAR(1000)

michele4/14/2004 7:25:42 AM

Hello Itzik,

Of course you are aware that there is a XP and SP version of ExecResultset. The SP version does not have the TargetDatabase as a parameter. But, your other backdoor trick of calling it from another database even though it lives in Master applies again. These different calls to generate record counts for all tables in pubs give the same result:

master..xp_ExecResultset N'select ''select count(*) as '' + quotename(name) + '' from '' + name from sysobjects where type = ''u'' order by name', N'pubs'
go
pubs..sp_ExecResultset N'select ''select count(*) as '' + quotename(name) + '' from '' + name from sysobjects where type = ''u'' order by name', 1
go

The code of the SP version can simply be retrieved with sp_helptext SP_ExecResultset (it's extremely ugly).

Thanks,
Jacques Roumimper

Jacques Roumimper 3/26/2004 6:57:27 AM

I'm trying to use the output parameters for sp_executesql you mentioned in your article, with no success. Here is the code:

ALTER PROCEDURE ap_GetRemoteServerVersion
@ServerName varchar(50),
@Results nvarchar(65) OUTPUT
AS
BEGIN

DECLARE @SQL nvarchar(500)

SET @SQL = N'exec ' + @ServerName + '.master.dbo.xp_msver ProductVersion'

EXECUTE sp_executesql @Stmt = @SQL, @Params = N'@Results nvarchar(65) OUTPUT', @Results = @Results OUTPUT

END

This is the error message I receive when I run this code:

Procedure 'ap_GetRemoteServerVersion' expects parameter '@Results', which was not supplied.

My purpose in running this code is to capture "Character_Value" from the result set of xp_msver. This value is the SQL Server version information for a particular SQL Server. If you can tell me what I'm doing wrong, or know of another way to capture values from extended stored procedures, it would be greatly appreciated.

Result set for exec xp_msver 'ProductVersion':

Index Name Internal_Value Character_Value
----- -------------- -------------- ---------------
2 ProductVersion 458752 7.00.1063

KENNETH3/10/2004 8:57:45 AM

When I run EXEC master..xp_execresultset with the accompaning query I get "The command(s) completed successfully." but no records. I have tried both with your example and my own. I am running everything from query analyzer.

(SQL2000)

Mark Murray 3/1/2004 12:00:22 PM

Excelent article.

However, the arguments described for sp_execdirect are incorrect. The correct arguments for sp_execresultset are an nvarchar string containing the query and a bit field indicating if debug info needs to be show, instead of executing the resultset. The debug argument has a default of 0 (no debug info).

Karl Gram 2/27/2004 3:19:08 AM

You wrote "T-SQL programmers can also use sp_executesql's undocumented output parameters.", but output parameters in sp_executesql are not undocumented. They are documented in KB262499: http://support.microsoft.com/?id=262499

Razvan Socol 2/26/2004 2:35:35 AM

Fix your script, guys
Listing 4: Creation Script for the Sp_distinctcount Stored Procedure
USE master
GO

CREATE PROC sp_distinctcount
@table_name AS SYSNAME,
@col_name AS SYSNAME,
@cnt AS INT OUTPUT -- here
AS
DECLARE @sql AS nvarchar (1000)
SET @sql = N'SELECT @distinctcount = COUNT(DISTINCT ['
+ @col_name
+ N']) FROM ['
+ @table_name + N']'

EXEC sp_executesql
@stmt = @sql,
@params = N'@distinctcount int OUTPUT', -- here
@distinctcount = @cnt OUTPUT
GO

Mikhail2/23/2004 3:38:16 PM

Script in listing 2 misses nvavchar in declare clause:

CREATE PROC sp_getcolumn @table_name AS SYSNAME, @col_name AS SYSNAME
AS
DECLARE @sql AS nvarchar (1000) -- here
....

Mikhail2/23/2004 2:52:45 PM

Very intresting article. Thanks,Rimon.

Rimon2/20/2004 7:42:44 AM