SQL Server Magazine

You must log on before posting a comment.

If you don't have a username & password, please register now.

This article disappointed in the beginning but I found the last two pages pretty interesting and useful - thanks.

Cedric (since SQL Server 1.0A so not always in touch with the latest back door potential)

Cedric- January 27, 2004

Hi,

I found the article interesting, however, I cannot get the code samples to work.

In listing 4 the code

CREATE PROC sp_distinctcount @table_name AS SYSNAME, @col_name AS SYSNAME, @cnt AS OUTPUT

doesn't appear to define as datatype for @cnt and the syntax appears incomplete. I might be incoorect be I was expecting @cnt int OUTPUT.

Upon trying to execute

DECLARE @n AS int EXEC sp_distinctcount @table_name='authors', @col_name='au_lname', @cnt=@n OUTPUT PRINT 'Distinct count of last names of authors is: ' + CAST(@n AS varchar(10))

The error

Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 9 Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'

was returned.

Could you provide some assistance.

Regards

John Langley

john Langley- February 10, 2004

Itzik, Excellent article, not just saying it because I respect your knowledge and understanding of SQL Server, I found the information U had to share on special views and tables very useful. . . .I have to admit I stumbled across the executing a special procedure against any database some years ago, and I have found it very useful ever since

Olu Adedeji- February 19, 2004

Very intresting article. Thanks,Rimon.

Rimon- February 20, 2004

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 ....

MIkhail- February 23, 2004

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

Mikhail- February 23, 2004

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- February 26, 2004

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- February 27, 2004

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- March 01, 2004

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

Ken Powers- March 10, 2004

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- March 26, 2004

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

michele- April 14, 2004

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.

cvdv- October 21, 2004

Article Rating 3 out of 5