Strange behaviour of stored procedure

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)

Strange behaviour of stored procedure

Author

Topic

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-05-14 : 08:51:59

Full repro script using pubs database below.Please try it and see if you get the same results as me(should be 0 rows for first insert and 1 row for second)

PROBLEM:
I use bcp to produce a file from a stored procedure. This procedure uses FOR XML AUTO clause. I then use BULK INSERT to insert the contents of the file into a text column. This all works well normally. However for 1 particular procedure this does not work. It produces the export file however it fails to insert it into the text column.THe procedure is as follows

CREATE PROCEDURE xml_cat_export_sel
AS
SET NOCOUNT ON

SELECT category,sub_category
FROM dbo.xml_cat CAT (nolock)
WHERE category <> 'TREE'
AND xml_text is not null
ORDER BY category,sub_category
FOR XML AUTO

RETURN

This produces the required output file but will not insert into my table. If I comment out the line

AND xml_text is not null

Then it works ! However the only effect this has the output file is that its slightly bigger.If I go into the file and add an "a" to the front or back it inserts fine
Why on earth would commenting out this line allow it to work ??

REPRO SCRIPT:

use pubs
go

set nocount on
create table test(xml_text text NULL)
go

create table xml_cat(
category varchar(30) NOT NULL,
sub_category varchar(30) NOT NULL,
xml_text text NULL)
go

ALTER TABLE [dbo].[xml_cat] WITH NOCHECK ADD
CONSTRAINT [PK_xml_cat] PRIMARY KEY CLUSTERED
(
[category],
[sub_category]
) ON [PRIMARY]
GO

insert xml_cat values('A','A','<ROOT></ROOT>')
insert xml_cat values('A','B','<ROOT></ROOT>')
insert xml_cat values('A','C','<ROOT></ROOT>')
insert xml_cat values('TREE','ROOT','<ROOT></ROOT>')
insert xml_cat values('ADMIN','Export XML',NULL)
go

CREATE PROCEDURE xml_cat_export_sel
AS
SET NOCOUNT ON

SELECT category,sub_category
FROM dbo.xml_cat CAT (nolock)
WHERE category <> 'TREE'
AND xml_text is not null
ORDER BY category,sub_category
FOR XML AUTO

RETURN

go
-- This will fail to insert any rows and yet WILL produce a file
declare @a varchar(1000)
declare @cnt1 int

set @a='bcp "exec pubs.dbo.xml_cat_export_sel" queryout c:\withnull.temp -S'+@@SERVERNAME + ' -T -c -r -t'
exec master..xp_cmdshell @a,no_output

BULK INSERT test FROM 'c:\withnull.temp'
SELECT @cnt1=@@ROWCOUNT

PRINT 'With the line "AND xml_text is not null" in the stored procedure' + CHAR(10)
PRINT CAST(@cnt1 as varchar) + ' rows inserted by bulk insert' + CHAR(10)

go
ALTER PROCEDURE xml_cat_export_sel
AS
SET NOCOUNT ON

SELECT category,sub_category
FROM dbo.xml_cat CAT (nolock)
WHERE category <> 'TREE'
-- AND xml_text is not null
ORDER BY category,sub_category
FOR XML AUTO

RETURN

go

declare @b varchar(1000)
declare @cnt2 int

set @b='bcp "exec pubs.dbo.xml_cat_export_sel" queryout c:\withoutnull.temp -S'+@@SERVERNAME + ' -T -c -r -t'
exec master..xp_cmdshell @b,no_output

BULK INSERT test FROM 'c:\withoutnull.temp'
SELECT @cnt2=@@ROWCOUNT

PRINT 'Without the line "AND xml_text is not null" in the stored procedure' + CHAR(10)
PRINT CAST(@cnt2 as varchar) + ' row inserted by bulk insert' + CHAR(10)
go

select * from xml_cat
go
select * from test
go

drop table test
drop table xml_cat
drop procedure xml_cat_export_sel
go

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-05-14 : 21:29:43

ANSI_NULLS robvolk??????

The ANSI_NULLS setting has tripped me up so many times now, it just isn't funny. I guess I should have learned.

Check what you're setting is, and then try swapping it (the scientific approach)....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-14 : 21:39:31

I don't know, I tried this code, I even looked at the files in a hex editor, and I couldn't find ANYTHING that would cause this. I even tried using empty ROWTERMINATOR and FIELDTERMINATOR settings with the BULK INSERT command, nothing made a difference.

ANSI_NULLS is definitely a sneaky setting that can bite you in the ass, that's about the only thing I can think of too.

Maybe, if you're REALLY bored, try bcp'ing the data out using a single space as both a column and row delimiter (surround it in double quotes on the command line) and then specify an empty ROWTERMINATOR and FIELDTERMINATOR in the BULK INSERT command. That might solve the import issue, but it could possibly FUBAR your XML too. I know that was happening to me with CR-LF and tab characters in the XML output.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources